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.

Plan

Drive 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.

create service account

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.

Impersonation

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.

admin.google.com

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.

Source data

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

const testShareDrive = () => {

// get the airports sheet
// provoke a driveapp auth DriveApp.getFolders()
const fiddler = bmPreFiddler.PreFiddler().getFiddler({
id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
sheetName: 'airport list',
createIfMissing: false,
})

const crusher = new bmCrusher.CrusherPluginDriveService().init({
store: DriveApp,
prefix: '/crusher/store',
uselz: true
})


// put the data to the store so that node can get it
// since it's just a test we'll expire it after 300 seconds - 5 minutes
crusher.put('airports', fiddler.getData(), 1000)

}
get the data and write it to the crusher drive store

Note that the only difference between backends is how they are initialized. And that’s all there is on the Apps Script side.

Drive

Here’s what crusher files in Drive look like.

crusher files on drive

Node client

The node client is even briefer, but first install the bmcrusher-node module from npm using either npm or yarn

yarn add bmcrusher-node
add from npm

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

(async () => {
// drive crusher is an async initialiation
const driveCrusher = await new CrusherPluginDriveService().init({
tokenService: () => getDriveCreds(),
prefix: "/crusher/store",
subject: "bruce@mcpher.com",
});

const testAirports = async (crusher) => {
const airports= await driveCrusher.get('airports')
console.log(airports);
}
testAirports(driveCrusher);
})();
get data into node

And we’re done – in less than 5 minutes –

  {
name: 'Vilnius International Airport',
latitude_deg: 54.6341018676757,
longitude_deg: 25.2858009338378,
elevation_ft: 646,
iso_country: 'LT',
iso_region: 'LT-VL',
municipality: 'Vilnius',
iata_code: 'VNO',
timestamp: ''
},
{
name: 'Cape Town International Airport',
latitude_deg: -33.9648017883,
longitude_deg: 18.6016998291,
elevation_ft: 151,
iso_country: 'ZA',
iso_region: 'ZA-WC',
municipality: 'Cape Town',
iata_code: 'CPT',
timestamp: ''
},
... 456 more items
data from apps script airports

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.

Links

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

The nodejs Drive API, service account impersonation and async iterators