How To Bring AI Into Your Productivity Apps

AI can make you unbelievably more productive, but that productivity could be improved if you didn’t have to leave your app to go to another page. And you’ve all seen videos where ChatGPT solves Excel problems, writes code, or explains code.

To do that, we have to go to the OpenAI platform, ask it what we want, then copy and paste it into our application. So this video is not that type of video. This is where we’re going to bring AI inside our application, right? So that’s what I did here. I brought it inside my Excel file so that I can ask a question in a cell and get the reply in the next cell. If I happen to ask it a question like “Give me the names of The Sopranos cast,” it puts it all in a single box, and that bothered me. I want to put it in multiple separate rows, right, because it’s easier to take that information and then work with it in Excel. Of course, we don’t want to have to do this manually, so I decided to build on this. Whenever ChatGPT’s answer is more than a single line, we’re going to get a multiple-row version of it as well. You have no idea how excited I was when I got this code to work. I’m going to show you how I set it up, and the purpose of this video is to inspire you and show you the possibilities that are out there. Let’s go.

Bringing AI Into Excel

Okay, so over here on Excel for Desktop, I’ve created this simple layout, and in this box, I can ask my question. So let’s say I’m talking with my colleagues, what the top 10 hip-hop songs of the 90s were. Okay, so then we click on “Ask,” and this is where my script is running. The moment I click on this, notice on the side pane here, we get Script Run Status. This is based on Office Scripts. I’m going to show that to you in a second, how it works. Once we get the answer from the AI back, we put it in this cell. Oh yeah, these were my party songs. Let’s do another one. Let’s say we’re discussing what the names of the Spice Girls members were. Do you remember the Spice Girls? Do you know what they were? Let’s see if the AI knows. These are the names we got. Let’s include their nicknames as well because they did have some weird, strange nicknames like Scary, Baby, and others.

Using AI To Solve Excel Problems

Now, let’s say I finally decide to get some work done, but I’m stuck. I can’t figure out this formula that I need to put in here. So here’s what I want to do. I want to grab the total revenue from either the Health sheet or the Productivity sheet or the Game sheet. I have different sheets here. They have a similar structure. My revenue is in column B. What I wanted to do is write a SUM function here that looks into the correct sheet. So, if this is Game, you should go to the Game sheet and grab the B column. If it’s Productivity, you should go to the Productivity sheet. I don’t want to write an IF function because I am going to have a lot of different sheets. I can’t figure out the formula, but ask our AI. Okay, I’m just going to pin this in place and start with “I need an Excel formula. I need to get the sum of column B from different sheets. Now, the sheet name is in cell A2.” Okay, so this is very specific to what I need and I want it to give me the formula. So, let’s ask our AI and see what we get. Okay, so we get: “Assuming the sheet name is in cell A2, you can use the following formula.” Well, this looks good. Let me just copy this, go to my sheet, and paste this in. Okay, it looks like everything is in order and I get a number. Does it really work? Let’s sum up Game revenue. What’s our total? 210,616. That’s what we get. So, if I change this to Productivity, 162,643. Let’s just make sure that it works. Yes, it works. It gave me the right formula. This saved me a lot of time of having to go through different websites, different forums, and blog posts. I got the answer that I wanted by just asking.

Analyzing Data With AI

Now, let’s say my boss asked me to analyze the balance sheet for BMW. So, I can find their balance sheet on the web here and I’ve done my task, I’ve analyzed it all on my own, but I want to get a second opinion from the AI and see what it has to say. So, I’m just going to copy everything, go back here, paste it in, and let’s just go all the way to the top and tell it what we want it to do. We want to analyze the balance sheet and tell us what it thinks the risks are. Okay, so let’s ask. Okay, so we get a response: “The balance sheet includes the following: a high level of intangible assets, which could be a risk if the company’s business model changes. The company has a large amount of debt and the company has a large number of receivables, which could be a risk if the customers don’t pay.” Now, you can run this multiple times and you are going to get different responses. You can use these to cross-check with your own response, but of course, don’t take what the AI says as the truth. You are responsible to cross-check these and come up with your own informed decision. We can also use the AI to quickly give us some data we can work with. For example, we want to get the top 10 countries by area. Let’s ask and see what we get. Okay, so we get our list here. Now, everything is inside the same cell. That’s where this part comes into play. So, I’ve updated the script so that whenever the result is more than one line, we get it into separate rows and we get it on this sheet here. This makes it easier for me to work with the data in Excel. If you’re for example, giving some training, and you want some fake data, you can ask it as well. So, for example, we want to get sales data and budget data from January to December for a fake company. Format should be month, actual sales, and budget sales. Let’s ask and see what we get. That’s our data right here. Now that we get it into separate cells, it’s easier to work with. Now, you can also update the logic to account for the delimiter. It’s just that you can get different delimiters back from the AI, but if you have it in this format, it’s really easy to split it into separate columns. You just have to go to the “Data” tab, here, “Text to Columns”, select the limited, go next, and then select the type of the delimiter you have. So, here is a comma and a space, Finish, and we have our data in separate columns. Now, I can use this as fake data for my training. If you’re a teacher, you could go with a prompt like this: “Give me names of the Breaking Bad characters, add a fake school subject and fake grade, and grades should be in percentage.” Click on “Ask”, and that’s what we get. Tuco Salamanca is not bad in math. Notice here, the delimiter is different here, but we get them into some separate rows, and then again, we can use Text to Columns or Power Query or TEXTSPLIT function if you’d like to split this into separate columns. So, I hope these examples give you some ideas on how you could use AI to speed up your work.

Setting Up AI in Excel

Now, let me show you how I’ve set this up. So, this setup consists of two main ingredients: Office Scripts and an API for GPT from OpenAI. Office Scripts was first introduced for Excel on the web, but recently, we got this Automate tab in Excel for desktop as well. So, this is the Office 365 version. The advantage is that, whenever you automate your tasks using Office Scripts, that automation is going to work for Excel Desktop, it’s going to work for Excel on the web or if you use Excel in Teams, it’s going to work in all places. The second ingredient is an API for GPT from OpenAI, and to get that, you have to go to the OpenAI website, then click on API, set up an account if you don’t have one, and then you can get your API key. So, if I go to my account here, go to your account, and then you can generate your secret key. This is currently free for up to three months, depending on your usage. After that, you’re going to need to upgrade. The API key you get is for GPT-3. So, ChatGPT is a version of GPT-3. It was fine-tuned for conversation and dialogue. GPT-3 is broader. Now, once you have that, you can go ahead and check out the documentation. There are guides on how you can use this API, how you can make requests, and so on. Right, so have a read through this, then you get some ideas on how you can integrate this into your own models. Now, once you have this, you can go over to Excel and create a new script, and you can also do this for Excel on the web if you don’t have the “Automate” tab yet in Excel for Desktop. The script you write is called “Office Script”. Office Script is written in TypeScript and it’s a superset of JavaScript. The script is saved on my OneDrive, you can also save it on SharePoint. The starting point of any Office Script is the “main” function, and here I’m using an asynchronous function. This allows the code to run independently of other code. We’re also using “ExcelScript.Workbook”, so that we can communicate with the Excel objects. We need to provide the API keys. I’ve defined a constant for this. This is where you would be pasting it in. I’ve pasted it inside a cell in a sheet that I’ve hidden. Next up, I’ve defined some more constants. So here, we have “mytext,” this is cell B2. So, this is the “Ask,” what we’re sending to the AI. And down here is the communication with the model. We’re sending the text to the AI model and then here, we’re getting the output back. The output is what we paste back in cell B4, right here. You can use the documentation on OpenAI to help you set this up. And in my case, I got stuck and I asked ChatGPT for help and it was able to solve the problem. So, this part was a collaboration with ChatGPT. We got it to work. Then, I went on to add this part that allows me to check whether I have more than one line, and if I do, it’s going to split it and paste it into separate cells. So, this is where that happens. We are using the “Split” function. This basically splits the string into a list of smaller strings and puts it inside an array. Then, we loop through that array. For each instance, I’m checking whether the array length is greater than zero, and the reason I’m doing that is because sometimes the AI returns empty lines in between, and if I don’t account for this, I end up having empty rows here, and I don’t want that. I just want my rows with text to be below one another. OK, so that’s basically the script, and you can build upon this as you need. I so wish I had this when I was starting to learn Excel. There were times I could spend hours just getting my formula to work. Let me know what you think. I personally enjoyed this experiment just to see the potential we could have in Excel. Who knows, maybe in the future, we can ask it to import the data from Bob, summarize it, and then send it to the boss. Right? Something to look forward to. But let me know what you think. Comment below. I hope you enjoyed this experiment, and I’ll catch you next time.