Sharing data between Apps Script and Node using Google Cloud Storage (GCS) back end – 5 minute example

[Bruce Mcpherson]

Another quick demo of data sharing between apps script and Node

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 except this time, instead of using Upstash (redis) or GitHub – we’re going to use a Google Cloud Storage. Cloud storage allows a much bigger payload so this is a good choice (as it there will be less splitting across multiple records), but it does mean you have create a Cloud project and enable billing etc (although the free tier is very generous).

https://ramblings.mcpher.com/cacheplugins/apps-script-and-node-gcs/

[Joshua Snyder]

How does the speed of writing and reading, let's say a 1mb JSON object, compare with Upstash and GCS?

Will be implementing one of these solutions into my project and would be great to get a better understanding of how fast they'll be in Apps Script.

[Bruce Mcpherson]

I was going to do a post on all the pros and cons once I've finished with all the Node versions - Drive is a pretty tricky implementation in Node, so I probably won't get that finished for a little while though. 

I have a set of tests that read and write  and delete a selection of small, huge that overflow into multiple chunks, and big files - here' some rough timings. Node will often be faster because it can do multiple things at once when the API allows it (Github doesn't like that, has rate limit and API challenges - which is why the Node version is so slow)

GCS

Apps Script 11secs

Node 3 secs

Upstash

Apps Script 9 secs

Node 2.5 secs

Git

Apps Script 13 secs

Node 23 secs

Cache

Apps Script 5 secs

Node n/a

Property services

n/a - can't do this same test as it the data is too big to fit in the store limits

Drive

Apps Script 18 secs

Node - not done yet

OneDrive

Apps Script 20 secs

Node - not done yet

Note-  Upstash free version has a 400k limit for a single item - (but of course I get round that by writing multiple chunks anyway), and a 1mb limit for the paid version.  Upstash is the easiest to set up, and  has simple expiration built in, and is also the fastest. It's my favorite - but if you're consistently writing multiple chunked items, GCS might edge it as it would have less splitting up to do.

I'll publish the full results at some point when I'm done with everything.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c253e7e0-409c-4694-9a8a-282539c1cd05n%40googlegroups.com.

[Bruce Mcpherson]

.. forgot to mention - what actually gets written will be much smaller than your source data, as everything gets compressed - so 1mb will probably end up being a few hundredK depending on how compressable it is.

Here's the tests im doing

const testAll = (crusher) => {

testOne(crusher, 100, 'small')

testOne(crusher, 1000, 'big')

testOne(crusher, 100, 'smallanother', 60)

testOne(crusher, 10000, 'biganother', 60)

testOne(crusher, 50000, "huge", 60)

}

const testOne = (crusher, length, key, expiry) => {

const data = makeData(length);

crusher.put(key, data, expiry)

const recover = crusher.get(key)

if (JSON.stringify(recover) !== JSON.stringify(data)) throw key + 'failed'

}

const makeData = (length) => Array.from({ length }, () => Math.random());

const testExpired = (crusher) => {

const recover = crusher.get('smallanother')

const r = crusher.get('biganother')

if (!r) console.log('biganother expired')

if (!recover) console.log('smallanother expired')

}

const testUpstash = () => {

// 9 seconds apps script

// 2 seconds node

const crusher = new bmCrusher.CrusherPluginUpstashService().init({

tokenService: () => PropertiesService.getUserProperties().getProperty('usrwkey'),

prefix: '/crusher/store',

fetcher: UrlFetchApp.fetch,

uselz: true

})

testExpired(crusher)

testAll(crusher)

}

[Joshua Snyder]

Thanks for posting the benchmark results that you got.

I was interested to do it myself and tried to run your script using the bmCrusher library. Both the expired tests worked, but I got an error whilst trying to write the small object.

"failed to set value for key"

I thought this might be a write permissions error, but I used the READ/WRITE key for Upstash so not sure what is causing it...

[Bruce Mcpherson]

Here's the log using bmcrusher 

library v15 

3:04:41 PM

Info

biganother expired

3:04:41 PM

Info

smallanother expired

3:04:43 PM

Info

done small

3:04:44 PM

Info

done big

3:04:46 PM

Info

done smallanother

3:04:48 PM

Info

done biganother

3:04:53 PM

Info

done huge

check your upstash graphQL console to see if anything is written 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/66aae89e-505e-4266-8dcb-c357b5603072n%40googlegroups.com.