Integrate OpenAI on Google Spreadsheet

Question

I'm creating a simple script to call the OpenAI API through Google App Script and send it several prompts at the same time to write the answers in column B: For that purpose, I create an array with the prompts from column A, I loop through them calling the API and writing values in column B.

This is the code:

function gpt3_response(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var ufila = sheet.getLastRow();
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange("A2:A" + ufila);
  var vector = range.getValues().flat();
  var API_KEY = "XXXXX";
  var data = {
    "model": "text-davinci-003",
    'max_tokens': 220,
    'temperature': 0,
  };
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data),
    'headers': {
      Authorization: 'Bearer ' + API_KEY,
    },
  };
  const recorreArray = vector => vector.forEach((prompt, index) => {
    data['prompt'] = prompt;
    response = UrlFetchApp.fetch(
      'https://api.openai.com/v1/completions',
      options,
    )
    var respuesta = JSON.parse(response.getContentText())['choices'][0]['text'];
    sheet.getRange(`B${index + 2}:B${index + 2}`).setValue(respuesta);
  });
  recorreArray(vector);
}

However, the response is:

package com.example.demo.controller;

import com.example.demo.model.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping(""/user"")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(""/all"")
    public List<User> getAllUsers(){
        return userService.getAllUsers();
    }

    @PostMapping(""/add"")
    public void addUser(@RequestBody User user){
        userService.addUser(user);
    }

    @PutMapping(""/update"")
    public void updateUser(@RequestBody User user){
        userService.updateUser(user);
    }

    @DeleteMapping

Someone knows whats happen? If I call the API with only one prompt, the response its ok, but when I try to make a loop calling the API many times, the response is what I show on top

I'm trying to make a call to OpenAI API through a loop with many prompts at the same time, and writing each response on the column B

Welcome to [so] Please add some sample data, clarify if the above iis wrote to a single cell, multiple cells or as an error message. — Rubén, Dec 12 '22 at 13:57

Ashley Guberman · Answer 1 · 2022-12-22T03:14:18.550

function OpenAI_Ask(API_KEY, Model, Prompt, Temperature, Tokens) {
  // Set the API endpoint for ChatGPT
  var apiEndpoint = "https://api.openai.com/v1/completions";

  // Set the options for the API call
  var options = {
  "headers": {
    "Content-Type": "application/json",
    "Authorization": "Bearer " + API_KEY
    },
  "payload": JSON.stringify(
    {
    "model": Model,
    "prompt": Prompt,
    "max_tokens": Tokens
    },
  ),
  "method": "POST",
  "temperature": Temperature
  };

  // Make the API call to ChatGPT
  var response = UrlFetchApp.fetch(apiEndpoint, options);

  // Parse the response from ChatGPT
  var responseObject = JSON.parse(response.getContentText());

  // Print the generated text from ChatGPT
  var Choice = responseObject.choices[0].text;
  return Choice;
}

function OpenAI_Test()
{
  var API_KEY = 'YOUR-API-KEY-HERE'; ;
  var PROMPT  = 'What is your favorite color?';
  var MODEL   = 'text-davinci-003';
  var TEMPERATURE = 0.5;
  var TOKENS   = 200;
  var Answer = OpenAI_Ask(API_KEY, MODEL, PROMPT, TEMPERATURE, TOKENS);
  Logger.log(Answer);
}

There is a sample Google Sheet you can copy with code and test harness here.

Please explain how this answer the question. P.S. It looks that the temperature property is misplaced. It should be included in the payload JSON. — Rubén, Jan 25 '23 at 23:04

Integrate OpenAI on Google Spreadsheet

1 Answers1