Google Sheets API

Overview

The Google Sheets API reads cell values, formulas, and hyperlinks from Google Spreadsheets. Use this for retrieving specific data from sheets—not for file management (that's the Drive API).

Authentication

All requests require OAuth token from the google_sheets connection:

headers: {
  Authorization: "Bearer GOOGLE_SHEETS_TOKEN"
}

Reading Spreadsheet Data

Get Spreadsheet Metadata

Retrieve sheet names, properties, and structure:

const response = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}`,
  {
    headers: { Authorization: "Bearer GOOGLE_SHEETS_TOKEN" }
  }
);

const data = await response.json();
// data.sheets[] contains sheet metadata
// data.properties.title is the spreadsheet name

Read Cell Values (Simple)

For basic value retrieval without formatting:

const response = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`,
  {
    headers: { Authorization: "Bearer GOOGLE_SHEETS_TOKEN" }
  }
);

const data = await response.json();
// data.values is a 2D array of cell values

Range formats:

• Sheet1!A1:D10 — specific range
• Sheet1 — entire sheet
• A1:D10 — range on first sheet
• Sheet1!A:A — entire column

Read with Hyperlinks

To extract hyperlinks, use the spreadsheet endpoint with specific fields:

const fields = "sheets.data.rowData.values(formattedValue,hyperlink,userEnteredValue)";
const response = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}?` +
  `ranges=${encodeURIComponent(range)}&` +
  `fields=${encodeURIComponent(fields)}`,
  {
    headers: { Authorization: "Bearer GOOGLE_SHEETS_TOKEN" }
  }
);

Response structure with hyperlinks:

{
  "sheets": [{
    "data": [{
      "rowData": [{
        "values": [{
          "formattedValue": "Click Here",
          "hyperlink": "https://example.com/order",
          "userEnteredValue": {
            "formulaValue": "=HYPERLINK(\"https://example.com/order\", \"Click Here\")"
          }
        }]
      }]
    }]
  }]
}

Hyperlink locations:

• hyperlink — direct hyperlink on cell (most common)
• userEnteredValue.formulaValue — contains URL if using HYPERLINK() function
• Parse formula: =HYPERLINK("url", "display") → extract URL from first argument

Hyperlink Extraction Patterns

Direct Hyperlinks

Cells can have hyperlinks attached directly (right-click → Insert link). These appear in the hyperlink field:

const url = cell.hyperlink; // "https://example.com"
const displayText = cell.formattedValue; // "Click Here"

HYPERLINK() Function

When users use =HYPERLINK(url, label), extract from the formula:

const formula = cell.userEnteredValue?.formulaValue;
if (formula?.startsWith("=HYPERLINK(")) {
  // Parse: =HYPERLINK("https://url", "Display Text")
  const match = formula.match(/=HYPERLINK\("([^"]+)"(?:,\s*"([^"]*)")?\)/i);
  if (match) {
    const url = match[1];
    const label = match[2] || url;
  }
}

Combined Check

Always check both locations:

function extractHyperlink(cell) {
  // Check direct hyperlink first
  if (cell.hyperlink) {
    return {
      url: cell.hyperlink,
      text: cell.formattedValue || cell.hyperlink
    };
  }

  // Check HYPERLINK formula
  const formula = cell.userEnteredValue?.formulaValue || "";
  const match = formula.match(/=HYPERLINK\("([^"]+)"(?:,\s*"([^"]*)")?\)/i);
  if (match) {
    return {
      url: match[1],
      text: match[2] || cell.formattedValue || match[1]
    };
  }

  return null;
}

Common Patterns

Read All Sheets in Workbook

// First get sheet names
const meta = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}?fields=sheets.properties`,
  { headers: { Authorization: "Bearer GOOGLE_SHEETS_TOKEN" } }
);
const { sheets } = await meta.json();

// Then read each sheet
for (const sheet of sheets) {
  const sheetName = sheet.properties.title;
  const data = await fetch(
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${encodeURIComponent(sheetName)}`,
    { headers: { Authorization: "Bearer GOOGLE_SHEETS_TOKEN" } }
  );
}

Batch Read Multiple Ranges

const ranges = ["Sheet1!A1:D10", "Sheet2!A1:B5"];
const response = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?` +
  ranges.map(r => `ranges=${encodeURIComponent(r)}`).join("&"),
  {
    headers: { Authorization: "Bearer GOOGLE_SHEETS_TOKEN" }
  }
);

const data = await response.json();
// data.valueRanges[] contains each range's values

Error Handling

Status	Meaning	Action
400	Invalid range or request	Check range format
403	No access to spreadsheet	User needs to share or reconnect
404	Spreadsheet not found	Verify spreadsheet ID
429	Rate limited	Wait and retry

Best Practices

1. Request only needed fields — Use fields parameter to reduce response size
2. Batch when possible — Use batchGet for multiple ranges
3. Handle empty cells — Missing values in sparse sheets return undefined
4. Check both hyperlink sources — Direct hyperlinks AND HYPERLINK() formulas
5. Preserve source metadata — Include sheet name, cell reference, and URL for user context