How to get a Google Sheet as JSON

google_sheet-to-json.md

How to get a Google Sheet as JSON

Preparing Your Google Sheet

1. The first row of your spreadsheet should be headers, and the rest is data under those headers (see example).
2. Share the spreadsheet so anyone can see it (“Share” button in top right corner > “Anyone on the internet with this link can view”).

Preparing you url access

fetch('https://sheets.googleapis.com/v4/spreadsheets/' +
           spreadsheet_id + '/values/' + tab_name +
           '?alt=json&key=' + api_key')
.then(data => {
return data.json();
})
.then(data => {
console.log(data);
});

spreadsheet_id is the long string of letters and numbers in the address of the spreadsheet — it is the bit between /d/ and /edit

tab_name is the name of the sheet, i.e., the name you see in the tab bar at the bottom of the window when you have the spreadsheet open for editing The secret: don't use a number for this name

api_key is the API key you get from Google Cloud Platform Console

Example finally url: https://sheets.googleapis.com/v4/spreadsheets/1ojGUHocs09bOFm4lEjQG83B6U_IM302uD6CF9njm6to/values/data?alt=json&key=AIzaSyBDxkD7N8HSz6SxjFmb0y8ODmrwLXk4lgI

Best alternative

Opensheet