How to connect ChatGPT and Google Sheets with a Custom GPT

Downloads

Security warning: This tutorial creates a public, unauthenticated Apps Script endpoint that runs as you. Anyone with its deployment URL can call values exposed by the script. Use these files only with non-sensitive demo or genuinely public data; use an API key or OAuth for private data.

• Download the Google Apps Script (.gs)
• Download the Custom GPT OpenAPI schema (.json)

These files match the examples below. You still need to add your spreadsheet ID to the script and your Apps Script deployment ID to the schema.

Older video walkthrough (2024)

Outdated video: This 2024 video shows an earlier, less restricted workflow. Do not copy its arbitrary cell-address setup or old deployment URL configuration. Follow the allowlisted code, deployment-ID-only schema and security guidance in the written guide below.

Video not loading? Watch on YouTube

What this connection does

This guide creates a deliberately narrow, read-only lookup. A Custom GPT sends a logical key such as stock_price to a Google Apps Script web app. The script maps that key to one approved cell and returns the cell's formatted display value as JSON.

The GPT never receives an arbitrary sheet name or cell address. Those details remain in a server-side allowlist, which reduces the chance of exposing another part of the spreadsheet accidentally.

Good uses for this pattern include:

• retrieving one non-sensitive demo KPI;
• returning the latest value displayed by a spreadsheet formula;
• exposing a public status or reference value; and
• learning how GPT Actions, OpenAPI and Apps Script fit together.

It does not expose an entire spreadsheet, build a dashboard or provide a trend dataset. Those require a larger, properly secured API design.

Before you start

You need:

• a Google Sheet that you can edit;
• a paid ChatGPT account, or permission to build GPTs in a managed workspace;
• access to ChatGPT on the web, because GPTs can be built and edited only in the web experience; and
• a non-sensitive value that is safe to make available through a public URL.

See OpenAI's guide to creating and editing GPTs for current account and workspace requirements.

Important security limitation

This simple Apps Script setup uses no authentication. To let ChatGPT call it, the web app is deployed to execute as you and be accessible to anyone. Anyone who obtains the deployment URL can call it with your authority and retrieve any value that your code exposes.

Use this version only for a non-sensitive demo or genuinely public data. Do not expose customer information, private financial data, credentials, personal data or confidential business metrics. Keep the allowlist as small as possible.

For private or user-specific data, use a separately hosted API with API-key or OAuth authentication and appropriate access controls. OpenAI documents the available GPT Action authentication options.

If you share the GPT by link or publish it to the GPT Store, OpenAI requires a valid privacy policy URL for a GPT that uses Actions. See Configuring actions in GPTs.

Step 1 - Prepare your Google Sheet

For this example:

1. Name a sheet tab Example.
2. Put the value you want to return in cell B13.
3. The logical API key for that value will be stock_price.

For a stock-price demonstration, you could put this formula in B13:

=GOOGLEFINANCE("NASDAQ:GOOG","price")

Google notes that quotes from GOOGLEFINANCE may be delayed by up to 20 minutes, so describe this as the latest available spreadsheet value rather than a live market price. See the GOOGLEFINANCE documentation.

Find your spreadsheet ID

Open the sheet and look at its URL:

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit

Copy the value between /d/ and /edit. You will paste it into SPREADSHEET_ID in the script.

Step 2 - Create the Google Apps Script web app

In Google Sheets, select Extensions > Apps Script. Replace the editor contents with this code:

// PUBLIC, UNAUTHENTICATED DEMO ONLY.
// Deploying this web app with access set to "Anyone" lets anyone who obtains
// the deployment URL call it with your authority. Expose only non-sensitive,
// genuinely public values. Use an authenticated API for private data.

const SPREADSHEET_ID = "PASTE_YOUR_SPREADSHEET_ID_HERE";

// Only these logical keys can be requested. Keep sheet names and ranges here,
// rather than accepting arbitrary sheet names or A1 ranges from the internet.
const ALLOWED_VALUES = Object.freeze({
  stock_price: Object.freeze({
    sheetName: "Example",
    range: "B13",
  }),
});

function jsonResponse(payload) {
  return ContentService.createTextOutput(JSON.stringify(payload)).setMimeType(
    ContentService.MimeType.JSON,
  );
}

function doGet(e) {
  const key = String(e?.parameter?.key ?? "").trim();

  if (!Object.prototype.hasOwnProperty.call(ALLOWED_VALUES, key)) {
    return jsonResponse({
      status: "error",
      message: "Unknown key. Use one of the keys defined by this API.",
    });
  }

  try {
    const target = ALLOWED_VALUES[key];
    const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
    const sheet = spreadsheet.getSheetByName(target.sheetName);

    if (!sheet) {
      return jsonResponse({
        status: "error",
        message: "The configured sheet was not found.",
      });
    }

    return jsonResponse({
      status: "success",
      key,
      value: sheet.getRange(target.range).getDisplayValue(),
    });
  } catch (error) {
    console.error(error);
    return jsonResponse({
      status: "error",
      message: "The spreadsheet value could not be retrieved.",
    });
  }
}

Replace PASTE_YOUR_SPREADSHEET_ID_HERE with the ID from Step 1.

This version uses SpreadsheetApp.openById() because active-spreadsheet methods are not available when a bound script runs as a web app. It also uses getDisplayValue(), so the API always returns a formatted string that matches the value displayed in the sheet.

Deploy and test the web app

1. Select Deploy > New deployment.
2. Choose Web app as the deployment type.
3. Set Execute as to Me.
4. Set Who has access to Anyone.
5. Select Deploy, complete Google's authorisation flow and copy the web app URL.

Google's Apps Script web-app guide explains the deployment settings and how query parameters reach doGet(e).

Test the endpoint in a private browser window, replacing the placeholder with your deployment ID:

https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec?key=stock_price

You should receive JSON similar to:

{
  "status": "success",
  "key": "stock_price",
  "value": "123.45"
}

The deployment URL ends in /exec. For the OpenAPI schema, copy only the deployment ID between /s/ and /exec.

If your Google Workspace does not allow an anonymous web-app deployment, stop here. Do not weaken workspace security or publish private data to make this example work; use an authenticated API instead.

Step 3 - Create your Custom GPT

Open the GPT editor in a desktop web browser and create a GPT. Give it a clear name, description and a few conversation starters based on the single value you are exposing.

In its instructions, describe exactly when to use the action. For example:

When the user asks for the latest stock value stored in the demo sheet,
call getAllowedSheetValue with key stock_price. Treat the returned value as
the latest value displayed by the sheet and mention that market data may be delayed.

OpenAI recommends referring to the action name and its parameters explicitly in your GPT's instructions. See Getting started with GPT Actions.

Add the Action

1. In the GPT editor, open Actions and select Create new action.
2. Set authentication to None. This is appropriate only for the non-sensitive public demo described above.
3. Paste the schema below.
4. Replace YOUR_DEPLOYMENT_ID with only the Apps Script deployment ID, not the full URL.
5. Save the schema.

{
  "openapi": "3.1.0",
  "info": {
    "title": "Allowlisted Google Sheets value",
    "version": "1.0.0",
    "description": "Public, unauthenticated demo only. Returns one non-sensitive display value from a fixed, server-side allowlist in Google Sheets. Use an authenticated API for private data."
  },
  "servers": [
    {
      "url": "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID"
    }
  ],
  "paths": {
    "/exec": {
      "get": {
        "operationId": "getAllowedSheetValue",
        "summary": "Get an allowlisted value from Google Sheets",
        "description": "Returns the formatted, non-sensitive display value for one approved logical key. This public demo has no authentication.",
        "x-openai-isConsequential": false,
        "security": [],
        "parameters": [
          {
            "name": "key",
            "in": "query",
            "required": true,
            "description": "The approved logical value to retrieve.",
            "schema": {
              "type": "string",
              "enum": [
                "stock_price"
              ]
            }
          }
        ],
        "responses": {
          "200": {
            "description": "The requested display value, or a safe error response.",
            "content": {
              "application/json": {
                "schema": {
                  "type": "object",
                  "additionalProperties": false,
                  "properties": {
                    "status": {
                      "type": "string",
                      "enum": [
                        "success",
                        "error"
                      ]
                    },
                    "key": {
                      "type": "string"
                    },
                    "value": {
                      "type": "string",
                      "description": "The formatted value shown in the configured Google Sheets cell."
                    },
                    "message": {
                      "type": "string",
                      "description": "A safe error message when status is error."
                    }
                  },
                  "required": [
                    "status"
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
}

The schema's server URL deliberately stops before /exec; the path supplies /exec exactly once.

Test the Action

The detected action should be named getAllowedSheetValue.

1. Select Test beside the action.
2. Use stock_price as the key.
3. Approve the request if ChatGPT asks for permission.
4. Confirm that the test output contains status, key and a string value.
5. In Preview, ask one of your representative questions and check that the GPT calls the action only when appropriate.

Adding another approved value

Add a new logical key to ALLOWED_VALUES in Apps Script:

const ALLOWED_VALUES = Object.freeze({
  stock_price: Object.freeze({ sheetName: "Example", range: "B13" }),
  public_status: Object.freeze({ sheetName: "Public", range: "B2" }),
});

Then add the same key to the OpenAPI parameter's enum:

"enum": ["stock_price", "public_status"]

To keep the deployment ID already used by the GPT, open Deploy > Manage deployments, edit the existing web app, choose New version, and deploy it. Then update the GPT Action schema and test both keys. Never change the API to accept arbitrary sheet names or cell addresses from the caller.

Example Custom GPT

You can see a Custom GPT built for Touchstone Exploration. It is an example of a specialised GPT, but its data sources and security design may differ from the deliberately small public lookup in this tutorial.

What you have built

You now have a Custom GPT Action that can request one approved, non-sensitive Google Sheets display value through a read-only logical key. The script, schema and GPT instructions use the same getAllowedSheetValue contract, and the deployment URL contains /exec only once.

Keep the endpoint narrow, test each allowed key and move to an authenticated API before using private or business-sensitive data.