using ChatGPT in Google Sheets

A quick easy guide on how to write an App Script function in Google Sheet to call OpenAI’s ChatGPT, or strictly speaking, OpenAI’s API ‘completion’ method using the ‘text-davinci-003’ model. In this example, I am using the AI to interpret and classify the main reason or sentiment behind some product reviews left by buyers, e.g. reviews you might get from an Amazon on-line boutique.

upload successful

Step 1. Create a sheet in Google Sheet (as above) with Review Comments in Column A. Cell C1 is a comma separated list of the classification you want ChatGPT to respond with when it reads through all the comments in Column A. Column B hold the cells that call the App Script function ‘callchatGPT’. In Cell B2, insert the formula as follows:

1
=callchatGPT("Respond with the main reason from choices in '"&$C$1& "' for this product review: '"&A2&"'")

Step 2. From the Extensions menu in your Google Sheet, create an App Script, give it a name, and paste in the function below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
function callChatGPT(input) {
var response = UrlFetchApp.fetch("https://api.openai.com/v1/completions", {
"method": "post",
"headers": {
"Content-Type": "application/json",
"Authorization": "Bearer YOUR-SCERET-KEY"
},
"payload": JSON.stringify({
"prompt": input,
"model": "text-davinci-003",
"max_tokens": 250,
"temperature": 0.4
})
});
var json = response.getContentText();
var data = JSON.parse(json);
return data.choices[0].text;
}

Step 3. Head over to Open AI, and get a Secret Key from here – https://beta.openai.com/account/api-keys and copy key to clipboard.

Step 4. Go back to your App Script window, replace YOUR-SECRET-KEY after the “Bearer “ above by pasting the key from the clipboard.

Step 5. Save the script, and return to your sheet. Drag down the formula from Cell B2 and wait for the API to respond.

Watch the guide in action on YouTube here: https://www.youtube.com/watch?v=b3sy01YmRF4

Note: if you have a free account, you will be limited to around 20 queries/calls per minute.