Google Spreadsheet JSON Breaks / Errors and The Fix

One by one, portions of customer sites whose content is driven from a Google Spreadsheet, their data started failing. Slowly at first, more rapidly and within the last 48 hours, completely.

Google quietly sunset their Google Sheets API and I was fortunate to quickly find how a developer discovered the change outlined the update needed.

• Requires a new URL structure

• Use tab name rather than number

• Need to add an API Key from cloud.google.com (requires a free account)

• Need to activate that API Key

• Need to re-write some of your JSON structure calls

While this is an extreme pain, the new JSON output is absolutely beautiful compared to that chaos which was insanely difficult to navigate.

This is the fix below as we’re updating.

See the Pen Google Spreadsheet Driven Content V4 by Gary Ricke (@garyricke) on CodePen.

Below: Google JSON output of a spreadsheet before and after the change to the Sheets API 4

Before

After

Above: the new file size is ~96% smaller which equates to a much faster application load and as you can see with the simpler format, the code that parses out that information is significantly simpler and smaller.

LINK: Check out the new JSON
LINK: Old Google JSON — pure chaos — may not work

How to get JSON from a Google Spreadsheet

1) Create a Google Spreadsheet and populate it

2) Publish the Spreadsheet to Web

File > Share > Publish to the Web

Then click Publish

Ignore the prompt to copy the published URL

Exit the Publish for Web Dialog the grab the Share link

From the copied URL, grab the key or string of characters as shown below to plug into the Javascript function.

The Redundancy Plan

googleSpreadsheetJSONBackup.net?orbis-clients-20210819.json

• $9.00 / month

• 5 or less files

• Backup once daily

Designed so you can check for a file call error, switch to the backup using a standard date format (minus 1 day — i.e. yesterday) and then send an email to alert we’re running on backup.

squarespaceWebsiteBackup.net?orbis-20210819

• $15 / month

• 1 website

• Backup once daily

• +$5 :: DNS Instant On (DNS is tested and ready for quick switch to backup site)

• +$5 :: Squarespace page JSON

URL opens to a folder with HTML, images, JS and CSS for that day.