Using a Google service account

Release:	… • 4.6 • 5.0 • 5.1 • 5.2 • 5.3 • 5.4 • 6.0 • 6.1 • 6.2 • 6.3 • 6.4 • 6.5 • • 6.6 • 7.0 • 7.1 • 7.2

New to Analytica 7.2

Normally the GoogleSheets backend opens a spreadsheet as you: the first time, your web browser opens, you sign in to Google and choose the spreadsheet, and the connection is remembered on your computer.

That cannot work where there is nobody at a browser -- a server, a scheduled task, a build, a model running under ADE on a machine nobody signs in to. For those, Analytica can open a spreadsheet as a Google service account: an account that belongs to a program rather than a person, and that proves who it is with a key file instead of a sign-in. You share the spreadsheet with the service account's e-mail address, exactly as you would share it with a colleague, and pass its key to SpreadsheetOpen in the «account» parameter.

When to use one

An unattended machine: a scheduled model run, a server, a continuous-integration job, or ADE on a machine where nobody has ever run the desktop connect flow.
A spreadsheet that should be read as itself rather than as whoever happens to run the model -- a reference dataset the model's author maintains, where you do not want every reader to need their own access to it.

Do not use one for an ordinary desktop model. There the browser connection is simpler, it keeps each person reading with their own permissions, and it needs no key to look after.

Creating the service account

In the Google Cloud console, choose or create a project, and enable the Google Drive API and the Google Sheets API for it. (The Picker API is not needed -- a service account never sees a file chooser.)
Go to IAM and admin, Service accounts, and create one. Give it a name you will recognize later. It needs no project roles: the only access that matters is the sharing you do in step 4.
Open the new account, go to Keys, Add key, Create new key, type JSON. Google downloads a small .json file, once -- it cannot be downloaded again. This file is the account's password; treat it like one.
Copy the account's e-mail address, which looks like something@your-project.iam.gserviceaccount.com, and share each spreadsheet with it in Google Sheets exactly as you would with a person: Share, paste the address, Viewer for read-only or Editor if the model writes. A service account sees nothing that has not been shared with it.

The service account needs no consent screen, no Google verification, and no security assessment, because nobody is being asked to grant it anything: it holds its own credentials, and you decide what it can reach by sharing.

Giving Analytica the key

Put the contents of the .json file in a Secret, and pass the Secret as the «account» parameter:

SpreadsheetOpen("https://docs.google.com/spreadsheets/d/id/edit", account: Sheets_Robot_Key)

where Sheets_Robot_Key is a Secret whose value is the whole JSON text. Set its Sinks to SpreadsheetOpen and its Destination to https://oauth2.googleapis.com/, so the key can be used for this and nothing else; Analytica refuses the substitution otherwise. Which storage kind the Secret uses -- in the model, an environment variable, per machine user, or a vault -- is your choice, and is the thing that decides who else can run the model.

Passing the JSON as a plain text literal works too, and is the wrong thing to do in a saved model: anyone who opens the file then has the key.

The «backend» parameter is not needed; a Google Sheets link selects the backend by itself.

What is different with a service account

Nothing interactive ever happens. No browser opens, no dialog appears, and nothing waits for a person. If the account cannot reach the spreadsheet the call reports that and stops.
The file chooser is not involved. Any spreadsheet shared with the account opens directly from its link. There is no "pick one" mode, so SpreadsheetOpen("") and showDialog: True have nothing to offer.
It ignores the desktop's connection entirely. The Windows user's own Google connection, and the client-registration settings, play no part; the key carries its own identity and its own permissions. You can use a service account for one spreadsheet and your own connection for another in the same model.
Reads and writes behave exactly as they do otherwise: a snapshot on open, writes sent when the computation finishes, on SpreadsheetSave(wb), or on SpreadsheetSetInfo(wb, 'Refresh', true). Give the account Editor on the spreadsheet if the model writes to it.
SpreadsheetInfo(wb, 'Account') reports the service account's address, so a model can say which identity a workbook was opened with.
SpreadsheetOpen("New", backend:'GoogleSheets', account: key) does work, but the new spreadsheet is created in the service account's own Drive, where no person can see it. That is occasionally what you want for scratch space and almost never what you want otherwise; a service account has no Drive anyone can browse.

When it does not work

The most common failure is the one that has nothing to do with Analytica: the spreadsheet was never shared with the service account. Analytica says so by name --

The Google service account name@project.iam.gserviceaccount.com cannot open the spreadsheet ...

-- and the cure is to share it at that address. Note that sharing a folder with the account shares what is in it, which is usually the tidier arrangement for a model that reads several spreadsheets.

A key that is damaged, truncated, or not a service-account key at all is reported when the call is evaluated, before any network request, so a mistyped Secret fails immediately rather than halfway through a run.

Some organizations' Workspace policies block service-account keys from being created at all, or disable them after a period. If the console will not produce a key, that is an administrator's setting, not a Google-wide limit.

Not supported

Domain-wide delegation -- a service account impersonating a member of your Workspace -- is not supported. The service account acts as itself, and sees what has been shared with it.