Use Google Sheets in custom ChatGPT

Google Sheets can be integrated with custom ChatGPT. You can provide Sheets read and write functionality for ChatGPT. We will use Python notebook to create custom code that will list all values from sheet and allow to update selected cell. The notebook will be served as REST API endpoint, which will be available as GPT Action. The OpenAPI schema for notebook and serving is handled by Mercury framework (opens in a new tab). The notebooks is deployed online with Mercury Cloud.

Python notebook

Let's create a Python notebook for accessing Google Sheets. We need to install gspread (opens in a new tab) package to make Sheets integration simple and mercury for running notebook as web application:

# install package gspread
pip install python-dotenv
 
# install widgets framework
pip install mercury 

Let's import required modules in the first cell:

import gspread
import mercury as mr

We need to provide Sheets credentials to authorize access:

gc = gspread.service_account("sheets-credentials.json")

I kept JSON with credentials in sheets-credentials.json file. Please read article on how to connect Python with Google Sheets service (opens in a new tab).

Here is a sheet that will be used in this example:

We can access worksheet by providing a title:

sh = gc.open("chat-gpt-builder")

We will allow to actions on the sheet:

• list action to display all values,
• update to update one cell value.

Let's add widget to control the action:

action = mr.Select(label="Select action", value="list", choices=["list", "update"], url_key="action")

We need two more widgets to select updated cell in A1 notation and provide new value:

update_cell = mr.Text(label="Update cell in A1 notation", value="", url_key="cell")

cell_value = mr.Text(label="Cell value", value="", url_key="new_value")

In the case of list action, we will return APIResponse with all values from sheet listed:

if action.value == "list":
    list_of_dicts = sh.sheet1.get_all_records()
    data = [{**l, "Answer_cell": f"B{i+2}"} for i, l in enumerate(list_of_dicts)]
    response = mr.APIResponse({"sheet_data": data})

Please note, that we added Answer_cell with its index, this will help ChatGPT navigate through the sheet.

This it the update response:

if action.value == "update":
    try:
        sh_response = sh.sheet1.update_acell(update_cell.value, cell_value.value)
        response = mr.APIResponse({"message": "Cell updated"})
    except Exception as e:
        response = mr.APIResponse({"error": str(e)})

The full code in the notebook, please notice that sheet data is returned as dictionary:

Deploy notebook online

The notebook code is ready. We need to put it online, the easiet way is to use Mercury Cloud. We get notebook running under custom domain address in few clicks.

Login to Mercury Cloud

If you don't have account, you can create it here: Mercury Cloud (opens in a new tab).

Create new site

Please create a new site. Remember, to set site as PRIVATE otherwise, anyone with link to your site will be able to query your database.

Upload file with notebook

Please upload your notebook file, credentials JSON and requirements.txt file with all required packages listed.

Configure ChatGPT builder

Next step is to configure new action in ChatGPT builder. Mercury will generate OpenAPI schema for you based on provided notebook. Please find OpenAPI link in the site footer.

Please copy the full OpenAPI schema.

Please add a new action in GPT builder and provide copied schema:

You will see that available actions will popup. The last configuration step is to provide API Key to authorize ChatGPT to execute notebooks.

You can find Mercury API Key in accounts view:

Please copy key and in ChatGPT configuration provide in Authentication dialog:

Chat with your Sheets

Let's ask ChatGPT to list all questions from sheet. You will be asked to confirm the request:

We will get the response:

OK, questions are listed, let ChatGPT to provide answers and update the sheet:

ChatGPT iterate over all answers and updated the sheet:

Let's check the sheet, all answers are there!

Summary

Integration of ChatGPT with Google Sheets is very powerful. You can use Python and Mercury to customize it as you want. The sheet data is provided to the ChatGPT as JSON with cell index in A1 notation to each row. Based on this, ChatGPT can update selected cell. The Pyhton notebook is served online with Mercury Cloud. This integration is truly amazing!