Quick demo of data sharing

There are many ways to accomplish this of course, but using Caching across multiple Apps Script and Node projects using a selection of back end platforms is pretty fast to set up. To test this, 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 except this time, instead of using Upstash (redis) – we’re going to use a Github Repo.

Plan

  • Github as the back end crusher platform and get credentials
  • bmPreFiddler  to deal with the data in the spreadsheet
  • bmcrusher-node as the node client
  • scrviz to quickly find the apps script library credentials

Github credentials

Assume you’ve set up a github personal token for Node and either an oauth dance or the same personal token for the Apps Script side. If you want to use Oauth for Apps Script, then you’ll need an extra 5 minutes for that. For Oauth2, see Github as an Apps Script cache platform  if you haven’t done that before. If you prefer you can use a Github personal access token for both Node and Apps Script, here’s how to set one up in your Github settings. You’ll need repo scope.

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

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 testShare = () => {

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

// client for crusher - the upstash key is already in property store
// assumes you're using the OAUTH2 method, otherwise just return your personal access token to the token service
const goa = cGoa.make('gistgoa', PropertiesService.getScriptProperties())
const crusher = new bmCrusher.CrusherPluginGitService().init({
tokenService: () => goa.getToken(),
prefix: 'store',
fetcher: UrlFetchApp.fetch,
repo: '-crusher-store',
owner: 'brucemcpherson',
uselz: true
})

// put the data to the store so that node can get it
crusher.put ('airports', fiddler.getData() )

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

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

Github repo

I’m using a specific repo dedicated to all my crusher keys. It’s not essential, but probably best. Here’s what the repo looks like. The airports key us the one we’ve just written

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 my github personal access code from a file, but you do whatever here to get yours.

const { CrusherPluginGitService } = require("bmcrusher-node");
const { crusherGit } = require("./private/secrets");

const gitCrusher = new CrusherPluginGitService().init({
tokenService: () => crusherGit,
prefix: "store",
repo: "-crusher-store",
owner: "brucemcpherson",
});

const testAirports = async (crusher) => {
const airports= await crusher.get('airports')
console.log(airports);
}
testAirports(crusher)
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