Get your team started on a custom learning journey today!
Our Boulder, CO-based learning experts are ready to help!
Get your team started on a custom learning journey today!
Our Boulder, CO-based learning experts are ready to help!
Follow us on LinkedIn for our latest data and tips!
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.
Here’s what I did to make some google spreadsheet data consumable via their API.
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 |
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.
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.
You’ll need an actual API key to get the data from your sheets. This can be done in the google console here.
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.
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.
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.
constructor(){ super(); this.state = { items:[] }; }
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" }, ….
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
Customized Technical Learning Solutions to Help Attract and Retain Talented Developers
Let DI help you design solutions to onboard, upskill or reskill your software development organization. Fully customized. 100% guaranteed.
DevelopIntelligence leads technical and software development learning programs for Fortune 500 companies. We provide learning solutions for hundreds of thousands of engineers for over 250 global brands.
“I appreciated the instructor’s technique of writing live code examples rather than using fixed slide decks to present the material.”
VMwareThank you for everyone who joined us this past year to hear about our proven methods of attracting and retaining tech talent.
© 2013 - 2020 DevelopIntelligence LLC - Privacy Policy
Let's review your current tech training programs and we'll help you baseline your success against some of our big industry partners. In this 30-minute meeting, we'll share our data/insights on what's working and what's not.
Training Journal sat down with our CEO for his thoughts on what’s working, and what’s not working.