# Google Sheets > Send conversation messages and feedback kept in Google Sheets to Dimension Labs with the universal API. 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](/api/tracker). ## 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](/api/tracker#your-api-key). ## 1. Get access 1. In the Google Cloud console, enable the **Google Sheets API** for your project ([Node.js quickstart](https://developers.google.com/workspace/sheets/api/quickstart/nodejs)). 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](https://developers.google.com/workspace/guides/create-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](https://github.com/googleapis/google-api-nodejs-client#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](https://developers.google.com/workspace/guides/create-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: ```bash 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](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets)). A field mask keeps grid data out of the reply ([spreadsheets.get](https://developers.google.com/workspace/sheets/api/reference/rest/v4/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](https://developers.google.com/workspace/sheets/api/guides/concepts)). Empty trailing rows and columns are left out ([ValueRange](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values)). - 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](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/get), [DateTimeRenderOption](https://developers.google.com/workspace/sheets/api/reference/rest/v4/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](/data/pii-redaction). ## 4. Send it Install the client in your project first: `npm install googleapis`. ```js // 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. 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](/api/tracker#set-up-your-project), and run it with the [scheduled sync](/api/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](/dimensions/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](https://developers.google.com/workspace/sheets/api/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](/data/upload-a-file). Run it through the [file checker](/data/check-a-file) first. ## Google Sheets reference - [Node.js quickstart](https://developers.google.com/workspace/sheets/api/quickstart/nodejs) - [Create access credentials](https://developers.google.com/workspace/guides/create-credentials) - [spreadsheets.values.get](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/get) - [spreadsheets.get](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/get) - [ValueRenderOption](https://developers.google.com/workspace/sheets/api/reference/rest/v4/ValueRenderOption) - [DateTimeRenderOption](https://developers.google.com/workspace/sheets/api/reference/rest/v4/DateTimeRenderOption) - [Sheets API concepts (A1 notation)](https://developers.google.com/workspace/sheets/api/guides/concepts) - [Usage limits](https://developers.google.com/workspace/sheets/api/limits) - [Google APIs Node.js client](https://github.com/googleapis/google-api-nodejs-client) Checked against Google's documentation on 2026-10-26.