Data

How to Use ChatGPT with Any Google Spreadsheet

Automating everyday manual tasks has been one of the highest-leverage productivity hacks I've found for LLMs. Nowhere have I seen more manual tasks than in spreadsheets (I even cofounded a company to help save biotech accountants from some of this manual work).

One of my favorite applications is using OpenAI's GPT API directly inside of Google Sheets. I find it's helpful to quickly ask the same question, do customized template generation, or clean up text on 100+ different cells of data all at once e.g. "Can you give me the city and state for each of these people based on their bios in the format city, state e.g. Boston, MA?"

It's also very helpful for generating sophisticated formulas and visualizations.

Someone said I have "spreadsheet superpowers." Here's how you can have them too:

Demo

Setup

  1. If you don't have one, create an OpenAI account and an OpenAI project with an OpenAI Key (platform.openai.com/api-keys).
  2. Save the OpenAI Key (usually something like sk-....
  3. Create a new Google Spreadsheet (sheets.google.com).
  4. Go to Extensions -> Apps Script in the Menu bar. This will open a new tab with a code editor.
  5. Delete the default function and copy and paste the "GPT for Spreadsheets Apps Script" code into the editor at the end of this post.
  6. Change <REPLACE WITH YOUR OPENAI SECRET KEY> inside of the quotes to the OpenAI key you created earlier.
  7. Click Deploy -> New Deployment. In the dialog click the gear to the right of "Select type," choose "Web App," use the default settings, and click "Deploy."
  8. Click "Authorize" and authenticate with your Google Account.

    If a dialog warning pops up saying "Google hasn't verified this app," click "Advance" and then click "Go to Untitled project (unsafe)" and authorize the application. This screen pops up because you're running a custom app (e.g. the script we just pasted in).
  9. You can now go back to the spreadsheet tab and try using the GPT function.

How to Use GPT for Google Spreadsheets

You can now type in =GPT(reference 1, reference 2, ...) as a formula into any spreadsheet cell just like you, just like any other Google Spreadsheets function.

The function can take any number of references that are either strings of text (e.g. "Here's an example string." or a reference to a cell e.g. A1. It will append all the strings together, send them to OpenAI's API, and return the result response.

For instance: =GPT("Can you give me the city and state for the following description after the colon?:", A2)

Tips to Get Good GPT Spreadsheet Output

  • Descriptive Prompts - Be descriptive and specific with your ChatGPT prompting. It's especially helpful to specify the type of structured output you want in the cell and give an example. For instance, a better version of the above GPT prompt would be:
    =GPT("Can you give me the city and state for the following description after the colon? Please give me the format city, state e.g. Chicago, Illinois and do not give any other extraneous text:",...).
  • Spaces for Multiple Inputs - If you have multiple inputs, make sure to include spaces, e.g =GPT(I have a question about these three things: ", A1, " ", B1, " ", C1).
  • Paste Values - Just like any other Google Spreadsheets function, the GPT function will run every time you re-open the Spreadsheet or one of the referenced cells changes. Once you have the output you want, I recommend making a copy of the outputs by selecting the GPT cells, copying Cmd+C (Mac) Ctrl+C (Windows), and then pasting by values Cmd+Shift+V (Mac) Ctrl+Shift+V . This will save the raw text output of the GPT formula, so it doesn't keep calling the API and you can manipulate it in your spreadsheet how you see fit.
  • Using ChatGPT to Write Other Formulas - Separate from Google Apps Script, everyone can now be a Google Spreadsheets formula wizard using ChatGPT. I've asked OpenAI's chat app to make some incredibly sophisticated formulas that have been invaluable for cleaning and visualizing data on the fly.

ChatGPT for Spreadsheets Apps Script Code

/*
 * Usage: =GPT(<string or cell reference 1>, 
 *             <string or cell reference 2>,...)
 * e.g. =GPT("Give me the city from this text: ", A1)
 *
 */

/* 
 * Replace with your OpenAI API key.
 * Tweak parameters as you see fit.
 */
const OPENAI_SECRET_API_KEY = "<REPLACE WITH YOUR OPENAI SECRET KEY>";
const MAX_TOKENS = 3000;
const TEMPERATURE = 0.9;
const MODEL="gpt-4o";

function GPTDetailed(prompt, temperature = TEMPERATURE, model = MODEL, max_tokens = MAX_TOKENS) {
  const url = "https://api.openai.com/v1/chat/completions";
  const payload = {
    model: model,
    messages: [
      { role: "system", content: "You are a helpful assistant." },
      { role: "user", content: prompt },
    ],
    temperature: temperature,
    max_tokens: max_tokens,
  };
  const options = {
    contentType: "application/json",
    headers: { Authorization: "Bearer " + OPENAI_SECRET_API_KEY },
    payload: JSON.stringify(payload),
    timeoutInSeconds: 60
  };
  const res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
  return res.choices[0].message.content.trim();
}

/* Allow for a variable number of arguments to GPT and connect them with spaces */
function GPT(...prompts) {
  var prompt = "";
  for (var i = 0; i < arguments.length; i++) {
    prompt = prompt + " " + arguments[i];
  }
  return GPTDetailed(prompt);
}

Read more