MENU
お問い合わせ
  1. Home
  2. Google Sheets
  3. Link ChatGPT with Google Sheets: Boost Efficiency Using Google Apps Script and ChatGPT API

Link ChatGPT with Google Sheets: Boost Efficiency Using Google Apps Script and ChatGPT API

Google Sheets

If you want to integrate ChatGPT and spreadsheets to make your work more efficient, but don’t know how to do it, this article will show you the basic way to integrate ChatGPT and Google Sheets.

By utilizing Google Apps Script and the ChatGPT API, you can batch process questions and tasks on a spreadsheet and get real-time answers from ChatGPT.

TOC

Linking ChatGPT to Spreadsheets

  • Exchanging data via API
  • Using Google Apps Script
  • Fees are charged for API use.

The integration between ChatGPT and Google Sheets is done using an API (Application Programming Interface).

An API is an interface that allows two different systems to exchange data.

Through Google Apps Script, a prompt is sent from the spreadsheet to ChatGPT and the response obtained through the API is displayed in the google sheets.

The ChatGPT API has usage and cost limitations.（OpenAI Rates Page）

Be cautious, as usage fees may apply if the frequency of API requests or the amount of data increases.

Integration Steps

Here are the steps to integrate Google Sheets with ChatGPT.

Obtaining the API Key

STEP
Visit the OpenAI website

Access the OpenAI platform.

If you already have an account, click “Log In” to sign in.

If you don’t have an account yet, click “Sign Up” at the top right of the screen to register.

STEP
Open the Dashboard

After logging in, navigate to the OpenAI dashboard.

Open the “Dashboard” at the top right of the screen.

STEP
Open “API keys”

Open “API keys” on the left side of the screen.

STEP
Open “Create new secret key.”

Open “Create new secret key” in the center of the screen

STEP
Enter the name of the API key

Enter a name for the API key.

Then press “Create secret key”.

STEP
Copy and save the API key

The API key created is displayed.

This key appears only once, so copy it and save it in a safe place.

Managing API Keys
  • If you lose your API key, you cannot reacquire the same one.
    However, you can create a new key.
  • Be careful not to share or disclose API keys.
    If someone uses that API key illegally, it can be costly.

Usage Limitations and Fees
Visit OpenAI’s rates page to learn more about usage and costs.

Now you have your API key and are ready to use it.

Create and run scripts

Next, use Google Apps Script to create a script to link the Google Sheets and ChatGPT.

Here is how to save and use ChatGPT API keys in script properties.

Script properties allow you to manage scripts while maintaining security by not writing API keys directly in the code.

STEP
Open the Apps Script Editor

Open Google Sheets, then click on “Extensions” in the menu and select “Apps Script” to open the editor.

STEP
Store API keys in script properties

Once the editor is open, click on “Project Settings” from the left side menu.

Then select “Add Script Property”.

Enter the API key in the property field and save.

  • Property: Any name you like (e.g.,OPENAI_API_KEY)
  • Value: API key obtained (e.g.,sk-xxxxxx)
STEP
Create the Script
Contents of this Script

Use the prompts entered in column A of the spreadsheet to display ChatGPT responses in column B

function getChatGPTResponse() {
  // Retrieve the API key from the script properties
  var scriptProperties = PropertiesService.getScriptProperties();
  var apiKey = scriptProperties.getProperty('OPENAI_API_KEY');
  
  // Get the active sheet of the spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Get the last row
  
  // Process rows from the second row to the last row in column A
  for (var i = 2; i <= lastRow; i++) { // Start from the second row
    var prompt = sheet.getRange(i, 1).getValue(); // Get the prompt from column A
    
    // Send a request to the ChatGPT API if the prompt is not empty
    if (prompt) {
      var url = 'https://api.openai.com/v1/chat/completions';
      var options = {
        'method': 'post',
        'headers': {
          'Authorization': 'Bearer ' + apiKey,
          'Content-Type': 'application/json'
        },
        'payload': JSON.stringify({
          'model': 'gpt-3.5-turbo', // Specify the model to use
          'messages': [{"role": "system", "content": "You are a helpful assistant."},
                       {"role": "user", "content": prompt}],
          'max_tokens': 1000
        })
      };
      
      // Send a request to the ChatGPT API and retrieve the result
      var response = UrlFetchApp.fetch(url, options);
      var json = response.getContentText();
      var data = JSON.parse(json);
      
      // Display the ChatGPT response in column B (second column) of the same row
      sheet.getRange(i, 2).setValue(data.choices[0].message.content.trim());
    }
  }
}

Once the API key is saved in the properties, the next step is to create the script.

Copy and paste the code above into your editor.

Script Description
Get API key from script properties
function getChatGPTResponse() {
  // Retrieve the API key from the script properties
  var scriptProperties = PropertiesService.getScriptProperties();
  var apiKey = scriptProperties.getProperty('OPENAI_API_KEY');
  • The API key to access the ChatGPT API is stored in a Google Apps Script script property, which is then retrieved.
  • OPENAI_API_KEY is a pre-defined property name.
Get active sheet and last row of spreadsheet
  // Get the active sheet of the spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Get the last row
  • Get Active Spreadsheet: Get the active sheet of the currently open spreadsheet.
  • Get Last Row: Gets the last row in the sheet where data is entered. This determines how far along to process.
Loop processing / Get prompt of column A
  // Process rows from the second row to the last row in column A
  for (var i = 2; i <= lastRow; i++) { // Start from the second row
    var prompt = sheet.getRange(i, 1).getValue(); // Get the prompt from column A
  • Loop processing: a for loop is used to process the second to last row in sequence, starting with row 2 as i = 2.
  • Get the prompt for column A: sheet.getRange(i, 1) gets the value of column A in row i and treats that value as a prompt. This prompt is the text sent to ChatGPT.
Skip empty prompts / API request settings
    // Send a request to the ChatGPT API if the prompt is not empty
    if (prompt) {
      var url = 'https://api.openai.com/v1/chat/completions';
      var options = {
        'method': 'post',
        'headers': {
          'Authorization': 'Bearer ' + apiKey,
          'Content-Type': 'application/json'
        },
        'payload': JSON.stringify({
          'model': 'gpt-3.5-turbo', // Specify the model to use
          'messages': [{"role": "system", "content": "You are a helpful assistant."},
                       {"role": "user", "content": prompt}],
          'max_tokens': 1000
        })
      };
  • Skip empty prompt: if (prompt), continue processing only if column A contains a value. If there are blank cells, they are ignored.
  • API request settings: url: Specify the API endpoint for OpenAI. Here, https://api.openai.com/v1/chat/completions is used to send a chat-style request.
  • headers: Send the API key as an Authorization header.
  • payload: sets the data to be sent to the ChatGPT API. messages includes instructions from the system and user input (prompt).
  • max_tokens specifies the maximum number of tokens (length of reply) returned by the API.
Sending requests / processing responses
      // Send a request to the ChatGPT API and retrieve the result
      var response = UrlFetchApp.fetch(url, options);
      var json = response.getContentText();
      var data = JSON.parse(json);
  • Send request: send API request with UrlFetchApp.fetch(url, options) and get response from ChatGPT.
  • Response processing: The acquired response is converted to JSON format and stored in data.
Output answers to column B / Formatting of answers
      // Display the ChatGPT response in column B (second column) of the same row
      sheet.getRange(i, 2).setValue(data.choices[0].message.content.trim());
    }
  }
}
  • Output responses to column B: Use sheet.getRange(i, 2).setValue()to output the ChatGPT responses to column B of the same row.
  • Answer formatting: use trim() to remove excess whitespace and display clean text.
STEP
Save the Script

After writing the code, name it and save it.

STEP
Run the Script

Enter the prompt in column A of the spreadsheet and run the script.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

STEP
Output ChatGPT response

The ChatGPT responses appear in column B.

This completes the method of linking ChatGPT and spreadsheets using Google Apps Script.

Benefits of linking ChatGPT and Google Sheets

You can ask ChatGPT questions in bulk.
Process a large number of prompts at once and automate responses to reduce work time.

No more copying and pasting from ChatGPT to Google Sheets.
ChatGPT responses are displayed on a Google sheets, eliminating the need for tedious copy/paste work.

Notes on using ChatGPT API

The following are some of the caveats when using the API.

  • Data security: Care must be taken to ensure that data transmitted does not contain sensitive information, and measures must be taken to protect privacy.
  • Cost management: Since the ChatGPT API has a usage fee, it is necessary to check the API usage and costs.
    （OpenAI Rates Page）

Conclusion

By linking ChatGPT and spreadsheets via API, you can automate data processing to streamline your work.

You can eliminate the need to manually copy-paste questions from ChatGPT to a spreadsheet or enter questions individually, and get answers to multiple prompts on a spreadsheet at once.

By modifying GAS scripts, ChatGPT can also be used to automate the categorization and analysis of data in spreadsheets.

Please take advantage of this service when you want to improve your business productivity.

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here

Google Sheets
Let's share this post !

関連記事

Comments

To comment

TOC