How to use GPT3 in Google Sheets

The OpenAI Generative Pre-trained Transformer 3 (GPT-3) is a cutting-edge natural language processing (NLP) model. GPT-3 is trained on a massive text dataset, allowing it to produce output that is more human-like than earlier models.

GPT-3 has been used to create text from several situations, including as creative writing, summarising, question-answering, and translation. It accepts text as input, understands the context of the input text and produces more human-like output than earlier models.

Google Sheets apps scripts is a robust scripting language that lets you automate Google Sheets functions like making charts and graphs, formatting cells, and sending emails.

In this blog post, you will learn how to add a button in the google Sheets ribbon and access GPT3 API using apps scripts. It helps you to fill your data sheet quickly and effectively.

GPT3 in Google Sheets Apps Scripts

Suppose you have a list of US States in column A2:A6 and need to fill state capital in column B2:B6.

Please follow the below steps to fill the cells B2:B6 in google sheets using GPT3.

Open your google sheets

Go to Extensions and Click Apps Script

/** This code add a button to the top ribbon of google sheets to fill the capital of the state using GPT-3 Language model **/

//Enter your API Key
var apiKey = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx;

var nTokens = 10;

function onOpen() {
  var ss = SpreadsheetApp.getActive();

  var mItems = [
    {name: 'Fill Capitals', functionName:"fillCapital"},
  ];

  ss.addMenu('FillCells', mItems);
}

function accessGPT3(quest) {
  var dValue = {
    'prompt': quest,
    'max_tokens': nTokens,
    'temperature': 0,
  };

  var keyValues = {
    "model": "text-davinci-002",
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(dValue),
    'headers': {
      Authorization: 'Bearer ' + apiKey,
    },
  };
   result = UrlFetchApp.fetch(
    'https://api.openai.com/v1/engines/davinci/completions',
    keyValues,
  );

  return JSON.parse(result.getContentText())['choices'][0]['text'] 
}

function splitResult(response) {
  var splitfill = response.slice(0, response.indexOf('Q: '));
  return splitfill;
}

function get_capital_of_state(bVal, state) {
  var quest = "Q: What is the " + bVal + " of " + state + "?"
  var result = accessGPT3(quest);
  var finalResult= splitResult(result);
 return finalResult;  
}

function fillCapital() {
  /* Select the range of cells A1:B6, where A1 is blank and B1 has the text Capital. */
  var activeSheet = SpreadsheetApp.getActive();
  var activeRange = activeSheet .getActiveRange();
  var nRows = activeRange.getNumRows();
  var nCols = activeRange.getNumColumns();

  for (var i=2; i< nCols+ 1; i++) {
    cellB1Txt = activeRange.getCell(1, i).getValue();

    for (var j=2; j< nRows + 1; j++) {
      cellA1to6 = activeRange.getCell(j,1).getValue();
      currentCell = activeRange.getCell(j, i);

      rslt = get_capital_of_state(cellB1Txt, cellA1to6);

      currentCell.setValue([rslt].toString().trim());
    }
  }
}

Copy and paste the above code into your apps script

Make sure to update the API KEY, SAVE your code and Close

Go to your google sheet and Reload the page. Now you can see a new Menu FillCells in the Ribbon

Select the range of cells A1:B6

Click the menu item Fill Capitals in the FillCells menu. Apps Scripts fill the Capitals for the respective US States using GPT3 API.

The Apps scripts form a question text based on the google sheets cell values. For example: form a question text based on cells B1 and A2. “Q: What is the Capital of Alabama?“, and passed to the GPT3 API and get the response. Later split the response and paste only the respective answer into the cells in column B2:B6.

Conclusions

Automation in Google Sheets is a strong and effective approach to optimise your productivity. It saves time and allows for more productive and accurate data analysis. Anyone may simply construct strong scripts and formulae to help them achieve their goals with the correct information and experience.

If you have any questions, please leave a comment below and I will get back to you as soon as possible.