All Projects → liddiard → Google Sheet S3

liddiard / Google Sheet S3

Licence: mit
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.

Programming Languages

javascript
184084 projects - #8 most used programming language

Projects that are alternatives of or similar to Google Sheet S3

Sqlitebiter
A CLI tool to convert CSV / Excel / HTML / JSON / Jupyter Notebook / LDJSON / LTSV / Markdown / SQLite / SSV / TSV / Google-Sheets to a SQLite database file.
Stars: ✭ 601 (+641.98%)
Mutual labels:  google-sheets, json
S3 Site Cache Optimizer
Optimize a static website for hosting in S3, by including a fingerprint into all assets' filenames. The optimized website is uploaded into the specified S3 bucket with the right cache headers.
Stars: ✭ 9 (-88.89%)
Mutual labels:  s3, s3-bucket
Sheetjs
📗 SheetJS Community Edition -- Spreadsheet Data Toolkit
Stars: ✭ 28,479 (+35059.26%)
Mutual labels:  spreadsheet, json
Visidata
A terminal spreadsheet multitool for discovering and arranging data
Stars: ✭ 4,606 (+5586.42%)
Mutual labels:  spreadsheet, json
Cuba
🇨🇺 Google Sheets + SQL = JSON
Stars: ✭ 45 (-44.44%)
Mutual labels:  spreadsheet, google-sheets
Aws
A collection of bash shell scripts for automating various tasks with Amazon Web Services using the AWS CLI and jq.
Stars: ✭ 493 (+508.64%)
Mutual labels:  s3, json
Mintable
🍃 Automate your personal finances – for free, with no ads, and no data collection.
Stars: ✭ 849 (+948.15%)
Mutual labels:  spreadsheet, google-sheets
terraform-aws-s3-bucket
A Terraform module to create a Simple Storage Service (S3) Bucket on Amazon Web Services (AWS). https://aws.amazon.com/s3/
Stars: ✭ 47 (-41.98%)
Mutual labels:  s3, s3-bucket
Goloc
A flexible tool for application localization using Google Sheets.
Stars: ✭ 42 (-48.15%)
Mutual labels:  google-sheets, json
Minio Hs
MinIO Client SDK for Haskell
Stars: ✭ 39 (-51.85%)
Mutual labels:  s3, s3-bucket
Nodb
NoDB isn't a database.. but it sort of looks like one.
Stars: ✭ 353 (+335.8%)
Mutual labels:  s3, json
Pygsheets
Google Sheets Python API v4
Stars: ✭ 1,116 (+1277.78%)
Mutual labels:  spreadsheet, google-sheets
Goofys
a high-performance, POSIX-ish Amazon S3 file system written in Go
Stars: ✭ 3,932 (+4754.32%)
Mutual labels:  s3, s3-bucket
Gspread
Google Sheets Python API
Stars: ✭ 5,676 (+6907.41%)
Mutual labels:  spreadsheet, google-sheets
simply-static-deploy
WordPress plugin to deploy static sites easily to an AWS S3 bucket.
Stars: ✭ 48 (-40.74%)
Mutual labels:  s3, s3-bucket
Googlesheets
Google Spreadsheets R API
Stars: ✭ 771 (+851.85%)
Mutual labels:  spreadsheet, google-sheets
lifebot
Use Google Sheets to log your life by texting it Emojis and pulling in data from Fitbit automatically.
Stars: ✭ 15 (-81.48%)
Mutual labels:  spreadsheet, google-sheets
radio
Redundant Array of Distributed Independent Objectstores in short RADIO performs synchronous mirroring, erasure coding across multiple object stores
Stars: ✭ 25 (-69.14%)
Mutual labels:  s3, s3-bucket
Luckysheet
Luckysheet is an online spreadsheet like excel that is powerful, simple to configure, and completely open source.
Stars: ✭ 9,772 (+11964.2%)
Mutual labels:  spreadsheet, google-sheets
Rumble
⛈️ Rumble 1.11.0 "Banyan Tree"🌳 for Apache Spark | Run queries on your large-scale, messy JSON-like data (JSON, text, CSV, Parquet, ROOT, AVRO, SVM...) | No install required (just a jar to download) | Declarative Machine Learning and more
Stars: ✭ 58 (-28.4%)
Mutual labels:  s3, json

logo google-sheet-s3

A 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.

For example, it turns a spreadsheet like this:

spreadsheet

Into an auto-updating JSON file like this:

JSON object

Get the add-on here on the Chrome Web Store.

Why?

Use case

"I want to display simple, structured, spreadsheet-like, publicly accessible data on a website (possibly with thousands of simultaneous visitors) that is easily updatable (possibly by multiple people at once) without the overhead and time of coding, deploying, and maintaining a full-blown web application."

Examples

Staff directory list, restaurant menu items listing, sports team standings page, live blog.

Why not [alternative]?

  • 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 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.)

Setup

We need to get an Amazon Web Services (AWS) S3 bucket and your Google Sheet to talk to each other, so there are two parts to this setup: one for AWS, and one for the spreadsheet.

AWS setup

  1. If you don't have one already, create an Amazon AWS account.
  2. If you don't have one already, create an AWS S3 bucket.
  3. If you don't have them already, generate AWS Security credentials that have write permissions to your S3 bucket.
  4. Add a bucket policy that enables public viewing of the published JSON. To enable, go to your S3 Management Console, click your bucket's name > Permissions tab > Bucket policy > enter your policy (sample to paste below) > click Save.
  5. If you're going to be accessing the published JSON data from a web browser, you will also need to add a CORS policy to your S3 bucket that allows GET requests from whatever origin (domain name) you want to access your data from. To add a policy, go to your S3 Management Console, click your bucket's name > Permissions tab > CORS configuration > enter your policy (sample to paste below) > click Save.

Sample bucket policy

This configuration is what I imagine most people using this add-on will want. It allows public access of the data stored in your Google Sheet, but not public write access.

Replace the text "PUT-YOUR-BUCKET-NAME-HERE" with your bucket's name.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AddPerm",
            "Effect": "Allow",
            "Principal": "*",
            "Action": "s3:GetObject",
            "Resource": "arn:aws:s3:::PUT-YOUR-BUCKET-NAME-HERE/*"
        }
    ]
}

Sample CORS policy

This configuration will allow any web page on the internet to access your sheet's data. You may not want that. You can modify the line <AllowedOrigin>*</AllowedOrigin> and replace the asterisk with your own hostname; e.g. the domain name from which you will be making Ajax requests for the sheet's JSON.

<?xml version="1.0" encoding="UTF-8"?>
<CORSConfiguration xmlns="http://s3.amazonaws.com/doc/2006-03-01/">
<CORSRule>
    <AllowedOrigin>*</AllowedOrigin>
    <AllowedMethod>GET</AllowedMethod>
    <MaxAgeSeconds>3000</MaxAgeSeconds>
    <AllowedHeader>Authorization</AllowedHeader>
</CORSRule>
</CORSConfiguration>

Google Sheet setup

How to use the add-on after completing the above AWS setup.

  1. Create or open an existing Google Sheet.
  2. Format the sheet so that the first row contains the column headers you want your JSON objects to have as properties. Example: Example
  3. Install and enable the add-on.
  4. In the spreadsheet's menu, go to Add-ons > Publish to S3 > Configure...
  5. Fill in the S3 bucket name, folder path within the bucket where you want data stored (leave blank for top level), and your AWS credentials that allow write access to the bucket.
  6. Click "Save". 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 safely renamed without breaking the URL.

Usage notes

  • 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 the null value in your consuming code.

Development setup instructions

  1. Create a new Google Apps Script with files whose names and content matches the ones in this repo (minus this readme).
  2. Add the Amazon S3 API Binding.
  3. In the menu bar, click Publish > Test as add-on...
  4. Select a version, for "Installation Config", choose "Installed and enabled", and select a document (must be a spreadsheet). Save.

Developement links for version published to Chrome Web Store

These links are not publicly accessible – please do not request access. They're only for my personal reference to develop the script. If you'd like to develop on this script yourself, follow the instructions above to set up a development environment.

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].