Turbocharge Your Google Sheets with GPT-4

ChatGPT is great, but have you tried using it directly with Google Sheets (without 3rd party software)? With a few simple tricks, you can use your data in your prompts, and get it processed in a smart way by GPT-4.

In this tutorial, I will demonstrate how we use the intelligence of GPT-4 with Google Sheets at Meetings.bio to automate otherwise unscalable tasks that we would need to do manually.

PLEASE NOTE: Even though GPT-4 API pricing is relatively affordable ($0.03 per ~800 words of prompt and $0.06 per ~800 words of responses), things could get out of hand, if you want to process thousands or tens of thousands of rows. “Luckily”, you will start getting rate limit errors from OpenAI API after processing dozens of rows at a time, so you will need to do some manual manipulation for hundreds or thousands of rows anyway. Still, make sure your API usage limits are properly set in OpenAI account settings (they are by default).

Making GPT-4 available in Google Sheets

Requirements

To get started, you will only need a Google account (so you can use Google Sheets). Getting everything else will be explained below. Please note that for this to work, you need a pay-as-you-go OpenAI account, so you will need to configure billing in your OpenAI account. However, API calls are relatively low cost—GPT-4 API calls cost you $0.03 for ~800 words of prompt, and $0.06 for ~800 words of response. Unless you need to process thousands of Google Sheet rows and/or use really large prompts, it will not be really expensive.

OpenAI account and API key

If you don’t have one, you need to create an OpenAI account (this is NOT a ChatGPT or ChatGPT Plus account!). Here’s how you create one:

1. Log into OpenAI with your Google Account here (click Continue with Google).
2. Enter your name, company name (if relevant), and birth date.
3. Verify your phone number.
4. Select “API – Integrate OpenAI models into your application or business”.
5. Set up billing information here.
6. Click “Set up paid account” and then enter your billing information and click “Set up payment method”.

Also, you will need an API key for this to work. Navigate to API keys and click “Create new secret key” and then type in a nickname for your key (can be whatever). Make sure you don’t share this key with anyone. Copy/paste it somewhere as you will need it later.

Set up Google Apps Script

Open (or create) the Google Sheet you want to use GPT-4 with. In the menu, select “Extensions” and then “Apps Script”. A code editor like this will open:

Delete all the content in the right side of the window (starting with “function myFunction…”) and then copy paste code below in to the same window, also replacing YOUR_API_KEY with the OpenAI API key you saved earlier:

Click the “Save Project” button (CTRL+s or CMD+s) and close the “Untitled project – Project Editor” browser tab. Congrats! You’re ready to use GPT-4 in your Google Sheet now. In a nutshell, this is how you use it: In the cell, you want to generate GPT-4 output in, write a formula using GPT_GENERATE() function, and provide it with the prompt you want to get a GPT-4 response to. That’s it, it’s really that simple.

Let’s look at a few real-world examples of using GPT-4 with Google Sheets.

Use Case #1: Sorting email responses by level of excitement

For a lead generation company, positive responses to email campaigns we send on behalf of our clients, are the most beautiful thing to read. We’re just updating our website, and I thought why don’t we include some of the most upbeat positive responses (anonymized, of course) on our website, as a testimonial to the quality of our service?

One could spend hours digging through hundreds of positive responses we received over the past few months, but there’s a better way to do it—by using GPT-4 to do sentiment analysis on our data. We only need the list of all responses and a Google Sheet configured as specified in the instructions above.

First, I exported the ~800 such positive responses we received in the past few months from various prospects, from our database into a Google Sheet (shown anonymized):

Now, I want to assign every response an “excitement score” between 0 and 100, so I can then sort responses by excitement level, and only pick the most exciting for our website. I created a new column called Excitement score, and now I can use the following Google Sheets formula in the cell B2:

This will produce the following result—note the “60” score in the cell B2. This value was provided by GPT-4:

The only thing left to do now is to expand the formula to the remaining cells in column B, so we calculate scores for the rest of responses:

Voila, and there they are, all the positive responses scored by their level of excitedness (as per GPT-4, which is not perfect, of course). Some of the responses had scores above 90, which were indeed more interesting and sounded more upbeat than the rest.

Doing this task with Google Sheets and GPT-4 saved hours of time of our account managers manually sifting through past responses.

Use Case #2: Converting full company names into nicknames

Let’s say you want to convert full company names into “handles”, i.e. shorter names you can refer to in an email, for example.

This is useful if you’re sending email campaigns and want to personalize emails per-prospect with their company name (but don’t want to use “ACME, Inc.”, because that sounds unnatural).

This is very easily doable with the Google Sheets + GPT-4 approach described above.

Let’s say we have the following list of company names:

Now, I want to derive a Company Nickname for every Full Company Name in the list. I created a new column called Company Nickname, and now I can use the following Google Sheets formula in the cell B2:

This will produce the following result—note the nickname in cell B2. This value was provided by GPT-4:

The only thing left to do now is to expand the formula to the remaining cells in column B, so we get the nicknames for all company names:

Wrapping up

I hope you found this guide helpful. It’s not difficult to imagine tons of other potential uses for such Google Sheets + GPT-4 integration.

Oh, there’s one thing that might make your life easier when using this approach: when you have generated responses in desired cells, it make sense to do a simple 1) “copy” and 2) “paste values only” on those cells, as this will ensure those cells won’t get recalculated when you manipulate them further (or reopen the sheet), and that you won’t get charged multiple times for the same result.

Good luck!