I just spent a week prototyping with AngularJS and Google Sheets. It turned out to be a powerful and relatively simple way to get a working prototype up and running fast! I wanted to share what I learned to hopefully help other designers leverage these tools for quick prototyping.
My Project
As part of a project, we have been documenting a list of tools and their various attributes. I wanted to test what a web application for these tools might look like and how it might function in the real world. We wanted to answer questions such as, “how would people use it?” “How could we make this data more helpful?”
Keeping the mantra of minimum viable product (MVP) in mind, I quickly identified how a user might want to gain knowledge from the tools list. I sketched out a few screens. Once I was relatively happy with them, I started to develop a simple visual design pattern. I asked for feedback from potential users along the way and updated the designs accordingly. This helped reduce the project risk by gut-checking the hypotheses along the way.
We had initial designs for the application, but we wanted to take it further and create an interactive version to see if developing the tool would be worthwhile. I searched around online and came across a way to publish a Google Sheet in JSON. Further exploration uncovered a tutorial for using Google Sheets with AngularJS. I still didn’t understand everything from these tutorials, so I have detailed my learnings below.
JSON is a way to organize your data nicely into groupings of attributes and values for those attributes, which makes using it relatively intuitive. Sometimes, JSON files may be given to you without the formatting, which can be overwhelming. If that happens, you can run that data through a lint validator to add some structure.
To illustrate the core of what I’ve been working with, I created a Google Sheet with a list of tools and attributes.
Here is an example Google Sheet with a list of tools:
Now I’ll explain the rest.
Get a JSON Feed of the Google Sheet Data
1. File > Publish to the web…
2. Click Publish for the entire document or sheets you want available on the web.
3. Confirm choice.
4. Copy the URL link and paste the URL into a text editor.
5. Find the Sheet’s unique identifier in the URL:
https://docs.google.com/spreadsheets/d/1vFAgyxmwrKWn9Bss3vmk5GKqn1QEZVspVTg6eMINMcc/pubhtml
Unique Identifier: 1vFAgyxmwrKWn9Bss3vmk5GKqn1QEZVspVTg6eMINMcc
6. Copy/paste the unique identifier into unique-identifier in the code below:
https://spreadsheets.google.com/feeds/list/unique-identifier/1/public/values?alt=json
https://spreadsheets.google.com/feeds/list/1vFAgyxmwrKWn9Bss3vmk5GKqn1QEZVspVTg6eMINMcc/1/public/values?alt=json
7. Copy this new URL and paste it into a browser to confirm that you have the data. It should look something like this:
We now have access to the Google Sheet in JSON form. Let us dive into the data to get a better understanding of what is going on.
If you were to copy/paste the data that was output in the browser and then place it into the lint validator, you might look through it and see most of the information we want under the feed.entries objects.
If you look at each object under entries, you might also notice that each column in our Google Sheets is represented here prepended with gsx$
, with spaces removed and lowercased; i.e. Cell A1 “Tool Name” equates to gsx$toolname
.
Set Up Your HTML File
Now that you have access to the Google Sheets data in JSON and we can read the data, let’s get it on a page. We need to do the following to get AngularJS added to your HTML file:
1. Add the AngularJS code in the header of your HTML file:
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular.min.js"></script>
2. Add the code for AngularJS to pull in the JSON feed and set up the angular application. Place this code in the script tags you just created.
var app = angular.module('myApp', []);
app.controller('SuperCtrl', function($scope, $http) {
$http.get("https://spreadsheets.google.com/feeds/list/1vFAgyxmwrKWn9Bss3vmk5GKqn1QEZVspVTg6eMINMcc/1/public/values?alt=json")
.success(function(response) {$scope.tools = response.feed.entry;});
});
This sets up Angular to create an app, pull in the JSON feed, and set a variable of tools
to represent the data we are interested in. We’ll be storing the tool data in a variable tools
. The $.scope.tools
allows us to easily access this data by writing: {{ tools }}
in the HTML. This will output everything inside the tools
variable.
To be a little more helpful, we can access the first tool name directly via {{ tools[0].gsx$toolname.$t }}
, and similarly Attribute 1 of the first tool by {{ tool[0].gsx$attribute1.$t }}
. .$t
is the JSON object’s key/value pair. In this particular case, when you ask for the “.$t” it would return “x” (See cell B2).
3. Add ng-app="myApp" ng-controller=“SuperCtrl"
to the body tag. This essentially connects the JavaScript we wrote above to communicate with the HTML.
4. Create the HTML structure for displaying the data. In this case, I added an HTML table element.
5. Add ng-repeat="tool in tools”
to the HTML element you want Angular to output each of the object’s data values. ng-repeat
is a neat feature of Angular that allows you to to loop through each object in a set of objects. This is perfect for going through each tool, asking for the tool name and each attribute value. You can set tool
to be any name you want. Since my set of tools object was plural, I just made it singular. This way, it reads a little easier when I look at the code a while later.
That is it! If you save the index file and open it in a browser, you should see an HTML table with the data from the Google Sheets example. It’s a simple example, but hopefully it illustrates how to grab data from Google Sheets and use it on a webpage. This is a simple yet powerful way to start prototyping ideas.
Example Output of the HTML We Created
Example of the Working HTML We Created
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>Tools - An Angular and Google Sheet Example</title>
<meta name="generator" content="BBEdit 11.1" />
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular.min.js"></script>
</head>
<script type="text/javascript">
var app = angular.module('myApp', []);
app.controller('SuperCtrl', function($scope, $http) {
$http.get("https://spreadsheets.google.com/feeds/list/1vFAgyxmwrKWn9Bss3vmk5GKqn1QEZVspVTg6eMINMcc/1/public/values?alt=json")
.success(function(response) {$scope.tools = response.feed.entry;});
});
</script>
<body ng-app="myApp" ng-controller="SuperCtrl">
<table>
<thead>
<tr>
<th>Tool Name</th>
<th>Attribute 1</th>
<th>Attribute 2</th>
<th>Attribute 3</th>
<th>Attribute 4</th>
<th>Attribute 5</th>
</tr>
</thead>
<tbody>
<tr class="tool" ng-repeat="tool in tools">
<td><p>{{ tool.gsx$toolname.$t}}</p></td>
<td><p>{{ tool.gsx$attribute1.$t }}</p></td>
<td><p>{{ tool.gsx$attribute2.$t }}</p></td>
<td><p>{{ tool.gsx$attribute3.$t }}</p></td>
<td><p>{{ tool.gsx$attribute4.$t }}</p></td>
<td><p>{{ tool.gsx$attribute5.$t }}</p></td>
</tr>
</tbody>
</table>
<hr>
First Tool Name Value: {{ tools[0].gsx$toolname.$t }}
<br>
First Attribute Value: {{ tools[0].gsx$attribute1.$t }}
<br>
{{ tools }}
</body>
</html>
Helpful links:
http://jsonlint.com
http://www.w3schools.com/angular/
MVP = Most Viable Product?
Thanks Pieter. It has been corrected.
No worries – sorry, I did not want to be pedantic :)
Great job. Do you know if there a way to write to the Google Sheet using $http?
Hi Kenny,
Thanks for the comment. I haven’t looked into solving that problem yet. Let me know if you get you it first.
I am looking for creating google spread sheet using angularjs.
Hi Bryan,
great job explanation is very clear .
Thanks
Rambabu Bommisettti
Works perfect. thanks a lot for this post
How to write data to spread sheet?
How do include a filter?
Hai,
I need push data to spreadsheet through anuglar js.. how to push data to spreadsheet using angular js??
Great post, thank you – the question is.. is it ok to use this solution in a live project?
or it is ok only for prototyping..
Is this solution permanent, or there is a chance google will change the file structure/block access etc?
thanks,