Using Google OAuth Client to read & write to a Google spreadsheet from a private notebook

Community Help
1

I have been trying to read and write data to a private Google spreadsheet from a private notebook.
I have forked this notebook (thanks again, Grant :grinning:) and managed to sign-in into my Google account.
When I try to access my Google spreadsheet with the gapi object it returns an array of 2 (range?) objects that is shortly black, but then turns red.

In the Chrome console I find these two error messages:

Refused to load the image ‘https://www.google.de/ads/ga-audiences?t=sr&aip=1&_r=4&slf_rd=1&v=1&_v=j96&tid=UA-112925870-1&cid=1873574770.1644056935&jid=1846995577&_u=SACAAEAAAAAAAC~&z=1609664656’ because it violates the following Content Security Policy directive: “img-src https://.static.observableusercontent.com https://static.observablehq.com https://observablehq.com https://.githubusercontent.com https://*.stripe.com https://avatars.observableusercontent.com https://www.google-analytics.com https://www.google.com/ads/ga-audiences static.observableusercontent.com data: blob:”.

and …

Failed to execute ‘postMessage’ on ‘DOMWindow’: The target origin provided (‘https://snoopy.static.observableusercontent.com’) does not match the recipient window’s origin (‘https://observablehq.com’).

,followed by an Error 400 from the Google server (failed-to-load).

In the Google OAuth 2.0 client I have the following JavaScript origin authorised (without any redirect URIs): https://snoopy.static.observableusercontent.com

And these are the scopes:

image
image476×622 31.1 KB

Any idea what could be wrong here ?
I should perhaps also mention that I this is my first encounter with OAuth on the software side of things.

2

Have you tried the built-in cloud files feature? It could simplify things a bunch for you, or do you want more control over the oauth flow?

3

try adding observablehq.com to authorized JavaScript origins? See if it works. The inbuilt cloud files interface is easier though

4

I’d love to - but according to the documentation the scopes are limited to file read-only. And I also need to write to it. Could I extend the scopes myself ?

I tried, without success.

5

I want to write to the file, too.

6

What puzzles me, too, is the fact that when I initially tried this, I am sure it did work. I just cannot reproduce it anymore.
Every now and then I also get an error message like “unauthorized daily quota exceeded” from my many trials & errors …

7

Oauth is somewhat tricky. I have got unstuck by recreating all my keys before.

If its for a one off type thing for personal use, not multi user, another route is to share the document with a service account instead of Oauth Access Google Doc with Service Account Example in Typescript — Futurice

After you do an oauth handshake, you get a token. You can test that token in isolation using google developer API playground. I highly recommend that resource for developing google API intergrations.

You can also obtain the token in the playground and pass that to your application to figure out exactly where the issue is. It’s just handy having a working system to compare against.

8

The Service account looks like a perfect fit for me, which I’ll try next.

But could it be that the issue that I am having is actually caused by this Observable notebook page load error that I just discovered ?
Below you’ll see the Chrome console output that I get already when I start a brandnew notebook:

image
image1600×900 191 KB

It does not appear when I load other pages, like the Observable Community page, Google search, …

9

No I get that on notebooks too

image
image2880×1354 451 KB

EDIT: jsut realised the origin exception is not coming from the oauth handshake… so no idea what is going wrong with the Oauth.

10

I do have a login with google oauth client here: Oauth 2.0 Client Examples / Tom Larkworthy / Observable

this is not using Google API client (GAPI) though, but I think the token can be passed to the GAPI client. (oh mine uses webcode.run to store a client secret, its the webserver flow, kinda simpler IMHO)

11

When I try to import these two lines of your script on top notebook level
import fs = require(‘fs’);
import {JWT, auth} from ‘google-auth-library’;
I get the error message “unable to load module”. And I don’t find the google-auth-library, either.
Do you happen to have alternative import statements for me, or what could I do instead ?

12

yes that code is for nodejs, not Observable. Sorry I was talking more conceptually that the specifics of that code.

If you have a service account, you can login to GAPI like the code here: Google API Client / Tom Larkworthy / Observable

I have not really generalised that notebook, maybe we should! But short term you are expected to fork it and mess about with the apiKey and discovery doc settings.

discovery doc for sheets API is https://developers.google.com/sheets/api/reference/rest i.e. * https://sheets.googleapis.com/$discovery/rest?version=v4

13

I have been using Google OAuth with AWS Cognito Identity Pools to secure AWS resources such as Lambda functions and it’s been working like a charm except on mobile (Google OAuth Javascript API doesn’t seem to support mobile?). For my Google Cloud project, my authorized Javascript origin for OAuth is https://gnestor.static.observableusercontent.com, gnestor being my Observable handle, so you just need to replace that with your own handle. You don’t need to set an “authorized redirect URI.” After you update our OAuth credentials, copy the “Client ID” from the console and use it as your clientId variable in the notebook. Lastly, be sure that you have enabled the sheets API in the “Enabled API & Services” section of the console and that https://www.googleapis.com/auth/spreadsheets is included in your scopes. I hope this helps :+1: