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 Chat Completion to a Text Prompt (ChatGPT)

OpenAI has many endpoints, each providing a different type of AI function, and the easiest way to get started is through API Connector's built-in integration. Here's how to send a message to OpenAI and automatically generate a text "completion" (a response) that matches the context you provided.

  1. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select OpenAI from the drop-down list of applications
    openai-application
  3. Under Authorization, enter your credentials from above, with the word Bearer in front.
  4. Choose the /chat/completions endpoint to access OpenAI's newest language models.
    openai-endpoints
  5. In the Body parameters section, open the messages parameter and select a role and message. If you'd like to keep things simple with a single-turn task, simply select user and enter your message.
    openai-message
  6. (Optional) The /chat/completions endpoint allows you to create a multi-turn conversation, where you first set a system message followed by alternating user and assistant messages (you're the user, and the AI is the assistant). To add multiple steps, click the +Add button and enter each role and message you'd like to process. More info >
  7. In the model parameter, select an OpenAI model. Currently, the most powerful model is gpt-4, but that's not yet available to all. If you don't have access, select gpt-3.5-turbo.
  8. Optionally fill out the other parameters to customize your request. See here for information about these parameters.
  9. Select a destination sheet, name your request, and hit Run.
    openai-response-chatcompletion

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. In Sheets, open API Connector and create a new request (Extensions > API Connector > Open > Create request)
  2. Select OpenAI from the drop-down list of applications
    Under Authorization, enter your credentials from above, with the word Bearer in front.
  3. Choose the /images/generations endpoint.
    In the Body parameters section, enter your prompt. Optionally fill out the other parameters to customize your request.
  4. 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.

Example 1: ChatGPT API.

  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/chat/completions
    • OAuth: None
    • Headers:
      • AuthorizationBearer your_api_key
      • Content-Type: application/json
    • Request body: {"model":"gpt-3.5-turbo","messages": [{"role": "user", "content": "How is gpt-3.5-turbo different from text-davinci-003"}],"temperature":0.5,"max_tokens":1000,"top_p":0.3,"frequency_penalty":0,"presence_penalty":0,"n":1}
  3. Replace the content 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-chatgptresponse

Note: The /v1/chat/completions endpoint is compatible only with specific models, as shown here (and the gpt-4 models are available only with a paid subscription to ChatGPT+)
openai-modelcompatibility

Example 2: GPT-4 with Vision

This API lets you analyze images.

  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/chat/completions
    • OAuth: None
    • Headers:
      • AuthorizationBearer your_api_key
      • Content-Type: application/json
    • Request body: {"model":"gpt-4-vision-preview","messages":[{"role":"user","content":[{"type":"text","text":"What’s in this image?"},{"type":"image_url","image_url":{"url":"https://upload.wikimedia.org/wikipedia/commons/thumb/d/dd/Gfp-wisconsin-madison-the-nature-boardwalk.jpg/2560px-Gfp-wisconsin-madison-the-nature-boardwalk.jpg"}}]}],"max_tokens":300}
  3. Replace the url parameter above with the image URL you'd like to analyze, and the text parameter with the question you'd like to ask about the image.
  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.

Note: you'll need to buy credits (at least $5) to access the GPT-4 with Vision API (info).

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):

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":"gpt-3.5-turbo","messages": [{"role": "user", "content": "+++Sheet1!A1+++"}],"temperature":0.5,"max_tokens":1000,"top_p":0.3,"frequency_penalty":0,"presence_penalty":0,"n":1}

:::BREAK:::

{"model":"gpt-3.5-turbo","messages": [{"role": "user", "content": "+++Sheet1!A2+++"}],"temperature":0.5,"max_tokens":1000,"top_p":0.3,"frequency_penalty":0,"presence_penalty":0,"n":1}

:::BREAK:::

{"model":"gpt-3.5-turbo","messages": [{"role": "user", "content": "+++Sheet1!A3+++"}],"temperature":0.5,"max_tokens":1000,"top_p":0.3,"frequency_penalty":0,"presence_penalty":0,"n":1}

The above example request bodies are for the /chat/completions endpoint; you'll need to edit them if you're using the /completions endpoint as the two endpoints require different request body parameters.

Demo sheet

This process 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 example setups on either the /chat/completions or /completions endpoint: 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.

19 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.
      2023-03 update: OpenAI is now providing direct API access to ChatGPT through the gpt-3.5-turbo model.

      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.

  3. I am using importapi() to run this request, but it always show Error: Exceeded maximum execution time (line 0). How to solve this? Thanks.

    Reply
  4. Hi!
    can I import my ChatGPT History in it's entirety in 1 fell swoop? This history is all prior to installing API Connector.
    If not, is there any other way to import it's entirety in a formatted way?
    Thank you for any help

    Reply
    • Sorry, there's no way to import your chatGPT history via the API. However you can export it as described here, and then paste it into your sheet so you can reference parts of it in your subequent queries (if that's what you were looking for).

      Reply
    • Thanks, looks like OpenAI deprecated that model next month so it's no longer available. We'll remove it from the menu in our next update. Please use one of the replacement models listed instead, e.g. gpt-3.5-turbo-instruct, babbage-002, or davinci-002 (you can type in the model name and hit enter if it's not listed in the dropdown menu).

      Reply
  5. Hello, I use the extension and it displays many columns like this id object create model choices.index choices.message.role choices.message.content choices.logprobs choices.finish_reason usage.prompt_tokens usage.completion_tokens, how do I hide it?

    Reply

Leave a Reply to Aziz Cancel reply

Jump To