Using your own Google OAuth client

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

Requires Analytica 7.2

When the GoogleSheets backend of SpreadsheetOpen connects to Google, Analytica identifies itself to Google as an application named Analytica, registered by Lumina Decision Systems. That needs no setup and is the right arrangement for nearly everyone.

This page is for the few organizations that would rather have Analytica identify itself as an application registered by them. It describes what that does and does not change, when it is worth the trouble, and how to set it up. If what you need instead is for a server, a scheduled job or ADE to open a spreadsheet with nobody present, see Using a Google service account.

What the application identity is, and is not

The client ID and client secret that Analytica sends to Google say which program is asking. They say nothing about who you are, and nothing about which spreadsheets it may touch. In particular, using Lumina's registration does not restrict Analytica to non-confidential spreadsheets:

  • Analytica asks Google for one permission, drive.file, which lets it see only the spreadsheets you yourself select in Google's file chooser, plus ones it creates. A confidential spreadsheet is no different from any other: select it in the chooser and Analytica can read and write it.
  • No spreadsheet content and no Google credential ever passes through Lumina. Your browser talks to Google, your computer exchanges tokens with Google directly, and the resulting connection is kept in the Windows Credential Manager under your Windows account. Registering your own application changes none of that.
  • On its own, your own registration does not remove the browser step, and does not change which spreadsheets the chooser offers you. It is what makes the broader permission below possible, and that is what removes the chooser.

Why you might want your own

  • Your organization controls which third-party applications may use its Google Workspace data. A Workspace administrator can allow or block individual applications (Admin console, under Security, API controls). If Analytica has not been allowed, connecting fails, or Google's chooser offers nothing you can select. An application registered inside your own organization is under your administrator's control from the start, instead of needing an exception made for an outside one.
  • You want to skip the file chooser altogether. Google will only grant an application permission to every spreadsheet after a review, and for an outside application that review is an annual paid security assessment. An application registered as Internal to your own Google Workspace skips it entirely. See #Asking for a broader permission below.
  • You want the consent screen, the grants and the audit trail inside your own organization. The browser names your application, the grants appear in your own Google Cloud project, and an administrator can review or revoke them centrally.
  • You want your own API quota. Google counts Drive and Sheets API usage per Cloud project. Your own project gets its own quota, rather than sharing a pool with other Analytica users.
  • You would rather Analytica's access to your data not depend on an application registered by another company at all.

If none of these apply to you, use the built-in registration and ignore this page.

Registering the application with Google

You need a Google Cloud project, which is free.

  1. In the Google Cloud console, create a project, or choose one your organization already uses.
  2. Enable three APIs for it (APIs and services, then Enable APIs and services): Google Drive API, Google Sheets API and Google Picker API. The Picker API is the one that presents the file chooser; without it the connection cannot finish.
  3. Configure the OAuth consent screen (Google Auth Platform, then Branding). Give it an application name -- this is the name the person sees in the browser, so use one they will recognize -- a user-support e-mail address, and a developer contact address.
    • Choose the Internal user type if your organization uses Google Workspace and only its own members need to connect. Internal applications skip Google's verification process entirely, which also makes the broader permission below available to you.
    • External also works for the default permission. Because Analytica asks only for the non-sensitive drive.file permission, an External application can be published without a Google review. While it is left in Testing, though, only the accounts listed as test users can connect, and their connections stop working after seven days.
  4. Create the credential: APIs and services, Credentials, Create credentials, OAuth client ID, application type Desktop app. Copy the Client ID and the Client secret that Google shows. There is no redirect URI to fill in -- a desktop client may use the local loopback address that Analytica uses.

Google calls the second value a secret, but for a desktop application it is not a true secret: it is distributed inside the program, and Google's own documentation treats installed applications as unable to keep it confidential. Look after it as you would any configuration value, but it is not a password, and on its own it grants access to nothing.

Telling Analytica to use it

Set two values in the registry, under

HKEY_CURRENT_USER\Software\Lumina Decision Systems

for one Windows user, or under

HKEY_LOCAL_MACHINE\SOFTWARE\Lumina Decision Systems

for everyone on the computer. If a value appears in both, the HKEY_CURRENT_USER one wins.

GoogleOAuthClientId (REG_SZ)
The full client ID, ending in .apps.googleusercontent.com
GoogleOAuthClientSecret (REG_SZ)
The client secret
GoogleOAuthScope (REG_SZ)
Optional. See #Asking for a broader permission below. Leave it unset to keep the default.

That key is deliberately the one above the per-product keys, so a single setting covers Analytica, ADE and the other Analytica products alike. Use it: one Google connection is shared by every product running as the same Windows user, and it is remembered under the client ID, so a client ID set for only one of them would leave the others unable to find the connection. (A value under a product's own key -- ...\Lumina Decision Systems\Analytica, ...\ADEW -- still wins for that product, if you ever need to make one of them differ.) None of these keys has a release number in it, so the setting applies to every installed release.

Set both the ID and the secret. If the ID is given without the secret, Google refuses the connection. As a .reg file: 

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Lumina Decision Systems]
"GoogleOAuthClientId"="123456789012-examplexample.apps.googleusercontent.com"
"GoogleOAuthClientSecret"="GOCSPX-exampleexampleexample"

Removing the values restores the built-in registration.

The change takes effect at the next connection; there is no need to restart Analytica. Connections are remembered separately for each client ID, so the first spreadsheet you open after the change asks you to connect once more. Your earlier connection is left untouched, and is used again if you remove the values.

Asking for a broader permission

By default Analytica asks Google for one permission, https://www.googleapis.com/auth/drive.file, which covers only the spreadsheets you pick in Google's chooser. That is why a spreadsheet you have not used with Analytica before sends you to the browser once.

The optional GoogleOAuthScope value replaces that request. Setting it to

https://www.googleapis.com/auth/drive

asks instead for access to your Drive as a whole. The effect on everyday use is that the file chooser disappears: you connect once, and after that every spreadsheet link you paste simply opens, in any model, with no further browser trips. The trade is the single consent screen at the start, which now asks for a great deal more, so it is a decision to take deliberately rather than a convenience to switch on by reflex.

Two things to know before you use it:

  • In practice this needs an Internal application. Google classes whole-Drive access as a restricted scope: an External application must pass an annual, paid third-party security assessment before Google will allow it, which is why Analytica's built-in registration does not offer this. An Internal application in your own Workspace is exempt from verification, so the setting works immediately there. Do not set it while using Lumina's built-in registration.
  • .../auth/spreadsheets alone is not enough. Analytica reads a native Google Sheet through Drive's export, and an Excel workbook stored in Drive through a Drive download, so it needs a Drive permission and not only a Sheets one. Give the value https://www.googleapis.com/auth/drive, or a space-separated list containing it.

Changing this value makes Analytica ask you to connect again, because the connection it has was granted under the old permission. With any value that does not include drive.file there is no chooser to browse in, so SpreadsheetOpen("") and showDialog: True no longer offer one -- give a spreadsheet link instead.

Checking that it worked, and disconnecting

Open any Google Sheets link with SpreadsheetOpen. The browser page that asks for permission should now name your application rather than Analytica. Afterwards:

SysLib_Internal::GoogleAccountEmail() -- the Google account now connected, or Null when there is none.
SpreadsheetInfo(wb, 'Account') -- the account an open workbook was opened with.

To disconnect -- which revokes the access at Google and forgets the stored connection -- evaluate

SysLib_Internal::DisconnectGoogleAccount()

from a button's OnClick, or in the Typescript window. It is a side effect, so it is refused inside a variable's Definition. It returns the e-mail address of the account that was disconnected, or Null when none was connected. You can also revoke Analytica's access at any time from your Google account's permissions page.

Notes and limits

  • One Google connection is remembered at a time for a given client ID, for the Windows user who made it. ADE scripts running as the same Windows user share that connection, which is why connecting once in Analytica is enough for them -- and why the settings above belong in the shared registry key.
  • A model published to the Analytica Cloud Platform does not use these settings. There, each person connects their own Google account through the ACP server's own registration, and the settings on the author's computer play no part.
  • These settings say how the person at the browser connects. They have no effect on a service account given to SpreadsheetOpen's «account» parameter, which carries its own credentials and its own permissions.

See Also

Comments
Loading comments...