Query a SQL database via an API layer

Northwind’s customer data does not live in Google Sheets — it lives in a Postgres database the product team owns. Apps Script cannot connect to that database directly: it has no driver, and opening the database to the public internet so it could would be a bad idea. So the numbers people actually want in a spreadsheet stay locked in the database, and someone copies them across by hand every week.

The fix is a thin HTTP layer in front of the database — a small endpoint that accepts a query, runs it, and returns rows as JSON. Apps Script never touches the database; it just calls the gateway. This script wraps that gateway in one reusable runQuery helper, then uses it to refresh a sheet of recently active users. Once the helper exists, every other report is a one-line query away.

What you’ll need

• A SQL gateway: a small HTTP service in front of your database that accepts a query and returns { "rows": [...] }. This can be a serverless function, a tiny app, or an existing internal API — it just has to speak JSON over HTTPS.
• A bearer token for that gateway, saved as SQL_KEY in Script Properties — see Store API keys and secrets securely.
• A Google Sheet to write results into, and its ID.

The script

// The HTTP endpoint that fronts your database. It accepts a POST with a
// { sql } body and returns { rows: [...] }.
const SQL_GATEWAY = 'https://sql-gateway.example/query';

// The spreadsheet that receives the "active users" report.
const ACTIVE_USERS_SHEET_ID = '1abcActiveUsersId';

/**
 * Sends a SQL string to the gateway and returns the rows it answers with.
 * Apps Script never speaks to the database directly — only to this gateway.
 *
 * @param {string} sql  The query to run.
 * @return {Object[]} An array of row objects, one per result row.
 */
function runQuery(sql) {
  const key = PropertiesService.getScriptProperties().getProperty('SQL_KEY');

  // POST the query as JSON, authenticated with the gateway's bearer token.
  const res = UrlFetchApp.fetch(SQL_GATEWAY, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + key },
    payload: JSON.stringify({ sql }),
    muteHttpExceptions: true,
  });

  // A non-2xx status means the gateway rejected the query — surface it
  // rather than letting a vague JSON error appear later.
  const code = res.getResponseCode();
  if (code < 200 || code >= 300) {
    throw new Error('Gateway returned ' + code + ': ' + res.getContentText());
  }

  // The gateway wraps results in { rows: [...] }.
  return JSON.parse(res.getContentText()).rows || [];
}

/**
 * Pulls users seen in the last seven days and rewrites the report sheet
 * with the fresh result.
 */
function pullActiveUsers() {
  // 1. Ask the gateway for the rows we want.
  const rows = runQuery(
    "SELECT id, email, last_seen FROM users " +
    "WHERE last_seen > NOW() - INTERVAL '7 days'"
  );

  // 2. Open the report sheet and clear last week's data.
  const sheet = SpreadsheetApp.openById(ACTIVE_USERS_SHEET_ID).getSheets()[0];
  sheet.clear();

  // 3. If the query returned nothing, leave the sheet empty and stop.
  if (!rows.length) {
    Logger.log('No active users returned — sheet left empty.');
    return;
  }

  // 4. Derive the header row from the first result object's keys.
  const headers = Object.keys(rows[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // 5. Turn each row object into an array in header order, then write
  //    the whole block in one call.
  const values = rows.map((r) => headers.map((h) => r[h]));
  sheet.getRange(2, 1, values.length, headers.length).setValues(values);

  Logger.log('Wrote ' + values.length + ' rows to the report sheet.');
}

How it works

1. runQuery is the reusable core. It reads the gateway token from Script Properties, then POSTs the SQL string as a JSON body to SQL_GATEWAY.
2. It checks the HTTP status before parsing. A bad query or an auth failure shows up as a 4xx/5xx response, and throwing here gives you the gateway’s actual error message instead of a confusing parse failure later.
3. On success it parses the response and returns the rows array, falling back to an empty array if the gateway omits it.
4. pullActiveUsers calls runQuery with a query for users active in the last week, then opens the report sheet and clears it.
5. If there are no rows it logs and stops, leaving the sheet empty rather than crashing on an undefined header.
6. Otherwise it builds the header row from the keys of the first row object, maps every row into header order, and writes the whole block in a single setValues call — far faster than writing cell by cell.

Example run

The gateway answers the query with JSON like this:

{
  "rows": [
    { "id": 4012, "email": "[email protected]",  "last_seen": "2026-05-24" },
    { "id": 4108, "email": "[email protected]", "last_seen": "2026-05-23" }
  ]
}

After pullActiveUsers runs, the report sheet holds:

id	email	last_seen
4012	[email protected]	2026-05-24
4108	[email protected]	2026-05-23

The header row is derived automatically, so adding a column to the SELECT needs no change to the sheet-writing code.

Trigger it

A “recently active” report is most useful when it is always fresh, so run it on a schedule rather than by hand:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose pullActiveUsers, event source Time-driven, and a Day timer set to run early each morning.

You can reuse runQuery from any other function — write a pullRevenue or pullSignups the same way, each with its own scheduled trigger.

Watch out for

• The gateway is a security boundary. Anything that holds a valid SQL_KEY can run arbitrary SQL, so the gateway should restrict which statements it accepts — read-only SELECTs, ideally against specific tables — and never run attacker-supplied text. Treat the token like a database password.
• Never build queries from untrusted input by string concatenation. The SQL here is a fixed literal; if a query must include user input, the gateway must use parameterised statements, not Apps Script string joins.
• UrlFetchApp has a 60-second timeout. A slow query that runs fine in a database client can still time out here — add an index, or have the gateway page large results rather than returning everything at once.
• setValues needs a rectangular array. If different rows carry different keys (some gateways omit null columns), r[h] returns undefined for the missing ones — fine for the sheet, but worth knowing if a column looks empty.
• Apps Script daily UrlFetchApp quota is finite. One scheduled report is trivial, but a function that loops and queries per row will burn through it.