Using Google Sheets as a Source of Dynamic Data for your Static Website

Static websites are fantastic. You can host them for free, and they are a great segue into full-stack web development. Though when you’re building a static website, the absence of a DB is a hard constraint. What this means is that any information that you display on your site can have two sources:

1. You can embed it in the website’s code itself
2. You can host it somewhere else and fetch it from a URL

A common roadblock in this approach is data that is frequently updated. For example — a list of projects on your portfolio; or a list of members for an organization’s website. Typically, we embed this data in the website itself, and each update requires a re-deployment. Ideally, we want an easy-to-edit data source that a static website could fetch.

There is one. Google Sheets. And it’s trivially easy to get started.

Create an API Key

You’ll need to provide a way for your static website to get data from a Google Sheet. We’ll do this through a Google Cloud project. As a first step, create an API key with access to Google Sheets.

Fetching Data

Google Sheets throws the contents of a worksheet within a sheet as JSON at this URL:

https://sheets.googleapis.com/v4/spreadsheets/<sheet_id>/values/<worksheet_name>/?key=<api_key>

• sheet_id : You can extract this from the URL of your Google Sheet.

https://docs.google.com/spreadsheets/d/<sheet_id>/edit#gid=0

• worksheet_name : It’s the name of the worksheet inside your Google Sheet that you want to fetch. Note that this method can only fetch one worksheet from a Google Sheet at a time. The default is Sheet1
• api_key : We created this in the previous step. Extract it from the Google Cloud project.

You can use this snippet :

const sheetLink = "https://sheets.googleapis.com/v4/spreadsheets/<sheet_id>/values/<worksheet_name>/?key=<api_key>"
const data = await  fetch(sheetLink)
.then((response)=> {return response.json()});

A JSON like the given sample is returned when you fetch a sheet. You can iterate over the values field to populate data on your website.

{
  "range": "Sheet1!A1:Y999",
  "majorDimension": "ROWS",
  "values": [
          [
           "value in row1, col1",
           "value in row1,col2"
          ],
          [
           "value in row2, col1"
          ]
  ]
}

And that’s it! Any changes you make to the sheet will reflect instantly on your website. Now you can add/edit content on your static website from any device that can access Google Sheets — including your phone.