Calling APIs (including ChatGPT!) from Google Sheets

Spreadsheets are powerful for many reasons. One big one: they help users engage in functional programming without even realizing they’re programming! Any time you type in a function, like =AVERAGE(A1:A10) you’re essentially programming: selecting inputs, engaging a processing step, and directing the output to the cell you typed the command in.

Because spreadsheets have democratized access to programming, I’m not disturbed that Excel (and Google Sheets) are cornerstones in most companies today. You won’t find me ripping out Excel and replacing it with enterprise components or products in a modern data stack: I like to find ways to meet people where they’re at, within the interface they’re most comfortable. In the AI product I’ve been building for the past year, I’ve chosen to expose some API endpoints I’ve written within the Google Sheets that administrators are already spending all their time in.

How do you do it? Use Google Apps Script, which you can access from any Google Sheet:

The code editor is inside the “Apps Script” option. Go there.

To call an external API, assuming that you’ve been provided a bearer token to authorize access to that API endpoint, do something like this:

function yourAPIFunctionName(input) {
  var baseUrl = 'https://your.api.host/yourAPIEndpointName';
  var bearerToken = 'longStringOfNumbersAndLetters';
  
  var queryParam = '?nameofAPIinput=' + encodeURIComponent(input);
  var fullUrl = baseUrl + queryParam;
  
  var options = {
    'method': 'get',
    'headers': {
      'accept': '*/*',
      'Authorization': 'Bearer ' + bearerToken
    }
  };
  
  try {
    var response = UrlFetchApp.fetch(fullUrl, options);
    Logger.log(response.getContentText());
    return response.getContentText();
  } catch (e) {
    // Log the error if the request fails
    Logger.log('Error: ' + e.toString());
    return 'Error: ' + e.toString();
  }
}

Click the “Debug” button to make sure you’ve edited the boldfaced parts to call your API correctly. When the function can run in the code editor, now you can go back to your spreadsheet and use your function.

For example, if your input is contained in cell A1, you could write this in cell B1 to execute your function: =yourAPIFunctionName(A1)

Troubleshooting: If these steps don’t work, try (in this order) adding Sheets API as a service, and asking your company’s Google administrator to make sure that permissions are set appropriately for you to use Google Apps Script.

You can use a similar process to call the ChatGPT API if you have a subscription:

 /* Uses code from https://www.youtube.com/watch?v=Qg6uq3w9BNE */

const OPENAI_ENDPOINT = "https://api.openai.com/v1/chat/completions"
const OPENAI_APIKEY = "the-api-key-you-got-from-chatgpt"

async function GPT(prompt) {
  const payload = {
    "model": "gpt-3.5-turbo",
    "messages": [
      {
        "role": "system",
        "content": prompt
      }
    ]
  }

 const res = await UrlFetchApp.fetch(OPENAI_ENDPOINT, {
    method: "post",
    contentType: "application/json",
    headers: {
      "Content-Type": "application/json",
      "Authorization": `Bearer ${OPENAI_APIKEY}`
    },
    payload: JSON.stringify(payload)
  })

  const data = JSON.parse(res.getContentText()).choices[0].message.content;
  console.log('Our data is: ', JSON.stringify(data));
  return data;
}

Exposing functions through Google Sheets can be a quick, dirty, and totally fine way of providing functionality to users without having to spin up a web API that they might not go to anyway. Meet your uses where they’re already living… and if that place is Google Sheets, add this tool to your toolbox.