Google Apps Script that publishes a Google Sheet to Amazon S3 as a JSON file. Auto-updates on edit & maintains data types. Creates an array of objects keyed by column header.
Turn a spreadsheet like this:
Into an auto-updating JSON file like this:
Get the add-on here on the Chrome Web Store.
"I want to manage simple, structured, data in an easily updatable way (possibly by multiple people at once) that can be published for use by other systems without the overhead and time of coding, deploying, and maintaining a full-blown web application."
Staff directory list, restaurant menu items listing, sports team standings page, etc.
- Doesn't require OAuth like the official Google Sheets API (no good for anonymous data viewing).
- Not using deprecated APIs like Tabletop.js that could suffer an untimely disappearance at the whims of Google.
- Doesn't require an intermediary web application like WSJ uses/used.
- Not an alternative service like Airtable or Fieldbook that is powerful but costs 💰💰💰.
- Not slow at returning data like Google Apps Script Web Apps seem to be. (If you're okay with 2000ms response times, this solution is easier because it doesn't involve S3. S3 response times tend to be 10-20x faster.)
An Amazon S3 bucket for which you have created security credentials that have write permissions to the bucket.
To make the published JSON publicly accessible, the following must be applied to the S3 bucket:
- A CORS policy that allows GET requests from whatever origin (domain name) you want to access the data from. The default policy allows access from any origin.
- A bucket policy that enables public viewing of the published JSON.
See Amazon S3 documentation for how to do this.
- Create or open an existing Google Sheet.
- Format the sheet so that the first row contains the column headers you want your JSON objects to have as properties. Example:
- Install and enable the add-on.
- In the spreadsheet's Add-ons menu, click "Configure..."
- Fill in the S3 bucket name, path within the bucket (leave blank if none), and AWS credentials that allow write access to the bucket.
- Click "Submit". The S3 URL of your JSON-ified spreadsheet will be shown.
Did I miss something in these instructions? Not working as expected? Feel free to file an issue.
That's it! Any time you make a change to the spreadsheet, the changes will be re-published to the JSON file. The JSON file's filename is taken from the spreadsheet ID, so the spreadsheet can be renamed without breaking the URL.
- The add-on only looks at the sequentially first sheet tab (called "Sheet1" by default). It won't publish or respond to changes on other tabs.
- The add-on will ignore columns that don't have a value in the header (row 1) of the spreadsheet.
- The add-on will ignore empty rows, skipping over them to the next row with values.
- A blank cell in a row is represented in the JSON as
null. So if you have a column that could have missing or optional values, be sure to handle thenullvalue in your consuming code.
- Create a new Google Apps Script with files whose names and content matches the ones in this repo (minus README and LICENSE).
- Add the Amazon S3 API Binding as
AWS. - In the menu bar, click Publish > Test as add-on...
- Select a version, for "Installation Config", choose "Installed and enabled", and select a document (must be a spreadsheet). Save.
Note: While testing, the event registration part may not work. If so, you can uncomment the additional menu item Publish Now to trigger the publish manually.