Web App Development with Google Apps Script/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]))