Using Google Sheets as a Database in 3 easy steps

Databases are very expensive, they cost a lot and for a small project or business — this is money better used in other areas. This is where Google Sheets comes in.

Google Sheets is a very useful way of capturing data. It allows you to collaborate with others seamlessly and exhibit data to other instantly. The problem with Google Sheets is that when you want to take this data and populate a website or system, there is a massive chore of exporting the data, putting it inside a database or giving it to a developer to get the data updated. There is no quick way of Google Sheets just offering an API to your data.

How to get Google Sheets data as JSON

1. Create a Google Sheet

For this example, we will be using Tunbridge Wells Open Data - Polling Districts data from data.gov.uk.

Tunbridge Wells Open Data - Polling Districts - data.gov.uk

https://data.gov.uk/dataset/980180e9-4c39-487f-afa9-5ef841200f23/tunbridge-wells-open-data-polling-districts

We will then import this data into Google Sheets.

https://docs.google.com/spreadsheets/d/e/2PACX-1vQ8oXOIqewtlNTyJvplT-QYSlX9UoB8XlV0gSTYBFHxtlF3HwdkVp-vJP7FIVgHhTheL8nKYxcaNu2t/pubhtml

2. Publish the spreadsheet

The next step is to publish the spreadsheet. We need this to be published as the data needs to be accessible via the internet for the service we will be using in the next step.

To publish to the format that we need, do the following steps:
1. Go to File > Publish to the web…
2. Select the correct sheet
3. Select CSV as the export
4. Click Publish

How to publish on Google Sheets animation

CSV

3. Convert CSV to JSON

Now that we have the CSV data published, we need to convert the CSV into something that websites/systems can read easily. For this example, we have chosen JSON. We will be using a service that has previously been created called CSV-to-JSON.

DilwoarH/csv-to-json-api

We will get the URL for the CSV from the publish screen on Google Sheets.

Build URL:

http://csv-to-json.herokuapp.com/?csv=http://csv-to-json.herokuapp.com/?csv=https://docs.google.com/spreadsheets/d/e/2PACX-1vQ8oXOIqewtlNTyJvplT-QYSlX9UoB8XlV0gSTYBFHxtlF3HwdkVp-vJP7FIVgHhTheL8nKYxcaNu2t/pub?output=csv

Note:
Sometimes you may get URLs like the one below which has additional parameters such as a gid and single — the CSV to JSON API will fail so just remove these (this is a limitation of the service).

From:

https://docs.google.com/spreadsheets/d/e/2PACX-1vQ8oXOIqewtlNTyJvplT-QYSlX9UoB8XlV0gSTYBFHxtlF3HwdkVp-vJP7FIVgHhTheL8nKYxcaNu2t/pub?gid=888351968&single=true&output=csv

To:

https://docs.google.com/spreadsheets/d/e/2PACX-1vQ8oXOIqewtlNTyJvplT-QYSlX9UoB8XlV0gSTYBFHxtlF3HwdkVp-vJP7FIVgHhTheL8nKYxcaNu2t/pub?output=csv

Upon calling this API, you should get a response like this:

http://csv-to-json.herokuapp.com/?csv=https://docs.google.com/spreadsheets/d/e/2PACX-1vQ8oXOIqewtlNTyJvplT-QYSlX9UoB8XlV0gSTYBFHxtlF3HwdkVp-vJP7FIVgHhTheL8nKYxcaNu2t/pub?output=csv

That it! You’re done.

Examples of using Google Sheets as a Database

News Sheet

URL: https://news-sheet.netlify.com/

Github: https://github.com/DilwoarH/news-articles

JSON: https://csv-to-json.herokuapp.com/?x&csv=https://docs.google.com/spreadsheets/d/e/2PACX-1vQIe7t8zVGc7SaAdex3mD9CnzzwvHj64wF3_x8zTcNHOZBFQCv7yYt-LzQmE24ymjDQZ7b_DpLN0jdx/pub?output=csv

Google Sheets: https://docs.google.com/spreadsheets/d/e/2PACX-1vQIe7t8zVGc7SaAdex3mD9CnzzwvHj64wF3_x8zTcNHOZBFQCv7yYt-LzQmE24ymjDQZ7b_DpLN0jdx/pubhtml

https://news-sheet.netlify.com/

https://news-sheet.netlify.com/article.html?id=1