Google Sheets

If your conversations or feedback are kept in a Google Sheet (form responses, pasted survey answers, a log your team fills in), you can send them to Dimension Labs from there. A scheduled job on your side reads the sheet, picks out the rows that are new since its last run and posts each one as a message to the universal API.

What you need

• A Google Cloud project where you can enable an API and create a service account.
• Edit access to the spreadsheet, so you can share it with the service account.
• A Dimension Labs API key for a new Universal API source. See Your API key.

1. Get access

1. In the Google Cloud console, enable the Google Sheets API for your project (Node.js quickstart).
2. Create a service account: go to Menu > IAM & Admin > Service Accounts, select your project, click Create service account, enter a name, and click Done (Create access credentials). It needs no Google Cloud roles for this.
3. Give the job the service account’s identity. On Google Cloud (Cloud Run, for example), run the job as that service account. Elsewhere, create a JSON key: click the service account’s email, open the Keys tab, choose Add key > Create new key, pick JSON and click Create. Google only lets you download it once. Point GOOGLE_APPLICATION_CREDENTIALS at the file (googleapis: service account credentials).
4. Share the spreadsheet with the service account’s email address, as a Viewer. A service account can read the documents shared with it, and nothing else (Create access credentials).
5. Copy the spreadsheet id: the long string between /d/ and /edit in the sheet’s URL.

Keep the key file in a secrets manager, never in code you commit.

2. Read new rows

Use Google’s Node.js client, googleapis, with the read-only scope https://www.googleapis.com/auth/spreadsheets.readonly. Each run makes two requests:

GET https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID?fields=properties.timeZone
GET https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID/values/'Sheet1'?valueRenderOption=UNFORMATTED_VALUE&dateTimeRenderOption=SERIAL_NUMBER

• The first reads the spreadsheet’s time zone, in a form such as America/New_York (Spreadsheet resource). A field mask keeps grid data out of the reply (spreadsheets.get).
• The second reads every cell on the tab. A range that is only a sheet name, such as 'Sheet1', means all the cells on that sheet (Sheets API concepts). Empty trailing rows and columns are left out (ValueRange).
• With UNFORMATTED_VALUE, dates come back as serial numbers: days since December 30th 1899, with the time as a fraction of a day (values.get, DateTimeRenderOption). They are in the spreadsheet’s time zone, so the code converts them to UTC.

Sheets has no “changed since” filter, so the job reads the whole tab and keeps the rows whose sent_at is at or after the cursor. Rows need to arrive in time order: a row pasted in later with an older date is skipped. To pick those up, run the history load again.

3. Map the columns

This guide assumes the first row holds these headers, and each row below it is one message. Rename them to match yours.

Column	Holds
message_id	The message’s or response’s own id
conversation_id	The conversation, ticket or response the message belongs to
customer_id	Your internal id for the customer, on every row. Not an email address or phone number.
sender_type	Who wrote the message, for example customer, agent or bot
body	The message text
channel	Chat, email, form and so on
sent_at	When the message was sent or the response submitted, as a date cell

Dimension Labs	Sheets column	Notes
text	body	Skip rows where it is empty.
userId	customer_id	If it is empty, use the sessionId.
incoming	sender_type	true only when the customer wrote it. If every row is customer feedback (form responses, say), set it to true.
sessionId	conversation_id	Replace any colons. If it is empty, the code uses message_id, then the row number.
conversationId	conversation_id	Same value as sessionId.
dimensionlabs_timestamp	sent_at	Converted from the sheet’s time zone to UTC milliseconds.
platformJson	message_id, channel	Add any join keys you have, such as an account or store id.

Personal data. Don’t put email, phone or name columns in the header list the code reads. If your only customer key is an email, send a consistent hash of it instead. Message text can still hold personal details: redact them in the function before you send. See PII redaction.

4. Send it

Install the client in your project first: npm install googleapis.

// google-sheets.mjs. Credentials: Application Default Credentials (a service account the sheet is shared with).
// Environment: SHEET_ID, SHEET_NAME (optional, default Sheet1), DL_API_KEY.
import { google } from 'googleapis';
import { sendToDimensionLabs, toMillis } from './dimensionlabs.mjs';

const HOUR = 60 * 60 * 1000;
const DAY = 24 * HOUR;
const auth = new google.auth.GoogleAuth({ scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly'] });
const sheets = google.sheets({ version: 'v4', auth });
const clean = (s) => String(s ?? '').replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();
const safeId = (s) => String(s).replaceAll(':', '-');

// How far a time zone is ahead of UTC at a given moment, in ms.
function tzOffset(ms, timeZone) {
  const fixed = /^GMT([+-])(\d{1,2}):?(\d{2})?$/.exec(timeZone); // custom zones such as GMT-07:00
  if (fixed) return (fixed[1] === '-' ? -1 : 1) * (Number(fixed[2]) * 60 + Number(fixed[3] || 0)) * 60000;
  const parts = new Intl.DateTimeFormat('en-US', {
    timeZone, hourCycle: 'h23', year: 'numeric', month: '2-digit', day: '2-digit',
    hour: '2-digit', minute: '2-digit', second: '2-digit',
  }).formatToParts(new Date(ms));
  const p = Object.fromEntries(parts.map((x) => [x.type, Number(x.value)]));
  return Date.UTC(p.year, p.month - 1, p.day, p.hour, p.minute, p.second) - Math.floor(ms / 1000) * 1000;
}

// A date cell to Unix ms: a serial number (days since 1899-12-30, in the sheet's time zone) or text.
function cellMillis(value, timeZone) {
  if (typeof value !== 'number') return toMillis(value);
  const wall = Math.round((value - 25569) * DAY); // 25569 days from 1899-12-30 to 1970-01-01
  return wall - tzOffset(wall - tzOffset(wall, timeZone), timeZone);
}

// Sends every row whose sent_at is at or after `since` (Unix ms). Returns the new `since`.
export async function syncGoogleSheets({ since, historical = false }) {
  const spreadsheetId = process.env.SHEET_ID;
  const sheetName = process.env.SHEET_NAME || 'Sheet1';
  const meta = await sheets.spreadsheets.get({ spreadsheetId, fields: 'properties.timeZone' });
  const timeZone = meta.data.properties.timeZone;
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range: `'${sheetName}'`,
    valueRenderOption: 'UNFORMATTED_VALUE',
    dateTimeRenderOption: 'SERIAL_NUMBER',
  });
  const [header = [], ...rows] = res.data.values ?? [];
  const names = header.map((h) => String(h).trim());
  let newest = since;
  for (const [i, cells] of rows.entries()) {
    const row = Object.fromEntries(names.map((name, c) => [name, cells[c]]));
    if (row.sent_at === undefined || row.sent_at === '') continue;
    const ts = toMillis(cellMillis(row.sent_at, timeZone));
    const text = clean(row.body);
    if (ts < since || !text) continue;
    const id = safeId(row.conversation_id || row.message_id || `row-${i + 2}`);
    await sendToDimensionLabs({
      text,
      userId: row.customer_id ? safeId(row.customer_id) : id,
      incoming: String(row.sender_type).trim().toLowerCase() === 'customer',
      sessionId: id,
      conversationId: id,
      dimensionlabs_timestamp: ts,
      platformJson: { message_id: String(row.message_id ?? ''), channel: row.channel ?? null },
    }, { historical: historical || ts < Date.now() - HOUR });
    newest = Math.max(newest, ts);
  }
  return newest;
}

5. Run it

Save the code as google-sheets.mjs in a project set up as in Set up your project, and run it with the scheduled sync runner: import { syncGoogleSheets as sync } from './google-sheets.mjs';. On Google Cloud, run run.mjs as a Cloud Run job on a Cloud Scheduler trigger, as the scheduled sync page describes.

• History first: node run.mjs 2025-01-01 sends every row since that date to /trackhistorical. Then start a backfill from the Backfill tab.
• Then every 15 minutes: the scheduled job sends new rows to /track, and they are enriched automatically every day. A row whose sent_at is more than an hour old when the job sees it goes to /trackhistorical, and needs a backfill.
• Re-runs are safe: duplicates are removed automatically.
• Rate limits: the Sheets API allows 300 read requests per minute per project and 60 per minute per user per project, and answers 429 above that (Usage limits). This job makes two per run. Google recommends a 2 MB maximum payload, so split a very large sheet into several tabs.

You can also download the sheet as a CSV file and upload it. Run it through the file checker first.

Google Sheets reference

• Node.js quickstart
• Create access credentials
• spreadsheets.values.get
• spreadsheets.get
• ValueRenderOption
• DateTimeRenderOption
• Sheets API concepts (A1 notation)
• Usage limits
• Google APIs Node.js client

Checked against Google’s documentation on 2026-10-26.