Google Sheets

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:

Sheets used in GPT integrations

We can access worksheet by providing a title:

sh ="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":
        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:

Full code to query database in the notebook

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.

Private Site in Mercury Cloud

Upload file with notebook

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

Uploaded files to make ChatGPT integration with Google Sheets in Mercury Cloud

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.

Site in Mercury with OpenAPI link in the footer

Please copy the full OpenAPI schema.

OpenAPI schema for provided notebooks

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

Configure ChatGPT with OpenAPI schema from Mercury

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:

Mercury API Key

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

Mercury API Key in ChatGPT authentication

Chat with your Sheets

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

ChatGPT confirm request to access sheets

We will get the response:

Mercury API Key in ChatGPT authentication

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

ChatGPT provides answers

ChatGPT iterate over all answers and updated the sheet:

Mercury API Key in ChatGPT authentication

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

Mercury API Key in ChatGPT authentication


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!

ChatGPT integration with Google Sheets and Mercury is amazing