Microsoft 365 Excel Office Scripts For Dummies Cheat Sheet

When picking up a new programming language, having a reference sheet close at hand makes all the difference. Need a quick reminder of how to structure that if statement? Can't quite remember the syntax for a template literal? This cheat sheet has you covered.  

Here, you’ll find the core syntax patterns you’ll use again and again in Office Scripts. Bookmark it, lean on it, and let it guide you on your Office Script journey.

Common TypeScript syntax

if (value > 10) {
  console.log("Greater than 10")
}

if (value > 10) {
  console.log("High")
} else if (value > 5) {
  console.log("Medium")
} else {
  console.log("Low")
}

const label = value > 10 ? "High" : "Low"

switch (status) {
  case "Open":
    console.log("Still open")
    break;
  case "Closed":
    console.log("Finished")
    break;
  default:
    console.log("Unknown status")
}

const rowCount = 5
const colCount = 100
console.log(`Total Rows : ${rowCount}
Total Columns : ${colCount}`)

const prices = [10, 20, 30];
const withTax = prices.map(p => p * 1.10);
// new withTax array [11, 22, 33]

// Remove blank rows
const noBlanksArray = values.filter(row =>
  row[0] !== "" && row[0] !== null);

const sheet = workbook.getWorksheet("Data");
if (!sheet) {
  console.log("Sheet 'Data' not found");
  return;
}

workbook.getWorksheet("Sheet99") ??
workbook.addWorksheet("Sheet99");

workbook.getWorksheet("Sheet99")?.delete();

try {
  let MyTable = Mysheet.getTable('SalesData');
  MyTable.getRowCount
}
catch (error) {
  // runs if there is an error
  console.log('table does not exist')
}
finally {
  // always runs
  MyRange.clear(ExcelScript.ClearApplyTo.all)
}

for (let i = 0; i < 10; i++) {
  console.log(i);
}

for (let sheet of workbook.getWorksheets()) {
  console.log(sheet.getName());
}

let count = 0;
while (count < 5) {
  console.log(count);
  count++;
}

Leveraging LLMs for coding help

Transitioning from VBA to Office Scripts can feel like learning new routes in a city you already live in. You know how to get places the old way, but the old roads are closed. You know exactly what you want the data to do, but you don’t have a handle on the TypeScript syntax.  

Fortunately, you're adopting Office Scripts during the era of Large Language Models (LLMs). Tools like ChatGPT, Claude, Gemini, and Copilot are invaluable resources that act as technical translators. You provide the logic, and the LLM handles the translation into executable code. Here are a few tips to help you effectively use LLMs as a partner in Office Script development.  

Set realistic expectations

LLMs are fundamentally prediction machines. They use a vast library of language and syntax to predict the most statistically appropriate answer to a prompt. While the results can feel like magic, no thinking is happening behind the scenes. An LLM is a generative engine that matches patterns based on its training data.

In that light, do not expect an AI agent to code a perfect solution on the first try. It will churn out code in seconds, but that code is not always the most efficient. More importantly, LLMs occasionally hallucinate, that is, they propose non-existent Excel API methods with total conviction.  

Your goal should be to use LLMs to augment your existing knowledge rather than replace it. Instead of a broad prompt like "Write code to sort this range by region" try "Provide three examples of efficient ways to sort a range by a specific column in an Office Script." The second prompt enables you to compare different approaches and choose the one that is the most readable and maintainable.

Context is king

As mentioned before, LLMs can't render thoughts or ideas out of thin air. They only know what they’ve been told or have seen in training models. The more context you provide in prompts, the better the answers.

Don't begin prompts with "Write a script that." When you start a chat with an AI agent, you should seed the conversation with an explanation of what you're attempting to do. Give it some code you’ve already written. If a script fails, paste the specific error message and the line of code where it occurred.  Many models even let you upload sample data with your prompts. AI agents are at their best when given lots of context.  

Don’t rush to conclusions

Although LLMs can be huge timesavers, don't use them as quick-fix machines. Rarely is the first answer they provide the best answer. Rushing to accept anything an LLM feeds you is a recipe for potential issues later.  

Any sort of collaborative work you do with LLMs should be done iteratively. Take your time and dig for better answers. Prompt an AI agent to search for examples of code to solve your particular scenario. Then feed the answers of one AI agent (such as ChatGPT) into another agent (such as Claude) and ask “Are there any hidden inefficiencies or non-existent API calls in this TypeScript code?”  

Never trust a robot

Every major AI agent has, at some point, confidently provided code that simply doesn't work. This scenario is common with the Office Scripting API, where the AI might confuse the syntax with older JavaScript API language elements.

Remember, LLMs don’t really know what they’re writing. They simply try to predict a viable answer based on the statistical probability that certain words go together. The results can be magical or just wrong.

Test everything that comes from an AI agent. If the code produces an error, give it back to the agent with some context. Explain the error, tell it what line it’s on, and ask for a correction. Take the same code and feed it into another agent. Remember, AI agents don't get tired or offended. Take as much time as you need to test and retest.

It’s better if you’re already smart

In an old Bradley Cooper movie called Limitless, where a drug enhances cognitive ability to superhuman levels, there’s the line: “It’s better if you’re already smart.” Coding with AI is similar. It supplements your ability with a massive library of knowledge, but it works best when you already understand the fundamentals.

AI agents are not a replacement for your own skill development. The more you understand the core principles of Office scripting, the better you will be at crafting prompts and validating results. Use LLMs to bridge the gap in your syntax knowledge but keep your hands on the steering wheel.