Search API Connector Documentation

Print

Import OpenAI (ChatGPT) Data to Google Sheets

In this guide, we’ll walk through how to pull data from the OpenAPI (ChatGPT) API directly into Google Sheets, using the API Connector add-on for Sheets. You can use this method to generate text content and images in response to prompts you enter in your spreadsheet.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your OpenAI API Key

  1. Create a free OpenAPI account at https://beta.openai.com/signup
  2. Once you're logged in, click the account menu in the top right, and then View API keys (or just navigate to https://beta.openai.com/account/api-keys directly)
    openai-viewkeys
  3. Click Create new secret key
    openai-createkeys
  4. Your API key will appear in a modal. Copy it and keep it safe, you'll need it in a moment!
    openai-getkeys

Part 2: Generate a Completion to a Text Prompt (ChatGPT)

OpenAI has many endpoints, each providing a different type of AI function. Here's how to send a prompt to OpenAI and automatically generate a text "completion" (a response) that matches the context you provided.

  1. Select OpenAI from the drop-down list of applications
    openai-application
  2. Under Authorization, enter your credentials from above, with the word Bearer in front.
  3. Choose the /completions endpoint
    openai-endpoints
  4. In the Body parameters section, select an OpenAI model. Generally you'll want the text-davinci-003 model.
  5. Enter your prompt, and optionally fill out the other parameters to customize your request. See here for information about these parameters.
  6. Select a destination sheet, name your request, and hit Run.
    openai-response-text

Part 3: Generate an Image with Dall-E

OpenAI's Dall-E model enables image creation. Here's how you'd send a prompt to OpenAI and automatically generate an image.

  1. Select OpenAI from the drop-down list of applications
    Under Authorization, enter your credentials from above, with the word Bearer in front.
  2. Choose the /images/generations endpoint.
    In the Body parameters section, enter your prompt. Optionally fill out the other parameters to customize your request.
  3. Select a destination sheet, name your request, and hit Run. A moment later you’ll see OpenAPI's response in your spreadsheet. The data.url field will contain a link to the generated image; use Google Sheets' =IMAGE function to see the image in your sheet, or just click the link to see the image in your browser.
    openai-response-image

Part 4: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the API documentation.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • ApplicationCustom
    • MethodPOST
    • Request URLhttps://api.openai.com/v1/completions
    • OAuth: None
    • Headers:
      • AuthorizationBearer your_api_key
      • Content-Type: application/json
    • Request body: {"model":"text-davinci-003","prompt":"Write a poem about Google Sheets","temperature":0.5,"max_tokens":60,"top_p":0.3,"frequency_penalty":0.5,"presence_penalty":0}
  3. Replace the prompt parameter above with a prompt of your choosing. The other parameters can be left as is, or edited.
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see OpenAPI's response in your spreadsheet.
    openai-response

Part 5: Run Requests Based on Sheet Data

You may configure your request such that it uses prompts located in cells of your spreadsheet.

#1 Reference a cell in your request (info):

  • Reference cells in your request body by wrapping the cell reference in 3 plus signs on either side, e.g. +++Sheet1!A1+++
    openai-reference
  • Save the request. It will now automatically pull in the value from cell A1 each time it runs.

#2 Reference a list of cells in your request (info):

Note that this is a paid feature. To run through a list of cells, running a request for each one, you'll need to create a custom request. Insert the word :::BREAK::: between each request body, e.g.

{"model":"text-davinci-003","prompt":"+++Sheet1!A1+++","temperature":0.5,"max_tokens":60,"top_p":0.3,"frequency_penalty":0.5,"presence_penalty":0}

:::BREAK:::

{"model":"text-davinci-003","prompt":"+++Sheet1!A2+++","temperature":0.5,"max_tokens":60,"top_p":0.3,"frequency_penalty":0.5,"presence_penalty":0}

:::BREAK:::

{"model":"text-davinci-003","prompt":"+++Sheet1!A3+++","temperature":0.5,"max_tokens":60,"top_p":0.3,"frequency_penalty":0.5,"presence_penalty":0}

This can be semi-automated by concatenating all the request bodies in your sheet rather than pasting them in one by one. View/copy this sheet for an example setup: OpenAI Demo Sheet

Part 6: API Documentation

Official API documentation: https://beta.openai.com/docs/introduction/overview

Note: When you first get your API key, you'll be on a free trial. Check https://beta.openai.com/account/billing/overview to see your usage and pricing information.

9 thoughts on “Import OpenAI (ChatGPT) Data to Google Sheets”

    • Sure, that function uses the same /completions endpoint as other text completion functions, so you set it up the same way, just adding the "suffix" parameter into the request body, like this: {"model":"text-davinci-003","prompt":"Will snow fall in summer?","temperature":0.5,"max_tokens":500,"top_p":0.3,"frequency_penalty":0.5,"presence_penalty":0,"suffix":"when pigs fly"}.
      OpenAI also recommends setting max_tokens > 256 since if you use smaller max_tokens, the model may be cut off before it's able to connect to the suffix.

      Reply
    • I don't think you can run the full fine tuning process through API Connector. I haven't tried it myself yet, but it looks like you need to first create your own model by uploading a data training set in JSONL format, which isn't supported by API Connector. OpenAI provides a tool that produces data in this format, so I would start with that. Once you've uploaded your training data, you can use it by running a request like this (you can use API Connector's curl importer to directly import this query: curl https://api.openai.com/v1/completions \
      -H "Authorization: Bearer $OPENAI_API_KEY" \
      -H "Content-Type: application/json" \
      -d '{"prompt": YOUR_PROMPT, "model": FINE_TUNED_MODEL}'

      Reply
  1. Thanks for the guide. Let's say you're using a cell to input the prompt. And you want to repeat the same task, but multiple cells, with each cell/prompt getting its own response on a separate row. Is that doable?

    Reply
    • You can do this by running a multi-query request (paid feature) that runs down the list of cells and runs a request for each one.
      In the future we'll also add this functionality to our IMPORTAPI custom function (currently it only allows variable request URLs, but this API requires varying the request body).

      Reply
  2. Thanks a lot for the description, as anon-coder I managed to get a request running and save it in my google sheets. Just a question, the length of the result seems to depend on the max-tokens setting.

    chatgpt has no limits in in current web based form (gpt-3 has limits). Just to be absolutely sure, the prompt described above (with ... completions does indeed use chatgpt) and the limit of creation (tokens) is due to the api use., as the non api use thought the openai webform does not consume any tokens.

    Is that right, or is there a way to use chatgpt without consuming tokens.

    Reply
    • This is mostly correct. Tokens correspond to small chunks of text (1 token equals about 4 characters or .75 English words), and the max-tokens parameter adusts how many tokens your request uses. ChatGPT seems to default to about 800 tokens per prompt, and, unlike the API, has no usage limits since OpenAI is currently offering it for free during a testing phase. As you note, using the API does consume tokens, and these tokens are limited.
      The one small difference I'd point out from what you wrote is that the method above uses OpenAI, not ChatGPT directly. ChatGPT also uses OpenAI, so they're essentially the same thing, but ChatGPT is OpenAI's interface that's been modified to allow ongoing chat-type interactions and prevent certain types of "inappropriate" output. I believe it's also using a slightly different language model ("GPT 3.5") than what we currently have access to via the API.

      Reply
      • Thanks a lot, very clear reply. I will test a bit more about the differences between the results of the web form based gpt 3.5 results and the api based gpt 3 results.

Leave a Comment

Jump To...