How to connect ChatGPT with Google Sheets

I love Google Sheets. It's my favorite tool for handling both work and personal tasks. In fact, I spend more time than I care to admit each day deep in a spreadsheet. So, when I realized that I could use Zapier and ChatGPT to dramatically increase the power and functionality of my Google Sheets, I knew I needed to explore that. 

With a Zap—Zapier's automated workflows—you can send data from a new row in Google Sheets to ChatGPT, ask ChatGPT to generate relevant text, then update your Google Sheet row with that new data. The possibilities for this type of workflow really are endless. You could draft content from a series of keywords, generate detailed product descriptions, translate text to different languages, or write responses to customer feedback, and have it all neatly stored in your original spreadsheet. 

Whether you're a Google Sheets power user like me or simply looking to streamline your work, this guide is for you. Leveraging ChatGPT within Google Sheets can significantly increase the power of your data while also saving you time. 

Before you begin

To start, you'll need a Google Sheet to use for this Zap. This Google Sheet will contain information that you will send to ChatGPT so that it can generate its text responses. You should also leave a blank column which will store the responses from ChatGPT. 

The specifics of your Google Sheet setup will depend on your exact use case. For my example, I'm using a Google Sheet with customer reviews from my Etsy shop. This sheet contains 3 columns of data that I will send to ChatGPT: Customer Name, Star Rating, and Customer Review. It also has a 4th column called Response that will store the text responses from ChatGPT. Ultimately, I'll be using these responses from ChatGPT to reply to my customer reviews on Etsy, which will help me to increase my engagement with my customers. 

Here's what my Google Sheet looks like:

You'll also need an OpenAI account if you don't already have one. Go to platform.openai.com to sign up for your account. 

Once those steps are out of the way, we can move on to building out the Zap.

How to enhance Google Sheets with ChatGPT

Zapier lets you create automated workflows called Zaps, which send your information from one app to another. You can create your own Zap from scratch without any coding knowledge, but we also offer quick templates to get you started. 

If you'd like to start with a template, click on it to go to the Zap editor. You'll need to create a Zapier account if you don't already have one. Then, follow the directions below to set up your Zap.

Set up your Google Sheets trigger

First, set up your trigger—the event that starts your Zap. If you're using the Zap template, the trigger app and event will already be selected for you. Otherwise, search for and select Google Sheets for the trigger app and New Spreadsheet Row for the trigger event.

Next, connect your Google Sheets account, if you haven't already, then click Continue.

Now, set up your action step. In the Spreadsheet field, select the Google Sheet you'd like to use for this project. I selected the Artful Data Reviews sheet for mine. In the Worksheet field, select the worksheet that contains your data. My data is in the default Sheet1 worksheet, so I'm leaving that.

Click Test Trigger, and you should see a recent row from your spreadsheet, like this:

Select a row—this will be used to set up the rest of your Zap—and click Continue with selected record.

Set up your ChatGPT prompt

If you haven't set up your Zapier connection to OpenAI, you'll need to do that now. To start, go to platform.openai.com to log into your account. Expand the left sidebar and click API keys:

Next, click + Create new secret key to generate a new API key. You'll need this key to connect your OpenAI account within Zapier. Copy this key and store it somewhere safe.

Now it's time to add your ChatGPT action step. If you're using the Zap template, the action step will already be added for you with the action app and event selected. If you're building your Zap from scratch, click the + button to add another action step, then search for and select ChatGPT for your action app and Conversation for your action event.

Click on Account, and set up a new account by providing the API key you generated in OpenAI. If you already have set up your account connection, simply select your existing account.

Once that's set up, you can start customizing your action.

First, you'll need to create your prompt for ChatGPT, which you'll add to the User Message field.

In your prompt, you need to provide ChatGPT with information about the data in your Google Sheet and what you'd like it to do for you. The specifics of what you prompt ChatGPT with will depend on the task you want to accomplish. 

In my case, I want it to generate responses to my customer reviews on Etsy. I started by telling ChatGPT what to expect (a review for my Etsy shop) and what I need (a response coming from me). I also provided more information about my typical relationship with customers to guide the tone of the response.

Next, I provided ChatGPT with more context about my business and what I sell to help it add specific details in the response. I then gave ChatGPT some specific instructions about what to include in the response. Finally, I mapped the relevant fields from my Google Sheets trigger. In this case, that's the Customer Name, Star Rating, and Customer Review.

Here's my prompt, but you should customize all of these prompt details to the preferences and requirements of your business. 

I run an Etsy shop selling customized art prints. I recently received a new review from a customer. I'd like you to respond to this review as if you are me. Because my products are custom art pieces, I have typically had a lot of back-and-forth discussion with each customer, and you can convey that in your response. 

My main products include custom street maps, soundwaves made from audio files or songs, and star maps of the night sky. Typically, my customers purchase these items as gifts for others. The most common occasions for these gift purchases include anniversaries, engagements, weddings, mother's day, and father's day. Some of my art is printed on paper (framed and unframed), and some of my art is printed on metal (usually these are traditional anniversary gifts for 7 year, 8 year, 10 year, 20 year, 25 year, and 50 year anniversaries)

Keep your entire response in a single paragraph. Do not sign the response with my name. Do not use words like 'us' and 'we' in your response. Instead, use 'I', and 'me'. Your response should be friendly in tone and BRIEF. 

-----

Customer Name: {Map Customer Name from Google Sheets Trigger}

Star Rating: {Map Star Rating from Google Sheets Trigger}

Customer Review: {Map Customer Review from Google Sheets Trigger}

-----

Once you've added your prompt to the User Message field, select the GPT model you'd like to use (depending on what OpenAI account you have). You can leave the remaining fields as-is.

This is what my completed action step looks like:

Click Test Step to send the spreadsheet data over to ChatGPT and wait for it to generate a response. Review the response to make sure everything looks okay. You may want to spend some time experimenting with your prompt, as dialing in these details can significantly improve the responses you get from ChatGPT. 

Once it looks good, you're ready to continue to the next step to send responses back to Google Sheets.

Send responses back to Google Sheets

To get your ChatGPT response back in your original Google Sheet, you'll need to add one last action step. 

If you're using the Zap template, the action step will already be added for you with the action app and event selected. If you're building your Zap from scratch, click the + button to add another action step, then search for and select Google Sheets for your action app and Update Spreadsheet Row(s) for your action event.

Connect to the same Google Sheets account that you used for your trigger step. 

Next, we'll set up the action step. You'll want to connect to the same Google Sheet you used to trigger this Zap, so select the same Drive, Spreadsheet, and Worksheet.

In the Response field, map the response from your ChatGPT step by clicking in the field and selecting Reply from the dropdown.

In the Row Number field, select the Row Number from your Google Sheets trigger step to make sure the response goes to the correct row in your spreadsheet.

Your action step should look something like this when complete:

Next, click Test action to send the reply from ChatGPT to your Google Sheet. Check out your Google Sheet, and if everything looks right, you're now ready to use your Zap.

Integrating Google Sheets, ChatGPT, and Zapier unlocks a realm of possibilities for automating and enhancing your workflows. This Zap not only streamlines data processing but also dynamically updates and enriches your spreadsheets with generated content. Whether you're responding to customer reviews, writing content, or creating product descriptions, this setup empowers you to manage and maximize your data with unprecedented efficiency.