Blog

Use Google Apps Script to Call SheetsAPI

Call SheetsAPI from inside Google Apps Script using UrlFetchApp, cache responses with CacheService, and trigger syncs on a time-based schedule.

Jun 27, 20263 min read

#google-sheets #apps-script #automation #rest-api

Google Apps Script runs inside Google Workspace and has direct access to Spreadsheet data - so why would you call SheetsAPI from it? Because SheetsAPI exposes your sheet as a clean REST endpoint that other services (Zapier, Retool, your own app) can call. Apps Script is a great place to push data to SheetsAPI, sync rows from other APIs into your sheet, or orchestrate multi-sheet workflows triggered on a schedule.

Prerequisites

A Google account with access to Google Apps Script (script.google.com)
A Google Sheet with a header row
A SheetsAPI account and a YOUR_USER_KEY key from the dashboard

1. Basic fetch with UrlFetchApp

Apps Script's UrlFetchApp is the equivalent of fetch() in the browser:

function listProducts() {
  const userKey = "YOUR_USER_KEY";
  const apiKey = "sk_your_api_key_here";
  const url = `https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${userKey}/Products?limit=20&sort=name`;
 
  const resp = UrlFetchApp.fetch(url, {
    method: "get",
    headers: { Authorization: `Bearer ${apiKey}` },
    muteHttpExceptions: true,
  });
 
  if (resp.getResponseCode() !== 200) {
    Logger.log("Error: " + resp.getContentText());
    return;
  }
 
  const { data, meta } = JSON.parse(resp.getContentText());
  Logger.log(`Fetched ${data.length} of ${meta.total} products`);
  data.forEach((p) => Logger.log(`${p.name} - $${p.price}`));
}

Run this from the Apps Script editor (Run → listProducts) and see the output in the Execution log.

2. Add a row via POST

Append a row to your sheet using the SheetsAPI POST endpoint:

function addProduct(name, price, category) {
  const userKey = "YOUR_USER_KEY";
  const apiKey = "sk_your_api_key_here";
  const url = `https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${userKey}/Products`;
 
  const payload = JSON.stringify({ name, price: String(price), category });
 
  const resp = UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    headers: { Authorization: `Bearer ${apiKey}` },
    payload,
    muteHttpExceptions: true,
  });
 
  if (resp.getResponseCode() >= 400) {
    throw new Error("SheetsAPI error: " + resp.getContentText());
  }
 
  Logger.log("Row added: " + resp.getContentText());
}
 
// Test it:
function testAddProduct() {
  addProduct("Widget Pro", 49.99, "Widgets");
}

3. Cache responses with CacheService

Apps Script's CacheService avoids redundant API calls when the same function runs many times in a short window:

function getCachedProducts() {
  const cache = CacheService.getScriptCache();
  const cacheKey = "products_all";
 
  const cached = cache.get(cacheKey);
  if (cached) {
    Logger.log("Cache hit");
    return JSON.parse(cached);
  }
 
  const userKey = "YOUR_USER_KEY";
  const apiKey = "sk_your_api_key_here";
  const resp = UrlFetchApp.fetch(
    `https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${userKey}/Products?limit=500`,
    { headers: { Authorization: `Bearer ${apiKey}` } },
  );
 
  const data = JSON.parse(resp.getContentText());
  cache.put(cacheKey, JSON.stringify(data), 300); // cache for 5 minutes
  return data;
}

CacheService supports up to 100 KB per entry and a 6-hour maximum TTL.

4. Fetch all pages

SheetsAPI paginates with limit and offset. Loop until all rows are fetched:

function fetchAllProducts() {
  const userKey = "YOUR_USER_KEY";
  const apiKey = "sk_your_api_key_here";
  const base = `https://sheetsapi.gkit.mreshank.com/api/spreadsheets/${userKey}/Products`;
  const pageSize = 100;
  const all = [];
  let offset = 0;
 
  do {
    const resp = UrlFetchApp.fetch(`${base}?limit=${pageSize}&offset=${offset}`, {
      headers: { Authorization: `Bearer ${apiKey}` },
    });
    const { data, meta } = JSON.parse(resp.getContentText());
    all.push(...data);
    offset += pageSize;
    if (all.length >= meta.total) break;
  } while (true);
 
  Logger.log(`Fetched all ${all.length} products`);
  return all;
}

5. Sync SheetsAPI data into a different sheet tab

Pull data from SheetsAPI and write it into a separate tab in the same spreadsheet:

function syncProductsToTab() {
  const products = fetchAllProducts();
  if (!products.length) return;
 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName("Synced Products");
  if (!sheet) sheet = ss.insertSheet("Synced Products");
 
  // Write header row
  const headers = Object.keys(products[0]);
  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
 
  // Write data rows
  const rows = products.map((p) => headers.map((h) => p[h] ?? ""));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
 
  Logger.log(`Synced ${products.length} rows to "Synced Products" tab`);
}

6. Time-based trigger (scheduled sync)

Set up a trigger to run the sync automatically:

function createDailyTrigger() {
  // Delete existing triggers for this function first
  ScriptApp.getProjectTriggers()
    .filter((t) => t.getHandlerFunction() === "syncProductsToTab")
    .forEach((t) => ScriptApp.deleteTrigger(t));
 
  ScriptApp.newTrigger("syncProductsToTab").timeBased().everyHours(6).create();
 
  Logger.log("Trigger created: sync every 6 hours");
}

Run createDailyTrigger() once. After that, syncProductsToTab will run every 6 hours without any intervention.

7. Error handling and retry

Wrap calls in a try/catch and implement a simple retry for transient errors:

function fetchWithRetry(url, options, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      const resp = UrlFetchApp.fetch(url, {
        ...options,
        muteHttpExceptions: true,
      });
      const code = resp.getResponseCode();
 
      if (code === 429) {
        // Rate limited - back off
        const retryAfter = parseInt(resp.getHeaders()["Retry-After"] || "10", 10);
        Utilities.sleep(retryAfter * 1000);
        continue;
      }
 
      if (code >= 500 && attempt < maxRetries) {
        Utilities.sleep(1000 * attempt); // exponential back-off
        continue;
      }
 
      return resp;
    } catch (err) {
      if (attempt === maxRetries) throw err;
      Utilities.sleep(1000 * attempt);
    }
  }
}

Query parameter reference

Parameter	Example	Description
`limit`	`100`	Rows per page (max 500)
`offset`	`0`	Pagination offset
`search`	`category:widgets`	Filter `field:value`
`sort`	`name` or `-price`	Ascending / descending
`fields`	`name,price`	Return only named columns

Summary

Task	Apps Script approach
Fetch rows	`UrlFetchApp.fetch` + `JSON.parse`
Add rows	`UrlFetchApp.fetch` with `method: "post"`
Cache results	`CacheService.getScriptCache().put(key, val, ttl)`
Paginate all rows	`do/while` loop with `offset`
Write to sheet	`SpreadsheetApp.getActiveSpreadsheet().getRange().setValues()`
Scheduled sync	`ScriptApp.newTrigger().timeBased().everyHours(n).create()`

Apps Script is free, lives inside Google Workspace, and needs no server - making it one of the lowest-friction ways to automate syncs between SheetsAPI and the rest of your Google Workspace tools.

All posts