This guide will explain how to use ChatGPT to automate your Google Sheets workflows.
ChatGPT is a powerful AI that can help users create macros through a convenient chatbot system.
ChatGPT is a powerful chatbot service by OpenAI that can mimic natural human-like conversation. The AI was trained on a massive amount of data found on the Internet. This gives the chatbot enough knowledge to answer questions from almost any topic you can think of.
While ChatGPT can answer questions directly, users can also command the chatbot to create content. For example, you can write a prompt like “Write a cover letter for a Junior Data Analyst position for <Company X>” and ChatGPT will respond with a coherent email.
How can we use ChatGPT to our advantage when working with Google Sheets?
If your goal is to automate your workflow in Google Sheets, the solution will ideally start with creating a macro of your workflow. In cases where recording a macro is insufficient, we will need to either use a third-party plugin or create our own custom Apps Script code.
Since ChatGPT knows how Google Sheets and Apps Script work, users can ask ChatGPT to create the necessary code for their workflow.
Let’s take a look at a quick example!
Suppose you want to update a cell with the current date every time an edit is made in a particular range. This will help users understand when was the last time changes were made to a given table.
Instead of creating the code from scratch, you can ask ChatGPT the following prompt:
“Write a Google Apps Script code that will write the current date and time in cell G1 when any edits are made in columns A through F.”
After a few seconds of computation, ChatGPT will provide the actual code you’ll need to automate the process. The response may also provide additional information on how to run the script yourself.
Now that we know when to use ChatGPT to automate workflows in Google Sheets, let’s take a closer look at an actual sample spreadsheet that runs AI-generated code.
A Real Example of Using ChatGPT to Automate Workflows in Google Sheets
The following section shows an example of how to use ChatGPT to automate workflows in Google Sheets. We will also explain the formulas and tools used in these examples.
First, let’s take a look at our sample problem.
The table below records the daily sales of different branches of a T-shirt store.
Each branch is responsible for updating the numbers in the tracker. Every time an edit is made, we want to record the current date and time in cell G1.
We can ask ChatGPT to create an Apps Script code to set up this workflow without writing a single line of code.
ChatGPT will respond with an Apps Script function that you can copy and paste into the Apps Script code editor.
For example, my prompt to ChatGPT returned the following formula:
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
if (range.getColumn() >= 1 && range.getColumn() <= 6) {
sheet.getRange("G1").setValue(new Date());
}
}
After saving the function, Google Sheets should now edit cell G1 with the current date and time when the range is modified by any user.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out ChatGPT yourself, head over to the next section to read our step-by-step breakdown on how to do it!
How to Use ChatGPT to Automate Workflow in Google Sheets
This section will guide you through each step needed to start using ChatGPT to automate workflows in Google Sheets. You’ll learn how to create a prompt for ChatGPT to return a working Apps Script code snippet you can add to your project.
Follow these steps to start using ChatGPT:
- First, the user must have an OpenAI account to access ChatGPT. New users can sign up for ChatGPT through their Sign Up page.
- Once you are logged in, you will now have access to the ChatGPT chatbot service. Click on the textbox at the bottom of the screen and begin typing your prompt.
We recommend starting with a phrase like “Write a Google Apps Script code that…” followed by a clear description of what you want the Apps Script code to do. - Hit the Enter key or click on the arrow icon in the corner to send the prompt to ChatGPT. After a few seconds, ChatGPT should respond with an Apps Script code snippet.
Click ‘Copy code’ to save the code snippet to your clipboard. - Next, return to your Google Sheets project. Select the Apps Script option under the Extensions menu.
- Paste the AI-generated code snippet into the code editor.
- Click on the Save project icon to add the Apps Script code to your current project.
- The Apps Script code should now run when using the spreadsheet.
- You can ask ChatGPT to make additional changes to your Apps Script code.
- Paste the new changes into the Apps Script editor again and click the Save project icon.
In this example, we’ve added another line to set the edited range to a bold font.
These are all the steps you need to use ChatGPT to create an Apps Script code.
This step-by-step guide should provide you with all the information you need to begin using ChatGPT to automate your workflow in Google Sheets.
We’ve shown you how to use ChatGPT to create Apps Script code you can run in your spreadsheets to automate certain tasks.
Using ChatGPT with Google Sheets is just one way you can improve your spreadsheet workflow. Our website offers hundreds of other functions and methods to help you get more out of Google Sheets.
With so many other Google Sheets functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
