Ever since Google turned off direct access to the JSON data from Google Sheets, I’ve used a couple of App Script workarounds. They were a bit clumsy for what I was doing but better than re-writing the entire page. I find going through the whole oAuth and Google project process to be more hassle than my typical Google Sheets project deserves. Recently, Google made some more changes that broke these workarounds. Many people may feel I’m getting what I deserve for using Google. Others have no idea what I’m talking about anyway.
Because I don’t like to “go along to get along,” I wasn’t quite ready to go the route Google is forcing and I couldn’t find any other place that would provide the same options I get with Google Sheets. So . . . I decided to publish the sheets to the web as CSV (This remains a built in option in Sheets.) and then use a javascript library called Papa Parse to turn the CSV into JSON.
This gives me the added benefit that if Google shuts down CSV publishing for Sheets, I can easily get the CSV from the regular spreadsheet rather than having to reconstruct Google’s particular JSON structure. I could also set up a PHP script to fetch the file every so often if that became necessary. Papa Parse is also a tiny library that’s very fast and can handle large amounts of data. Hopefully, I’m exploiting Google for what it provides without allowing them to fully counter-exploit me.
You can see it working in the Code Pen example below. I’m writing ~500 spreadsheet rows to the page as JSON for demonstration. It’s fast.
See the Pen
Papaparse Remote Example by Tom (@twwoodward)
on CodePen.