Get your API key

1. Head over to the User settings page on the OpenAI website.
2. Click the “Create new secret key”.
3. Make sure to copy the created key, and save it somewhere safe. You will not be able to retrieve it later. (You will be able to create new keys, but just make sure that you don’t lose the one you have and you’ll be all fine.)

Open the Google Sheets script editor

1. Open a new Google Sheets (or an old one, that really doesn’t matter).
2. In the Add ons/Tillägg menu, select “Apps script”, and a new tab will open, and it should look like this:

1. Remove the little piece of code, so it’s all empty.
2. Paste this code:

var secretKey = '$SECRET KEY$';

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Trickle')
      .addItem('Use AI to generate formula', 'genForm')
      .addToUi();
}

function genForm()
  {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('AI prompt', 'Describe your desired outcome as precisely as possible', ui.ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() == ui.Button.OK) {
    SpreadsheetApp.getActiveSheet().getActiveRange().setFormula(askTheAI(response.getResponseText() + '. Use semicolon as the parameter delimiter.'));
    } 
  }

  /**
 * Use GPT-3 to generate a response
 * 
 * @param {string} topic - the topic for the article
 * @return {string} the generated article
 * @customfunction
 */
function askTheAI(_prompt) {
  var _model = 'text-davinci-003';
  const api_endpoint = '<https://api.openai.com/v1/completions>';
  const api_key = secretKey;
  const api_params = {
    prompt: _prompt,
    max_tokens: 1024,
    temperature: 0.7,
    model: _model,
  };
  const response = UrlFetchApp.fetch(api_endpoint, {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + api_key,
      'Content-Type': 'application/json',
    },
    payload: JSON.stringify(api_params),
  });
  var json_resp = JSON.parse(response.getContentText());
  var lines = json_resp.choices[0].text.split('\\n');
  lines.shift();
  lines.shift();
  return lines.join('\\n');
	}

1. On the top line, replace $SECRET KEY$ with the secret key that you generated above. So if your secret key is helloworld the topmost line should look like this:

var secretKey = 'helloworld';

1. Press CMD+S to save.
2. In the function dropdown, select “onOpen”, and then click Kör/Run:

1. Google Sheets will now ask you to approve it. Just do that. And remember that if you share this sheet with anyone, they will need to approve the script as well.

Call the custom function

What you have done, is that you have created a very own custom function in this Google Sheets file. Just as =AVERAGE() is a function that calculates the average value of a set of values, =LEN() counts the number of characters in a string, you can now use the function =askTheAI() to send prompts to ChatGPT: