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.

Plan

Upstash 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

Upstash credentials

Assume you’ve set up an upstash account and have a graphql access key. See Upstash as an Apps Script cache platform if you haven’t done that before.

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
  const crusher = new bmCrusher.CrusherPluginUpstashService().init({
    tokenService: () => PropertiesService.getUserProperties().getProperty('usrwkey'),
    prefix: '/crusher/store',
    fetcher: UrlFetchApp.fetch,
    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

I’m picking my upstash access key from the property store – obviously you do whatever to pass over your key. And that’s all there is on the Apps Script side.

Upstash console

Here’s what the data looks like in the back end upstash store.

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

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

const crusher = new CrusherPluginUpstashService().init({
  tokenService: () => upstashrw,
  prefix: "/crusher/store"
});
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