Another quick demo of data sharing
Here’s a challenge that shares the data in a spreadsheet with node, set up end to end in less than 5 minutes. This example is almost exactly the same as Sharing data with Upstash between Node and Apps Script – end to example in 5 minutes , and Sharing data between Apps Script and Node using Github back end – 5 minute example, and Sharing data between Apps Script and Node using Google Cloud Storage (GCS) back end – 5 minute example except this time, instead of using Upstash (redis), GCS or GitHub – we’re going to use a Google Drive. Drive is a good choice if you don’t want to enable any other services than those you already have with Google Workspace.
- Drive as the back end.
- bmPreFiddler to deal with the data in the spreadsheet
- bmcrusher-node as the node client
- scrviz to quickly find the apps script library credentials
We need to do some fiddling around to create a service account for node to use, and to allow that to impersonate you to access your Drive files. The first section of this article, The nodejs Drive API, service account impersonation and async iterators describes how to do that. It’s pretty straightforward. You’ll end up with a credentials file you can use on Node later on. Here’s how to do it.
Use a Service Account to impersonate yourself
Because we’re doing server side actions here, we can use a service account. That’s simpler than playing around with Node Oauth2 if you are using a pedefined Drive belonging to you. However there are a couple of extra wrinkles as a result of using a service account with your own Drive folder to host the data being shared between Apps Script and Node. Actually, you can of course share between Node and Node too, but in any case, you’ll still need to get accesss to a Drive.
Creating the service account
In the Cloud Console, enable the Drive API, create a service account key and pay particular attention to the items marked here. You’ll need them later.
You can then download the json file for that account and put it somewhere private in your Node project. Remember – Don’t commit it to github.
The first step in enabling impersonation is to enable G Suite delegation for that service account – done above when the service account is created. Next, we need to allow that that service account to have access to the scopes we’ll allow it to access. That’s in admin.google.com. You need to get the clientID from the service account, add it, and allow it the drive scope.
That’s the set up over, and we should be good to go. I’ll walk you through a utility module to interact with the Drive API from node
Create an apps script project, and find and add the necessary libraries
We can use scrviz to quickly find the library keys, which are normally a pain to find
- find and add bmCrusher
- find and add bmPreFiddler
Apps Script credentials
DriveApp is built into Apps Script so you don’t need to do any additional OAUTH2 stuff on the Apps Script side.
We’re going to get a sheet from this spreadsheet that contains a bunch of stuff about the main airports worldwide.
so all we need is the spreadsheetId and the name of the sheet, get the data, and write to the upstash store.
Code for getting spreadsheet data and writing to cache
Note that the only difference between backends is how they are initialized. And that’s all there is on the Apps Script side.
Here’s what crusher files in Drive look like.
The node client is even briefer, but first install the bmcrusher-node module from npm using either npm or yarn
Here’s the code
I’m picking up the service account from a file. This is the same service account .json file you set up earlier. There are a number of strategies in Node to handle Google credentials, so pick the one you are most comfortable with. Note that you will also have to prove the subject. That’s the email address of the person (you) that owns the Drive that will be used for storage
And we’re done – in less than 5 minutes –
Of course you could process the data in some way if you want, and write it back for Apps Script to pick up, clean up the store etc.
scrviz Apps Script bmCrusher: https://scrviz.web.app?repo=brucemcpherson%2FbmCrusher
scrviz Apps Script bmPreFiddler: https://scrviz.web.app?repo=brucemcpherson%2FbmPreFiddler
github bmcrusher-node: https://github.com/brucemcpherson/bmcrusher-node