{"id":2106,"date":"2024-08-01T10:30:00","date_gmt":"2024-08-01T00:30:00","guid":{"rendered":"https:\/\/digitalbbq.au\/?p=2106"},"modified":"2024-07-28T10:05:04","modified_gmt":"2024-07-28T00:05:04","slug":"giving-excel-an-ai-brain-with-office-scripts","status":"publish","type":"post","link":"https:\/\/digitalbbq.au\/index.php\/2024\/08\/01\/giving-excel-an-ai-brain-with-office-scripts\/","title":{"rendered":"Giving Excel an AI Brain with Office Scripts"},"content":{"rendered":"\n

As an Excel user, I’m always looking for ways to automate repetitive tasks and make my workflow more efficient. That’s where Office Scripts come in. The way office scripts are advertised out of the box is that they’re a feature in Excel that enables you to automate tasks by recording your actions and replay them whenever I want. you can create scripts using the Action Recorder, which is great for consistent actions. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

But what if you want something more? Office scripts can handle that too, as the underlying technology that drives your office scripts is Typescript, which you can use the Code Editor to build out more advanced scripts. In this post, we’ll take a closer look at how I used the Code Editor to build an Office Script that integrates with a local language model (LLM) using a REST API call.<\/p>\n\n\n\n

Not Every Problem Can Be Solved with Excel.. Or Can They?<\/h3>\n\n\n\n

I had a dataset with hundreds of rows that required me to make decisions based on multiple pieces of information. The problem was that these decisions couldn’t be made using Excel functions alone. While you can build out complex formulas to work with text, such as using the ISNUMBER(SEARCH(x)) function and other methods, they’re very basic and not suitable for my task.<\/p>\n\n\n\n

I knew I needed a more advanced solution, one that could provide a level of intelligence to the process. That’s when I turned to language models (LLMs). As I discussed in my previous post about self-hosting open source langauge models<\/a>, LLMs are not to be confused with artificial general intelligence (AGI). Instead, they’re designed to predict the next word in a sequence based on their training data. This gives the impression that they’re “thinking” when they provide responses, especially when they get it spot on. However, as anyone who’s worked with LLMs knows, they can also produce “hallucinations” – responses that are incorrect or nonsensical.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To benefit the power of LLMs in my Excel project, I needed to provide context and guidance to ensure accurate responses. I realized that by concatenating relevant information from other cells using basic Excel formulas, I could create a prompt that would help the LLM provide a more accurate answer. It was a simple yet effective approach: “Can you tell me the best match for x, pick the answer from the list of y?”<\/em><\/p>\n\n\n\n

With this approach in mind, I set out to integrate the LLM into my Excel workflow using Office Scripts. The basic steps to my code is:<\/p>\n\n\n\n