Using ChatGPT in Google Sheets

I use R and Python when working with large datasets or performing tasks that are either tedious in a spreadsheet or involve statistical functions that are difficult or impossible to execute in a traditional spreadsheet environment. Once you become proficient in R, manipulating data in spreadsheets often feels cumbersome.

That said, spreadsheets remain an excellent tool for sharing and communicating tabular data, and as a platform, Google Sheets is hard to beat.

Have you ever had a column filled with descriptions, titles, foreign language text, or other content that you wish you could normalize or extract? Sometimes, such data isn't easily handled by regular expressions, split(), find(), or other spreadsheet functions.

Lately, I’ve been relying on a quick Google Sheets hack that leverages ChatGPT to do the heavy lifting. Over time, I've customized it for various applications, but here, I want to share the basic code for making a simple query. Don't forget to read a little warning about GoogleSheets and custom formulas at the end of this entry.

The Google Apps Script code

I’ve written a function called queryGPT() that takes a prompt and cell content as arguments and returns ChatGPT’s response. (Apologies for the formatting—LinkedIn isn’t ideal for showcasing code.)

const role = {
  "role": "system",
  "content": "You are a data analyst performing tasks on a spreadsheet."
};

function queryGPT(prompt, content){
  var payload = {
      model: 'gpt-4o',
      messages: [
        role,
        {
          "role": "user",
          "content": prompt + ": ```" + content + "```" 
        }
      ]
    };
  return sendToGPT(payload);
}

function sendToGPT(payload){
  const oai_key = "sk-proj-**************";  // replace with your own key
  const url = "https://api.openai.com/v1/chat/completions";

  var options = {
      'method': 'post',
      'contentType': 'application/json',
      'headers': {
        'Authorization': 'Bearer ' + oai_key, 
        'Content-Type': 'application/json' 
      },
      'payload': JSON.stringify(payload)
    };

    var response = UrlFetchApp.fetch(url, options);
    var ct = JSON.parse(response.getContentText());
    return ct.choices[0].message.content;
}        

What you’ll notice first is that we define a role for ChatGPT as a data analyst working on a spreadsheet. This gives the model some context for the kind of tasks it’s handling.

Next, we format a payload to send to OpenAI, which combines the prompt and content wrapped in triple backticks. In earlier versions of ChatGPT, using backticks helped isolate the content from the context. While I haven’t tested whether that’s still necessary with newer models, old habits die hard.

The payload is sent to a function called sendToGPT(), which uses the OpenAI completion API. I keep this in a separate function because I have other helper functions (besides queryGPT()) that assist with things like summaries, translations, or processing multiple cells of data. By keeping all transactions with OpenAI in one place, the code stays cleaner and easier to maintain.

Simple as that! Now, here’s how to use it:

In your Google Sheet, in the cell where you want the result of your query, just type:

=queryGPT("Search this text for any city and state and return the city and two-letter state abbreviation. Do not include anything except the city and the state.", A2)        

In this example, "A2" refers to the cell containing the text you want to query. You can then copy or drag that query down the column, and each cell will execute the same query.

Below, you can see an example of how I’ve applied this. Once the query is set up, I simply drag the formula down column L or double-click to autofill the column. This quickly provides a TRUE/FALSE result, indicating whether the study includes "ORR" (Overall Response Rate), regardless of whether the text is capitalized, in lowercase, if the abbreviation is used, or even if it’s misspelled.

Here are just a few ideas on how you can use this...

• Translate this text into English. If the text is already in English, return it exactly as is.
• What language is this text? Return the language and nothing else.
• Find any geographic locations in this text and place them here, separated by a newline. Do not return any other information.
• For every brand-name drug in this text, return the generic equivalent, separated by a semicolon. Do not return anything else except the generic names.
• Does this text refer to a clinical case or a clinical trial? Return CASE if it's a case or TRIAL if it's a trial.
• Correct this text for style and grammar.
• Standardize this address to a proper format. Return only the corrected address.
• Provide a one-sentence summary of this text.
• What diseases are mentioned in this text? Return the disease name and its ICD-10 code, nothing else.
• What CPT codes are suggested by this visit summary? Return the codes only, separated by commas.
• ...

You'll notice that I often say "return" (as in the return/function vocabulary), and that is because this seems to work well in this context. I also often have to remind it to only return the information I want and not a sentence about it. If I don't the reply will be something like "The content mentions Niaux, France." when all I wanted was "Niaux, France."

If you haven't made an Apps Script before...

That's all there is. If you've never made an Goole Apps Script before, the way to do it is to select "Apps Script" under "Extension"

You'll then copy and paste this code into the Apps Script project, update the oai_key variable with your OpenAI API project key and save it. You can give it a name. When you first run it, it will ask for permission because it is passing data to a third party (OpenAI).

So, I guess the final note here is why not just use Gemini in Google Sheets? The answer is that not every Sheets user has access to Gemini. The other answer is that once you've paid for API access to one LLM, unless your area of research is comparing LLMs, you kind of stick with that, and in my case.

?? I have one final word of warning and it's really important or you'll pay for queries you don't need: Every time the sheet changes or you load it, this will make another call to OpenAI. Google Sheets constantly re-runs formulas unless you tell it to stop. So once you've got your results, copy and paste as value (Command-C, Command-Shift-V) in the same location so that it's not making the query again.