Skip to content

Instantly share code, notes, and snippets.

Last active January 25, 2024 17:34
Show Gist options
  • Star 5 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save tamsininnit/f31dc1023578e4dd3e49b3a6a112bd0f to your computer and use it in GitHub Desktop.
Save tamsininnit/f31dc1023578e4dd3e49b3a6a112bd0f to your computer and use it in GitHub Desktop.
// Add ChatGPT Menu
const onOpen = () => {
const ui = SpreadsheetApp.getUi();
.addItem("💾 Save Responses as Text", "saveAsText")
.addItem("ChatGPT API Sheet by Sarah Tamsin 💜", "openUrl")
const openUrl = () => {
const url = "";
const html = "<script>'" + url + "', '_blank');;</script>";
const ui = HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModalDialog(ui, "Redirecting...");
function saveAsText(){
// Saves formulas as plain text
var ss = SpreadsheetApp.getActiveSheet()
var data = ss.getDataRange().getValues()
function CHAT(val) {
// main function to generate the custom formula (=CHAT)
var ss = SpreadsheetApp.getActiveSheet()
// EITHER get API key from settings sheet
// var setsh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
// var apiKey = setsh.getRange(3,1).getValue() //Cell A3
// OR set API key
var apiKey = YOUR_API_KEY_HERE
// configure the API request to OpenAI
var data = {
"messages": [
{"role": "system", "content": "You are an experienced content writer with high levels of expertise and authority. Your job is to write content that will be published online on websites. [Your writing style is friendly, conversational using informal grammar and sometimes non-standard English - as if you're talking to a friend, while incorporating rhetorical questions, storytelling, metaphors and analogies.] I will provide you with a topic or series of topics and you will come up with an engaging and educational copy for this topic."},
{"role": "user", "content": "Here is your prompt:" +val +"\n"}],
"model": "gpt-3.5-turbo",
var options = {
'method' : 'post',
'contentType': 'application/json',
'payload' : JSON.stringify(data),
'headers': {
Authorization: 'Bearer ' + apiKey,
var response = UrlFetchApp.fetch(
// Send the API request
var result = JSON.parse(response.getContentText())['choices'][0]['message']['content'];
return result
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment