Skip to content

Publishing Spreadsheet Data as a Web API

Google Apps Script can serve JSON data over HTTP using ContentService. Combined with BareFrame.toJson(), you can turn any spreadsheet into a reusable public API with a few lines of code.

Overview

The workflow has four steps:

  1. Collect — Users enter data into a Google Sheet (directly or via a Form)
  2. Transform — Clean, filter, or aggregate the data using BareFrame
  3. Publish — Serve the result as JSON via ContentService (Web App)
  4. Access — Fetch the API from Python, JavaScript, or any HTTP client

Step 1: Collect Data in a Sheet

No setup is required. Data can come from direct edits, Google Forms, or any source that writes to a sheet.

Example sheet — Responses:

name score passed
Alice 85 TRUE
Bob 42 FALSE
Carol 91 TRUE

Step 2: Transform with BareFrame

Read the sheet, validate the data, then apply transformations before serving.

JavaScript
function getPublicData_() {
  const book  = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = book.getSheetByName("Responses");

  // Read
  const df = gaslamp.BareFrame.fromSheet(sheet);

  // Validate against a schema using FlameFrame
  // This catches type errors from bad sheet input before they reach API consumers.
  const schema = {
    name:   gaslamp.FlameGuards.isString,
    score:  gaslamp.FlameGuards.isNumber,
    passed: gaslamp.FlameGuards.isBoolean,
  };
  const { passed, failed } = gaslamp.FlameFrame.from(df, schema);

  if (failed.length > 0) {
    console.error(`Validation failed: ${failed.length} error(s).`);
    return gaslamp.BareFrame.fromColumns({ name: [], score: [] });
  }

  // Transform: keep only passing rows, expose name and score
  const result = passed
    .filter((row) => row.get("passed") === true)
    .select(["name", "score"]);

  return result;
}

See FlameFrame and Schema Validation for more on schema definition and error handling.

Step 3: Publish as a Web API

Google Apps Script Web Apps use doGet as the HTTP entry point. Return a ContentService response with MIME type JSON.

JavaScript
function doGet(e) {
  const df   = getPublicData_();
  const json = df.toJson();

  return ContentService
    .createTextOutput(json)
    .setMimeType(ContentService.MimeType.JSON);
}

Deploying the Web App

  1. Open the Apps Script editor (Extensions → Apps Script)
  2. Click Deploy → New deployment
  3. Select type: Web App
  4. Set Execute as: Me
  5. Set Who has access: Anyone (for a public API)
  6. Click Deploy and copy the Web App URL

The URL looks like:

Text Only
https://script.google.com/macros/s/<DEPLOYMENT_ID>/exec

Step 4: Access the API

Python

Python
import requests

url = "https://script.google.com/macros/s/<DEPLOYMENT_ID>/exec"
response = requests.get(url)
data = response.json()

# data is a list of dicts
for row in data:
    print(row["name"], row["score"])
# Alice 85
# Carol 91

curl

Bash
curl "https://script.google.com/macros/s/<DEPLOYMENT_ID>/exec"
# [{"name":"Alice","score":85},{"name":"Carol","score":91}]

JavaScript (fetch)

JavaScript
const url = "https://script.google.com/macros/s/<DEPLOYMENT_ID>/exec";
const res  = await fetch(url);
const data = await res.json();
console.log(data);
// [{ name: "Alice", score: 85 }, { name: "Carol", score: 91 }]

Complete Example

The full Apps Script file combining all steps:

JavaScript
// apps-script/Code.gs

function doGet(e) {
  const book  = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = book.getSheetByName("Responses");

  // Read
  const df = gaslamp.BareFrame.fromSheet(sheet);

  // Transform
  const result = df
    .filter((row) => row.get("passed") === true)
    .select(["name", "score"]);

  // Publish
  return ContentService
    .createTextOutput(result.toJson())
    .setMimeType(ContentService.MimeType.JSON);
}

Pretty-Printed JSON

Pass an indent value to toJson() for human-readable output. Useful during development.

JavaScript
const json = df.toJson(2);
// [
//   { "name": "Alice", "score": 85 },
//   { "name": "Carol", "score": 91 }
// ]

Notes

  • Re-deploy after code changes — GAS Web Apps cache the deployed version. Each code change requires a new deployment to take effect.
  • Access control — Anyone makes the URL fully public. Use Anyone with Google account to require sign-in.
  • Quota limits — GAS has daily execution quotas. For high-traffic APIs, consider caching with CacheService.
  • CORS — ContentService responses include Access-Control-Allow-Origin: * so browser fetch() calls work without additional headers.

Next Steps