Zapier makes it incredibly fast and easy to connect Microsoft Excel and ChatGPT together. We look at the Zapier process automation platform and how to set it up to connect it with ChatGPT.
What is Zapier?
Zapier is a process automation platform that is very similar to Microsoft Power Automate Flows. It’s designed to simplify and streamline integrating various online applications. It supports over 5,000 app connections and can integrate popular accounting software such as QuickBooks and Sage along with all major cloud document services like OneDrive, Google Drive, Google Sheets, Microsoft Excel Online etc.
Companies can use Zapier to effectively bridge thousands of web apps without requiring any coding knowledge. Zapier harnesses the power of APIs to trigger actions, known as “Zaps” to automate repetitive tasks. Companies can benefit from system integration by improving productivity, efficiency, and data accuracy.
Zapier and Power Automate Flows are a pivotal component in the world of digital automation, fitting perfectly into any business that aims for robust digital transformation and growth hacking strategies.
How does an API Work?
While not necessary for setting up a connection, it’s worth having a basic understanding of what an API is and how they generally work. At a high level, an API or Application Programming Interface is a set of rules for building and interacting with software applications. It’s a framework that allows computer systems to talk to each other.
To use an API, you will two pieces of information: typically need an API Endpoint URL. This is the location of the software on the internet or local network. The second part you need is an API Key. The API key is a unique identifier that’s used to authenticate access to the system.
Zapier already knows the Endpoint URL, but you will need to get an API Key from OpenAI, the creators of ChatGPT.
Setting up an OpenAI API Key
You can get an API key by logging into your OpenAI account, and navigating to the API keys section. Using the API currently costs money, and cost is based on usage. The more text you send to ChatGPT, the more you will be billed.
A credit card is required to be added to your account prior to being able to use the API key. Once the key is setup it takes a few minutes for other systems to register that it’s active.
Setting up an Excel Spreadsheet for ChatGPT
It’s important to note that Zapier is a cloud integration service. This means that your data has to be on the cloud. Zapier can connect to all major cloud platforms like Google Workspaces and Microsoft 365. Our example will use Microsoft Excel, but you can also do the same thing with Google Sheets.
First create a Table. Excel needs to have data in a table format so Zapier can reference the rows and columns of it.
To create a table, add some headers to the top row of your Excel spreadsheet. Select the cells and a few below it. Then go to Insert > Table. Once successful, the selected area will be highlighted and automatically be formatted.
In our example, we created an Excel Spreadsheet on OneDrive and added an example row asking to write an SEO optimized description for an article about Jello.
Create Your First Zap
Zapier uses web triggers or actions to start processing workflows. They call each process automation a Zap. Triggers could be anything that tells Zapier to start a process. For example, if you received an e-mail with a specific subject line that could start a Zap. Or, you can schedule a Zap to run every morning at 6am to consolidate some data and e-mail you a summary. The options are endless.
You can create a free account by going to https://www.zapier.com
We logged into our Zapier account and clicked on + Create New Zap. In our example, we don’t want to use a trigger, but will use a schedule to start it at a specific time of day.
You first have to choose the trigger which is when the Zap will start. The second step is to add an action.
We then setup our first action to be Update a Row in Excel. Our file is saved one OneDrive. We point Zapier to the specific OneDrive account, spreadsheet, sheet name, and Table name. This part is very specific as the software needs to know which cell, row or column to update. By using a Table it makes naming the columns and referencing a cell range even easier.
The Next Step is to add a Conversation in ChatGPT.
Do this by clicking the + button below the Excel action. This tells Zapier that step one 1 is to take data from each row of an Excel Spreadsheet. Step 2 is to send that data to ChatGPT.
When setting up the connection to ChatGPT, you will be asked for an OpenAI account and an API key. The API Key is from the OpenAI website. It will look like a big long string of text and numbers. Copy and paste it into Zapier. It’s used to authenticate your account.
Remember this is a paid service. OpenAI allows you to put some guardrails around the cost if you’re concerned about billing getting out of control. From the OpenAI API website, under billing you can set limits and warnings when you get up to a specific spending limit.
You can configure the ChatGPT API to use different models, along with how creative and how specific you want the response to be. Other settings involve setting up conversations where multiple outputs can be performed and referenced for an ongoing conversation.
The final step is to add an output for your data. The output is going to be the same spreadsheet as our input, but does not necessarily have to be. You could use Excel to load a large list of questions into ChatGPT and have it output to a completely different system if you want to.
We point the new Update Row in Microsoft Excel action to update the same Excel workbook, sheet and table. You also have to define which data is written to which column. Our data only has two columns, but it could be more. You then define what pieces of data go into which column.
Review Final Excel ChatGPT Output
Zapier will prompt you to test each part of your workflow as you’re building it to make sure that all parameters and settings are filled out. This helps ensure that you have a successful automation workflow run. Issues should be limited, but as a best practice we always check the end result to ensure that it meets expectations.
In the screenshot below, our columns are updated with the initial Question and the response from ChatGPT.
This example is fairly simple, but highlights the power of tools like Zapier and Microsoft Power Automate Flows. At the time of writing Power Automate Flows does not have an official connector to ChatGPT. It’s a bit odd as Microsoft has invested billions of dollars into OpenAI and is aggressively integrating ChatGPT into all of their products under the Microsoft Copilot 365 brand name. We expect that a native connector will soon be published by Microsoft but have no official word on timing.
Tools like ChatGPT become more powerful when combined with other systems. Zapier is a way for businesses to save time and money by integrating systems. They minimize the need for technical knowledge of how API’s work and make managing connections easier.
Do you Need Help Integrating Software?
Contact us today, if you need help integrating systems. Computers work better together when they can talk directly with each other. It drives efficiency by reducing manual processing and decreases the opportunity for data entry errors.