Data-driven web applications are invaluable for all sorts of people and businesses. But updating or changing data in a relational or NoSQL database is not something most non-programmers can do. Wouldn’t it be nice if we could power simple applications using a data source that many non-technical people are comfortable with? Thankfully, there’s Google Sheets and the increasingly well-documented v4 of its API.

Unlike a JSON or SQL database, a Google Sheet can be shared, edited, consumed, calculated with, and commented on by nearly anyone with a web browser. This makes google spreadsheet data (consumed via an API) a great data source for simple and low traffic web apps. This article will show you the basics for GETting data from a google spreadsheet using version 4 of their API and consuming that data using a simple React app.

This is obviously not the first attempt at using a spreadsheet as a backend. However, many of the tutorials or existings solutions were outdated (as of mid 2018), proprietary or used Angular/jQuery to consume the data. This tutorial will simplify the process, use the API directly, leverage v4 of the API, and use React to consume/use the data.

First Setup the API

Here’s what I did to make some google spreadsheet data consumable via their API.

1. Put the following data into a fresh google spreadsheet:

id	name	lat	lng
1	Grandview Park	37.7564084	-122.4717396
2	Hawk Hill	37.8254957	-122.4995417
3	Grizzly Peak	37.8822385	-122.2336546
4	Bernal Heights	37.7431498	-122.4152257
5	Tank Hill	37.7599703	-122.4477785

2. Share the sheet so anyone can view it:

3. Use Google’s API Explorer Tools to construct a JSON request URL.

The API method we’re calling is batchGet. batchGet is used for returning “one or more ranges of values from a spreadsheet. The caller must specify the spreadsheet ID and one or more ranges.” It’s the primary method we use to GET data.

4. Fill out the form in the batchGet explorer:

Your spreadsheet id is the long string after ‘/spreadsheets/d/’ and before ‘/edit#gid=0’. Mine is 1mpFIiSlkqU0BQml5la3nEwkmj3QJUR8SvkaCHJmm6zk. Your ‘ranges’ is Sheet1 (unless you’ve changed it). Go with ROWS for the majorDimension. For the authentication, go with API key. Google will give you a fake one in this demo.

You should see the data now once you run it.

5. Go get a real API key

You’ll need an actual API key to get the data from your sheets. This can be done in the google console here.

6. Construct and try your real JSON API url.

Mine looks like this : https://sheets.googleapis.com/v4/spreadsheets/1mpFIiLlkqU0BQmn5pa3nEwkmy3QJUR8SvkaCHJmm6zk/values:batchGet?ranges=Sheet1&majorDimension=ROWS&key=AIzaSyBNTDOtaFrsIMaKsIJ_E3CxKaTwf0tCaW8

I try this in my browser and get JSON. It looks something like this:

That’s all cool and all. Actual JSON from a spreadsheet. But…you’ll notice this is odd data. values is an array with an array for each row. Hmmmm…this will be odd to work with. I’d prefer an array of objects. We shall do this next within our basic React app.

Creating a Basic List with our spreadsheet JSON

For this app, we’re just going to retrieve some data and display it in a list. In future articles, we will do more interesting stuff with this data.

Scaffold a basic React app

I use create-react-app to get the basic app setup quickly.

$ create-react-app sheetsdemo
$ cd sheetsdemo

We’ll do everything from directly within App.js, which create-react-app so graciously gives us. This is not supposed to be great architecture, rather just a display of the concepts.

1. Setup an empty array in this.state

constructor(){
    super();

    this.state = {
     items:[]
    };

  }

1. Fetch the JSON data from within componentDidMount

componentDidMount is the most commonly used React lifecycle method for fetching data.

I first assign my JSON url to a variable:

const API = 'https://sheets.googleapis.com/v4/spreadsheets/1mpFIiLlkqU0BQmn5pa3nEwkmy3QJUR8SvkaCHJmm6zk/values:batchGet?ranges=Sheet1&majorDimension=ROWS&key=AIzaSyBNTDOtaFrsIMaKsIJ_E3CxKaTwf0tCaW8'

Then, I use the componentDidMount lifestyle method to call the API using fetch.

  componentDidMount(){

     fetch(API).then(response => response.json()).then(data => {
      let batchRowValues = data.valueRanges[0].values;

      const rows = [];
      for (let i = 1; i < batchRowValues.length; i++) {
        let rowObject = {};
        for (let j = 0; j < batchRowValues[i].length; j++) {
          rowObject[batchRowValues[0][j]] = batchRowValues[i][j];
        }
        rows.push(rowObject);
      }

        this.setState({ items: rows });
        console.log(this.state.items);
    });

  }

You can see the fetch promise in use at the top. But then what are those two loops after that? With those two for loops, I’m able to convert my values array of individual row arrays into an array of objects for each row. My console now logs the array of objects.

[
  {
    "id": "1",
    "name": "Grandview Park",
    "lat": "37.7564084",
    "lng": "-122.4717396"
  },
  {
    "id": "2",
    "name": "Hawk Hill",
    "lat": "37.8254957",
    "lng": "-122.4995417"
  },
….

4. Loop/map over the array of objects to create a simple list.

JavaScript Array.prototype.map() is the best for this and very commonly used for repeating elements in React. I change my render method to the following to get a very simple list:

  render() {
    const listItems = this.state.items.map((item) =>
    <li>{item.name} at Latitute {item.lat} and Longitude {item.lng} </li>
  );

    return (
      <div>
         <ul>{listItems}</ul>
      </div>
    );
  }

And get the following result:

Now this app is definitely not anything super impressive on the display side. But stop for a second and admire that you’re serving up data from a google spreadsheet directly into a React app. If you change anything in the spreadsheet and refresh the React app, the changes are immediately propagated. It does not get much simpler for a data driven web app.

Upcoming tutorials will show how we can do much more interesting things with this data, like making a map.

Github repo of final code: https://github.com/kpennell/sheetsdemo