Web App Development with Google Apps Script/Printable version

Web App Development with Google Apps Script

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/Web_App_Development_with_Google_Apps_Script

Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License.

Why GAS

Javascript everywhere

Using Google Apps Script is nice because it's javascript on both the front and backend. Normally we expect javascript and html on the front end (meaning how our users will interact with our tool) but often you have to learn and use a different language on the backend, where you interact with your data and figure out what you need to show your users. Backend software examples include PHP, Ruby, and interesting flavors of javascript like node.js. With Google Apps Script you do the same sort of programming everywhere.

Web editing

With Google Apps Script can do all your editing in a browser. Your scripts are either tied to a spreadsheet or they're stand alone scripts in your Google Drive. Either way you launch the editor through drive and you get a decent, if not fantastic, development environment that you can use on a Chromebook.

Most other web development approaches encourage you to code locally on your own machine and then push your code to the server that hosts the web page. Many people use GitHub to help facilitate the pushing, but getting a nice coding environment on your local computer can be a hassle so it's nice with Google Apps Script that you can just grab any web-connected computer to do your work.

Spreadsheets as database

What I'll be talking about mostly in this book is using simple Google Sheets to store all your data. I've done a lot of web app development using a mysql-type backend database, and I really appreciate the speed and utilities those provide. But they're a pain to set up and they're a pain to interact with separately from your web application. Google Sheets, on the other hand, are quite easy to set up and have a great user interface already for you to go look at and mess with your data.

User authentication

Assuming you're in a situation where you're programming in a Google environment (like a Google school like mine where all emails are just rebranded gmail accounts) there's a very simple command to determine the user's email address and from there you can decide what you want them to have access to.

Hello world

Create a new script

You can create a new Google Apps Script the same way you'd create a new Google Doc or Google Sheet: Using the "New" button scroll down to "Google Apps Script" under "more". That will open a new script window with a simple test function in it:

function myFunction() {

}

If you write some code in that function you can run the function with the buttons at the top. Typically to see any results you'd have to use Logger.log(some variable here).

If you put javascript code outside of a function it will also execute whenever you run any of the functions. Keep that in mind as we develop web apps. Anything outside of a function will run every time the page is accessed or you run a server function with an ajax call.

Set up doGet()

To actually have a web app you have to do two things:

1. Make sure you have a doGet() function in your script, and
2. Publish your site

Almost always you're going to want to write your html in a separate file (still housed in the overall script google entity) and then have the doGet() function call it and return it. Assuming you've created an html file called "main.html" you would call it like this:

function doGet(){
   var t=HtmlService.createTemplateFromFile("main"); // note that you don't have to put the ".html"
   return t.evaluate();
   }

You would then access the page by going under to the "Publish->Deploy As Web App..." menu item. Once it's published you can access the url from that same menu item.

Templated html

Putting variables into html pages

As noted in the Hello World chapter, you can have a basis of an html page by creating one and using it as a template. Normally you'd want to tailor that page with information from the server (ie usually one of your Google Sheets). You can use the various templating tools to do that.

You send a variable to the template with t.coolvariable="whatever" and you put it in the html document with <?= coolvariable ?>.

doGet code

from "code.gs":

function doGet() {
   var t = HtmlService.createTemplateFromFile("main");
   t.coolvariable = "hi there";
   return t.evaluate();
   }

html code

From "main.html":

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <?= coolvariable ?>
  </body>
</html>

Web result

hi there

More complex variables

If you want to send something more complex than a string, there are a few options:

• <?!= somevariable ?> will print things with special characters in it. This is useful, for example, for strings that contain html tags in them.
• var pagevariable = <?!= JSON.stringify(somevariable) ?>; inside a <script> tag on the html page will then let the local javascript deal with it as a global variable.
• If you have a bunch of variables that you want to have as global variables in the html document:
  • t.globals={"var1":var1, "var2":var2}; in the code.gs doc

  • var globals = <?!= JSON.stringify(globals) ?>;
    Object.keys(globals).forEach(key=>window[key]=globals[key]);
    

  • this works because the window object is the global so-called "namespace" for the html document. That just means it's where global variables sit.

Passing functions

The Google Apps Script editor doesn't work very well when checking for syntax errors in javascript code that's embedded in an html file. Here's a trick to let you write javascript functions in the server code spaces (which lets them be syntax checked) and then pass them to the client. The trick is to use function expressions.

In the server side you'd do this:

function doGet() {
  var t=HtmlService.createTemplateFromFile("main");
  t.funcs=[a,b,temp];
  t.funcnames=t.funcs.map(f=>f.name);
  return t.evaluate();
}

var a=(c,d)=>c+d;
var b=(e,f)=>e*f;
var temp=(a,b,c)=>{
                    var d=a+b;
                    var e=b*c;
                    return d+e;
                   }

and in the client html file you'd do this:

var funcnames=<?!= JSON.stringify(funcnames) ?>;
var funcs=[<?!= funcs ?>];
funcnames.forEach((fn,i)=>window[fn]=funcs[i]);

Data from spreadsheets

Structure overview

Google Apps Script lets you have access to Google Sheets. You do it by making a connection first to the Google Drive Sheet. There are two ways to do this:

• ss = SpreadsheetApp.openById(id) where "id" is the typical google drive id (the long string of characters usually right after "https://docs.google.com/spreadsheets/d/" in the url of a sheet).
• ss = SpreadsheetApp.getActive() if your script is tied to a Spreadsheet (see below)

Once you have the spreadsheet variable (ss in those examples), you can access the various sheets (some people call them tabs) in that doc and any data inside them.

Script tied to spreadsheet

If you want you can tie your script to a spreadsheet. Then the script doesn't live as a stand-alone google drive file but rather is stored with the Google Sheet. You can access the script by going to Tools->Script editor. It'll then look just like any other script. Oddly it still needs a name, but it'll prompt you to name it when you try to run something.

If you have it connected to a spreadsheet you definitely want to use the second approach mentioned above. Interestingly, the first method always works. There are some subtleties to access control when you tie it to a spreadsheet. Personally I do this a lot because if I want to edit the script I usually want access to the data at the same time. This way they're all stored together.

Read the data from a spreadsheet

If you have the spreadsheet variable, let's say you wanted to get all the data in a sheet called "Sheet1", which is the default name for the first sheet in a brand new Google Sheet document. You'd do that like this:

var sheet = ss.getSheetByName("Sheet1");
var data = sheet.getDataRange().getValues();
// then do something with the data

This usually takes a second or two, even for large (500+ row) spreadsheets. Once you have the data you can do all sorts of things. Let's say you have a spreadsheet that looks like this:

Name	email	age
Andy	andy@myschool.edu	48
Ash	ash@myschool.edu	20
Char	char@myschool.edu	18

Assuming it's in "Sheet1", you could augment the above code to create an html list to be displayed on the main.html page like this:

data.shift(); // this gets rid of the top row (the one with the headers in it)
var html = "<ul>";
html += data.map(r=>`<li>${r[0]} (${r[1]}) is ${r[2]} years old`).join('');
html += "</ul>";
t.html=html

Responding to users

JavaScript for user interfacing

This book is all about leveraging your google account to make data-driven web applications. It will not cover how to create your user interfaces very much. In this section I'll just give some simple examples, some of which don't even follow best practices but are quick and dirty approaches you can try for some proof-of-principle approaches.

User clicked something

Nearly any element in html can have an onClick function added to it. This includes:

• buttons
• text (most easily done with span tags)
• List items

onClick is not usually considered the proper way to do things, but if you're sure it's the only thing you want that element to do when the user is interacting it can work for you.

Here's some simple html/javascript code that would open an alert saying "you clicked me" when the user presses a button:

<button type="button" onClick="openAlert('press me')">press me</button> <!-- note the use of both types of quotes -->

<script>
function openAlert(text){
   alert(text);
   }
</script>

Update some text on the page

Alerts are annoying, don't use them. Instead have the user's actions change something on the page. Here's an update to the last example where now pressing the button puts some text on the page just below the button:

<button type="button" onClick="openAlert('press me')">press me</button> <!-- note the use of both types of quotes -->
<div id='emptyatfirst'></div>

<script>
function openAlert(text){
   document.getElementById('emptyatfirst').innerHTML=text;
   }
</script>

If you plan to make lots of changes to the text inside that div, you might instead want to make a global variable like this:

<button type="button" onClick="openAlert('press me')">press me</button> <!-- note the use of both types of quotes -->
<div id='emptyatfirst'></div>

<script>
// here's a global variable:
var emptyatfirst=document.getElementById('emptyatfirst');
function openAlert(text){
   emptyatfirst.innerHTML=text; // this uses the global variable
   }
</script>

Data to spreadsheets

Basic gist

If you collect some data from a user (maybe from an input tag or something) that you want to save to your spreadsheet, you have to send it to the server-side, even though you're using the client-side for all the user interface.

The steps are:

1. Grab data from the html element that the user adjusts
2. Possibly massage the data a little (trim any trailing or leading spaces, for example)
3. use the magic sauce: google.script.run!
4. Deal with any response from the server

Get data from input

Here's a simply way to get the data from a textarea element when the user hits "send":

<textarea id="mytextarea"></textarea>
<button type="button" onClick="grabtext()">send</button>

<script>
function grabtext(){
   var text = document.getElementById('mytextarea').value; // note you don't use innerHTML
   // here's where'd you do something with that text
   }

</script>

Sending to the server

Once you have some information to send to the server, you have to somehow get it there. That's where the magical google.script.run comes in!

Basically you can send some data to the server by calling a function **on the server** that can do stuff there and then return some response.

Here's an example that builds on the one from above. We grab whatever the user enters and we add it to a sheet on the (attached) spreadsheet:

<textarea id="mytextarea"></textarea>
<button type="button" onClick="grabtext()">send</button>

<script>
function grabtext(){
   var text = document.getElementById('mytextarea').value; // note you don't use innerHTML
   google.script.run.addRowToSheet(text);
   }

</script>

Meanwhile on the server (so in code.gs instead of javascript added to main.html):

function addRowToSheet(s) { // you can call what's passed anything you want. You don't have to call it "text"
   var ss = SpreadsheetApp.getActive(); // grabs the associated spreadsheet
   var sheet = ss.getSheetByName("Sheet1");
   sheet.appendRow([s]); // appendRow needs an array like ["first column info", "second column info", ...] 
   }

Hearing back from the server

Often you'll want to hear something back from the server. That's what withSuccessHandler is all about. Basically you tell javascript both the server function you want to run **and** the client-side function that should run when something comes back. The syntax is weird, but it works.

Here's an example that augments the last one letting the user know the data has been saved in the spreadsheet:

<textarea id="mytextarea"></textarea>
<button type="button" onClick="grabtext()">send</button>
<div id="callthiswhateveryouwant"></div>

<script>
function grabtext(){
   var text = document.getElementById('mytextarea').value; // note you don't use innerHTML
   google.script.run.withSuccessHandler(dealWithIt).addRowToSheet(text);
   }
function dealWithIt(returnvalue){ // you can call the return value whatever you want. Often you'll see people calling it "e"
   document.getElementById('callthiswhateveryouwant').innerHTML="From the server: "+returnvalue;
   }

</script>

Meanwhile on the server (so in code.gs instead of javascript added to main.html):

function addRowToSheet(s) { // you can call what's passed anything you want. You don't have to call it "text"
   var ss = SpreadsheetApp.getActive(); // grabs the associated spreadsheet
   var sheet = ss.getSheetByName("Sheet1");
   sheet.appendRow([s]); // appendRow needs an array like ["first column info", "second column info", ...] 
   return "holy cow it worked!";
   }

authentication

Why authenticate?

Quite often you'll want to protect your web app to ensure only certain people can use it. When you publish your page you have a few options in two major categories:

1. Who has access (who can get to the page)?
   1. Just you
   2. Anyone in your domain (hamline.edu for me)
   3. Anyone
2. Which account is being used to run the script?
   1. Yours (even when other people access)
   2. Theirs (won't work with "anyone" above)

Those are useful but they're a little coarse grained. Sometimes you'll want only certain people to either have access and/or be able to do certain things.

Who is accessing?

For this section we'll assume you've set "who has access" to "anyone in your domain." If you do then you can determine who is accessing like this:

var email = Session.getActiveUser().getEmail();

Are they allowed?

Once you have the user's email, you can check it against a sheet in your spreadsheet that has allowed users and perhaps other information about them, like what things they're allowed to do. Let's assume you have a sheet that looks like this:

"my users" sheet

email	name	role
arundquist@hamline.edu	Andy Rundquist	admin
test1@hamline.edu	Testy McTesterson	student
test2@hamline.edu	Tess Tesserson	faculty

Then we could authenticate the user like this:

var email = Session.getActiveUser().getEmail();
var usersData=SpreadsheetApp.getActive().getSheetByName("my users").getDataRange().getValues();
usersData.shift() // gets rid of headers row
var user = usersData.find(r=>r[0]==email);
if (!user) {
   return HtmlService.createHtmlOutput("sorry, nothing for you here");
   }
// now do something cool with the user
var role=user[2]; // not 3, remember how array numbering works
if (role == 'admin') {
   // do cool things here
   }
// etc

syntax

ajax

Ajax stands for "Asynchronous JavaScript and XML" and it's the ability for a web page to communicate with a server without reloading the page.

To do it in Google Apps Script you use the google.script.run tool. This command, which has several varieties, runs JavaScript code on Google's servers and potentially returns information back to your web page.

For example, if you wanted to send a name to a server, then check if it's in your spreadsheet and return true if it is and false if it's not that you can set to a variable, you'd do this:

First the server side code:

function doGet(){
   var t=HtmlService.createFileFromTemplate("main");
   return t.evaluate();
   }
   
function runOnServer(name){
   return checkInSpreadsheet(name); // this would be a function you'd write to check the spreadsheet
   }

Now the portion of "main.html" with the JavaScript you'd be doing

google.script.withSuccessHandler(dealWithReturn).runOnServer("Andy");

function dealWithReturn(e){
  if(e) {
     alert("yep that name's in there");
     } else {
        alert("nope, that name's not in there");
   }

The flow is: main javascript -> function on server -> returns data to your SuccessHandler -> your successHandler does something.

Array numbering

A simple (and quite forgivable) mistake is to be off by a row or column when moving data back and forth between google sheets and javascript is the array numbering issuer. Javascript starts numbering at zero while google sheets starts numbering at 1. Here's an example:

var coolArray=[1,2,3,5,7,11]; // why not 9? because it's not prime!
var myCoolSheet = SpreadsheetApp.getActive().getSheetByName("my cool sheet");
// this is a dumb way to put data into a sheet but it points out the numbering difference
coolArray.forEach((item,i)=>{
                              // myCoolSheet.getRange(i,1).setValue(item); this fails
                              myCoolSheet.getRange(i+1,1).setValue(item); // this works
                             }) // don't forget to close everything

filter

If you need to filter an array, use, um, filter:

var people = [{name: "Andy", age: 48}, {name: "Ash", age: 20}, {name: "Char", age: 18}];
// get people under 30:
var youngPeople = people.filter(person=>person["age"]<30) // note that "person" didn't need to be initialized

As with find, forEach, and map you can also use the index if you want:

var people = [{name: "Andy", age: 48}, {name: "Ash", age: 20}, {name: "Char", age: 18}];
// get people under 30 but not those with an index less than 2
var youngPeople = people.filter((person,i)=>person["age"]<30&&i>=2) // note that "person" didn't need to be initialized

find

If you need to find an element in an array, find is super helpful:

var array=[{name: "Andy", age: 48}, {name: "Ash", age: 20}]; // an array of objects
var ashObject = array.find(person=>person["name"]=="Ash"); // returns the first element that gives "true" for the check

forEach

If you need to loop through an array and do something with every element, you could of course do this:

var array=["apple", "orange", "banana"];
for (var i=0; i<array.length; i++) {
   doSomethingCool(array[i]);
   }

But you could also do this, which I think reads better:

var array=["apple", "orange", "banana"];
array.forEach(fruit=>doSomethingCool(fruit));

Note that you never have to initialize the "i" variable this way.

If you need to access the item (fruit) and the location (index) that it's in the array:

var array=["apple", "orange", "banana"];
array.forEach((fruit,i)=>doSomethingElseCool(fruit,i));

Note the parentheses around fruit, i that makes that magic work. Also note that you still don't have to declare the i variable!

includes

If you're looking to see if an array includes a particular element, try includes:

var myArray=["apple", "orange", "banana"];
if(myArray.includes("green beans")) {
   // do something to say that green beans isn't in the array
   }

map

Similar to forEach, map is a great way to create a new array based on an existing array. Let's say you wanted to add a tag around some text in an array:

var array=["apple", "orange", "banana"];
var newarray = array.map(fruit=>`<b>${fruit}</b>`)

You can also have easy access to the index of the item in the array:

var array=["apple", "orange", "banana"];
var newarray = array.map((fruit,i)=>`${fruit} is at index ${i} in the array`)

sort

Sorting arrays is quite valuable and sometimes a pain. Assume you have a 2D array from a spreadsheet with columns of "name", "age", "email". If you want to sort them by age, do this:

var data=... // data from spreadsheet
data.sort((a,b)=>a[1]-b[1]);

really sort needs to return a true or false when determining who should go first, but by subtracting two numbers, if you get a zero or a negative number it'll be treated as false whereas a positive number will be treated as true.

If you wanted to sort by the age and the name (alphabetically) you could:

var data=... // data from spreadsheet
data.sort((a,b)=>(a[1]>b[1])&&(a[0] < b[0]))

exercises

templates

Templates

• Google Apps Script

  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("test");
  var data = sheet.getDataRange().getValues();

var x=100;