Hi,
I’m looking for any guide describing how to call Google Sheets from my GPT as an action and store there data.
2 Likes
Welcome to the forum.
Here’s an overview of the steps to call Google Sheets from your GPT model as a custom action and store data in it:
Set Up Google Sheets API:
- Create a Google Cloud project.
- Enable the Google Sheets API for your project.
- Generate credentials in the form of a JSON file.
Authenticate Your GPT Model:
- Load and use the credentials JSON file to authenticate your GPT model with the Google Sheets API.
Write Code to Interact with Google Sheets:
- Develop code using a client library (e.g., Google API Client Library for Python) to interact with Google Sheets.
- Implement functions to perform actions like creating, reading, updating, and deleting data in your Google Sheets.
Call Google Sheets from Your GPT Model:
- Within your GPT model or application, invoke the code you’ve written to interact with Google Sheets.
- Use these calls to store and retrieve data as needed.
Remember to consider security and error handling in your implementation and refer to the detailed documentation provided by Google for the Google Sheets API and your chosen client library for specific instructions.
2 Likes
Depending on how complex your needs, you might also try directly using the sheets.googleapis.com from the Action without writing any code and using the GPT as a natural language interface to the API
You might find it an interesting (and possibly frustrating) experience.
You may need to pare down the schema / fix it up for the GPT to validate it
EDIT: Caveat I have only tried google apis with api token so far, not with Oauth, so that might change things.
2 Likes
i keep running into authentication errors. I would my customgpt ask its external users / potential clients some questions and then for the information to be stored directly into a googlesheets document I own, using the googlesheetapi directly. Even when I make my document publically available I keep running into authentications errors. As I don’t want to the user to identify himself, but somehow code the authentication directly into the customgpt api (with proper restrictions on the google cloud side), I find few examples how to do that. I’ve tried creating a service account and and a apikey, but none of them seem to work.
1 Like
This GPT should do the job https://chat.openai.com/g/g-IC2jFLI8c-sheet
It interfaces through App script because GPT4 struggles interacting with the API directly. Also had to setup a whole OAuth screen on GCP for the access to the document
2 Likes
DISCLAIMER: I work at superface
Google sheets is a tricky API for LLMs. Our solution was to wrap it in a custom tools with a simpler interface for GPT to understand. You can test it at Connect GPTs to Google Sheets . It comes with solved OAuth for you and your GPT users.
3 Likes
When you say custom tools, you are referrring to "Superface
’ correct? If not what does that mean?
yea, but you can develop and host it on your own - create a dedicated endpoint that simplifies the google sheets api call…
I need direction in those steps. Usually when I ask I can never get a clear and direct answer. Where would I go to develop and host?
1 Like
Yeah! @PaulBellow, that is a great explanation, but like @Chowderr says, I feel like I’m missing a conceptual step.
When you say “develop and host” do you mean develop your website as an oAuth server so it can do this middle step?
Or is there something else that’s happening on Google Cloud that I’m missing?
1 Like
Sorry for the confusing. Some platforms you have to have a “server”, to do things for oAuth - I think I ran into it about two or three times so far. I found great companies though that take care of those aspects so it is not a concern anymore. i mainly try to understand as I want to build my own platform.
1 Like
No problem! I’ve had a ton of trouble learning all of this too. But I’ve discovered a bit.
Here:
I just watched this. It helped immensely and has demystified the whole deal: You have to have an application running the “API” between Google Sheets and a CustomGPT that defines the actions.
This is the part I didn’t understand: There are a bunch of places you could host this application, such as Google Cloud Services, Amazon Web Services, or Microsoft Azure; all of whom offer nice initial packages. There’s even more about how and where you’d want to host the application within those services, but it’s nice to have a place to begin.
You would basically need to develop your own server and then deploy it. That server would have an API that would be used by your agent and then it would call and relay the API calls to Google Sheets. The exact steps differs based what programming language (e.g node.js) and hosting platfor (e.g heroku) you use …
1 Like
Here I am going to explain it to you, in the best possible way I can, To have your GPT model interact with Google Sheets and store data, you’ll need to follow a few straightforward steps. First, set up a project on Google Cloud and enable the Google Sheets API. You can do this by visiting the Google Cloud Console, creating a new project, and then enabling the Google Sheets API under the “API & Services” section.
Next, create credentials for your project. This involves setting up an OAuth client ID, and downloading a JSON file which contains your credentials. This file is crucial as it will allow your program to connect with Google Sheets.
After setting up your Google Cloud project, you’ll need to prepare your programming environment. Install necessary Python libraries like google-auth and gspread. You can install these with a simple command using pip.
Using Python, you can write code to authenticate using the credentials from your JSON file and perform operations like reading from or writing to a spreadsheet. For example, to write “Hello, World!” into cell A1 of your sheet, or to read all data from it, you can use simple commands provided by the gspread library.
Finally, integrate this functionality into your application where GPT interacts with Google Sheets. This might involve setting up a server or adding scripts to handle data exchange based on your specific needs. Make sure to handle errors and ensure your setup is secure, especially if you are managing sensitive data.
1 Like
Hi
I dont know if you already found the solution or its still pending. There are number of solutions mentioned here, may be they are good for some users. For me i just want to use actiongpt and google sheet directly nothing in between. after long search and reading i found the below solution.
I am writing it here because may be other users like me need help and find this solution helpful. i am mentioning below the details:
Step 1: create your API (OAUTH) from https://console.cloud.google.com/ (you can ask chatgpt how to do it and it will give you the step by step solution)
Step 2: create a new gpt and go to “cerate action” here select "authentication as “OAUTH” and enter the “client id” and “client secret” for Authorization URL use Sign in - Google Accounts
for Token URL use https://oauth2.googleapis.com/token
for scope URL use https://www.googleapis.com/auth/spreadsheets
Step 3: now ask actiongpt or chatgpt to write the schema to use action gpt you can click on the button “Get help from ActionsGPT” it is on right bottom of schema screen. ActionsGPT uses openai 3.0 you can also ask chatgpt to write with the latest one that is openAI 3.1. it shows in the start of every schema
Step 4: once it is written press the back button and in the screen it will show the call back URL:
copy this call back url and go back to google API screen as shown in the image and paste the call back URL
now you are done
Important Note: everytime you open OAUTH and enter key in the chatgpt callback URL will be changed and you have to update it in google API screen
here is the schema i have asked Chatgpt to write (it is reading data, meta data, writing in cell, for mass update i have not tested it yet if the mass update doesnt work give this schema and ask chatgpt to update it for massupdate error:
{
“openapi”: “3.1.0”,
“info”: {
“title”: “Google Sheets API for GPT”,
“version”: “1.0.0”,
“description”: “This API integration allows the GPT to read from and write to Google Sheets using OAuth 2.0 authentication, retrieve spreadsheet metadata, and perform batch updates.”
},
“servers”: [
{
“url”: “https://sheets.googleapis.com/v4”
}
],
“paths”: {
“/spreadsheets/{spreadsheetId}/values/{range}”: {
“get”: {
“summary”: “Read Data from a Google Sheet”,
“operationId”: “readGoogleSheet”,
“parameters”: [
{
“name”: “spreadsheetId”,
“in”: “path”,
“required”: true,
“schema”: {
“type”: “string”
}
},
{
“name”: “range”,
“in”: “path”,
“required”: true,
“schema”: {
“type”: “string”
}
}
],
“responses”: {
“200”: {
“description”: “Successful operation”,
“content”: {
“application/json”: {
“schema”: {
“$ref”: “#/components/schemas/SheetResponse”
}
}
}
}
}
},
“put”: {
“summary”: “Update Data in a Google Sheet”,
“operationId”: “updateGoogleSheet”,
“parameters”: [
{
“name”: “spreadsheetId”,
“in”: “path”,
“required”: true,
“schema”: {
“type”: “string”
}
},
{
“name”: “range”,
“in”: “path”,
“required”: true,
“schema”: {
“type”: “string”
}
},
{
“name”: “valueInputOption”,
“in”: “query”,
“required”: true,
“schema”: {
“type”: “string”,
“enum”: [“RAW”, “USER_ENTERED”]
}
}
],
“requestBody”: {
“required”: true,
“content”: {
“application/json”: {
“schema”: {
“$ref”: “#/components/schemas/SheetValues”
}
}
}
},
“responses”: {
“200”: {
“description”: “Data updated successfully”,
“content”: {
“application/json”: {
“schema”: {
“$ref”: “#/components/schemas/SheetResponse”
}
}
}
}
}
}
},
“/spreadsheets/{spreadsheetId}”: {
“get”: {
“summary”: “Get Spreadsheet Details”,
“operationId”: “getSpreadsheet”,
“parameters”: [
{
“name”: “spreadsheetId”,
“in”: “path”,
“required”: true,
“schema”: {
“type”: “string”
}
}
],
“responses”: {
“200”: {
“description”: “Successful operation”,
“content”: {
“application/json”: {
“schema”: {
“$ref”: “#/components/schemas/SpreadsheetDetails”
}
}
}
}
}
}
},
“/spreadsheets/{spreadsheetId}:batchUpdate”: {
“post”: {
“summary”: “Batch Update in a Google Sheet”,
“operationId”: “batchUpdateGoogleSheet”,
“parameters”: [
{
“name”: “spreadsheetId”,
“in”: “path”,
“required”: true,
“schema”: {
“type”: “string”
}
}
],
“requestBody”: {
“required”: true,
“content”: {
“application/json”: {
“schema”: {
“$ref”: “#/components/schemas/BatchUpdateRequest”
}
}
}
},
“responses”: {
“200”: {
“description”: “Batch update successful”,
“content”: {
“application/json”: {
“schema”: {
“$ref”: “#/components/schemas/BatchUpdateResponse”
}
}
}
}
}
}
}
},
“components”: {
“schemas”: {
“SheetResponse”: {
“type”: “object”,
“properties”: {
“spreadsheetId”: {
“type”: “string”
},
“updatedRange”: {
“type”: “string”
},
“updatedRows”: {
“type”: “integer”
},
“updatedColumns”: {
“type”: “integer”
},
“updatedCells”: {
“type”: “integer”
}
}
},
“SheetValues”: {
“type”: “object”,
“properties”: {
“values”: {
“type”: “array”,
“items”: {
“type”: “array”,
“items”: {
“type”: “string”
}
}
},
“majorDimension”: {
“type”: “string”,
“enum”: [“ROWS”, “COLUMNS”]
}
}
},
“SpreadsheetDetails”: {
“type”: “object”,
“properties”: {
“spreadsheetId”: {
“type”: “string”
},
“properties”: {
“type”: “object”,
“properties”: {
“title”: {
“type”: “string”
}
}
},
“sheets”: {
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“properties”: {
“type”: “object”,
“properties”: {
“sheetId”: {
“type”: “integer”
},
“title”: {
“type”: “string”
},
“index”: {
“type”: “integer”
}
}
}
}
}
}
}
},
“BatchUpdateRequest”: {
“type”: “object”,
“properties”: {
“requests”: {
“type”: “array”,
“items”: {
“type”: “object”,
“description”: “A list of update requests to apply to the spreadsheet.”
}
},
“includeSpreadsheetInResponse”: {
“type”: “boolean”,
“description”: “True if the spreadsheet should be returned in the response.”
},
“responseRanges”: {
“type”: “array”,
“items”: {
“type”: “string”
},
“description”: “The ranges to include in the response.”
},
“responseIncludeGridData”: {
“type”: “boolean”,
“description”: “True if grid data should be included in the response.”
}
}
},
“BatchUpdateResponse”: {
“type”: “object”,
“properties”: {
“spreadsheetId”: {
“type”: “string”
},
“updatedRange”: {
“type”: “string”
},
“responses”: {
“type”: “array”,
“items”: {
“type”: “object”,
“description”: “The results of each update request.”
}
}
}
}
}
}
}
If you need further details i dont know how you will contact me but you can post your message here when i will see it will try to guide you
thanks
3 Likes
Google Sheets API can be challenging for language models like GPT to interact with directly. To simplify this, we created a custom tool that provides an easier interface for GPT models to connect with Google Sheets. You can give it a try at “Connect GPTs to Google Sheets 69.” This tool also handles OAuth authentication seamlessly for you and your GPT users, streamlining the process.
Hot dog! Big internet hugs to you friend, that’s working.
Thanks so much, that was driving me crazy.
How is this part handled moving forward?
I’m great with doing it manually for now, but I’d like to know what my next learning steps are.
Hi @ziftech,
I’m working on open-source framework for serving Python notebooks as web applications, that is called Mercury. Recently, I’ve added option to execute notebooks with REST API. Thanks to this, you can easily use Python code and connect it to ChatGPT. Mercury automatically generates OpenAPI schema for you.
I wrote Python notebook that is interacting with Google Sheets. The notebook can:
- list all values from Sheet
- update selected cell
I successfully integrate this notebook with Sheets. However, I needed to do one trick, in the JSON data in request response, I included cell index in A1 notation, so ChatGPT can easily navigate through cells.
Here is spreadsheet:
And this is the full Python notebook with example JSON response:
You can see that I added
Answer_cell for each row.
Example of ChatGPT listing values from sheet:
Example of ChatGPT update cell in sheet:
It was really amazing feeling interacting with sheets through the ChatGPT. The ChatGPT behaves like an assistant/agent, it performs several actions after single prompt. For example, I asked to provide answers and ChatGPT did several requests and in each it update single cell.
Links
I’ve been testing a couple of ways to use gpt for sheets and am quite happy with the results I’m getting with this extension. I generate, analyze, and format data with simple formulas, create dynamic tables, and summarize complex information.