To try this tutorial as a coding challenge, make yourself a copy of this Spreadsheet. Open the Script Editor from the Tools menu in your copy of the spreadsheet and see the comments for further instructions.
This tutorial will pretend that Gooogle has just released a fun Apps Script service called AutoVet that has a single method that either spays or neuters a pet resource:
- AutoVet.operate(pet) - returns undefined if successful, otherwise throws an error;
A pet resource will look like this when it is loaded from a Sheet with the headers Name, Pet Type, Weight (lbs), Sex, and Status:
"Name": string,
"Pet Type": string,
"Weight (lbs)": number,
"Sex": string,
"Status": stringNote that the pet resource uses non-valid Javascript names as keys
The AutoVet service source code is shown below. It's obviously not real, but it has methods that actually pretend to perform tasks on pets, and that return a status on the completion of that task. If invalid inputs are provided, an error object gets thrown whose message property is meaningful. Google Apps Script is full of such services, such as GmailApp, that have methods that can be understood to operate in the same manner, and which are likely written in a similar style under the hood.
/**
* A service for spaying and neutering pets
* Copyright 2016 Gooogle, Inc.
* FIDO license V2.0
*/
//packaged as a module, where methods are defined underneath a single global variable.
//similar to a Google Apps Script library, but code is kept local to the project
//helps organize code into re-usable components and keep global namespace from getting cluttered
var AutoVet = (function() {
return {
operate: function(pet) {
if (pet['Sex'] === 'Male') {
//Messy operation performed here...
} else if (pet['Sex'] === 'Female') {
//Messy operation performed here...
} else {
throw new Error("Invalid Sex value");
}
return;
},
vaccinate: function(pet) {
//Stuff happens here.
return;
}
}
})();The Sheet contains the following data:
Our goal is to make it possible to run a function from a custom menu, Vet-o-Matic -> Run Procedure, on the Sheet.
From the Apps Script code editor attached to a Google Sheet, let's assume we have installed the GSheetsUtils library per the instructions offered here.
Selecting and running the neuterAndSpay() function via the debugger button, with the word "debugger" included in a given row, gives us the opportunity to see GSheetsUtils.getRowsData() in action.
You can explore the array of cats in the variable state inspection panel. Written in Javascript object-literal notation, they would look like this:
{@lang javascript}
//Array of objects as loaded from Sheet
[
{
"Name":"Garfield",
"Pet Type": "Cat",
"Weight (lbs)": 38,
"Sex": "Male",
"Status":""
},
{
"Name":"Sandwiches",
"Pet Type": "Cat",
"Weight (lbs)": 26,
"Sex": "Male",
"Status":""
},
{
"Name":"Toots",
"Pet Type": "Cat",
"Weight (lbs)": 15,
"Sex":"Female",
"Status":""
}
]Our goal at this point is to write a code block that will iterate through these objects and perform the deed on each of them. One caveat - we should only perform the operation if the row doesn't already have a value in it's status property. This will allow us to ensure we are only performing tasks that have not already been done.
{@lang javascript}
function neuterAndSpay() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Patients');
var pets = GSheetsUtils.getRowsData(sheet);
for (var i=0; i<pets.length; i++) {
var jobStatus = '';
if (pets[i]['Status'] === '') {
debugger;
//Do the deed
}
}
}What you see above is a for loop that uses an 1-incremented index variable "i" whose value is intialized to 0. The code inside the loop will execute for as many elements as there are in the array, starting with the first element (index zero), increasint the index by one for each iteration, and breaking as soon as the index is equal to the length of the array (in this case 3).
The status variable is assigned the value of the "Status" property in a given row, and the code inside the if block will only execute if status is equal to a blank string value.
Extending this to include some additonal logic, with the appropriate calls to the AutoVet service...
{@lang javascript}
function neuterAndSpay() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Patients');
var pets = GSheetsUtils.getRowsData(sheet);
for (var i=0; i<pets.length; i++) {
var jobStatus = '';
if (pets[i]['Status'] === '') {
try {
AutoVet.operate(pets[i]);
jobStatus = 'Successful operation on ' + new Date();
} catch(err) {
jobStatus = err.message + ' on ' + new Date();
}
//TODO: something to write the value of jobStatus back to the Sheet
}
}
}Here you can see the use of try...catch error handling pattern. Because there's some potential that invalid data could be passed to the AutoVet.operate() method, or that the AutoVet service could be experiencing rate-limiting or quota issues that are typical of Google's other cloud-based APIs, we need a way to ensure that errors don't halt execution, but instead are gracefully written to the Sheet. Here the try...catch pattern prevents the error from being "thrown" and instead allows us to set the value of its message to a jobStatus variable.
We're almost there. The last bit of work can be seen in the "TODO" comment above. To write status to the Sheet, we're going to want a method that is reasonably efficient, but that isn't dependent on column order. Here we're going to insert some logic before the loop to ascertain the column position of the "Status" header so we can use the SpreadsheetApp service's native .setValue() method on the correct row and column for each job, inside the loop.
{@lang javascript}
function neuterAndSpay() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Patients');
//New: get the current column number of the status column
var headers = GSheetsUtils.getUpsertHeaders(sheet);
var statusColNum = headers.indexOf('Status')+1;
if (statusColNum == 0) {
throw new Error('Ooops! Sheet is missing Status column')
}
var pets = GSheetsUtils.getRowsData(sheet);
for (var i=0; i<pets.length; i++) {
var jobStatus = '';
if (pets[i]['Status'] === '') {
try {
AutoVet.operate(pets[i]);
jobStatus = 'Successful operation on ' + new Date();
} catch(err) {
jobStatus = err.message + ' on ' + new Date();
}
//New: write the status to the correct row and column
sheet.getRange(i+2, statusColNum).setValue(jobStatus);
//New: inefficient, but ensures the write actually occurs inside the loop
//rather than after the loop completes.
//This serves as a "poor man's progress indicator" in the Sheet
SpreadsheetApp.flush()
}
}
}Running this function from either the script editor or the new fancy menu on our spreadsheet will result in this:
Note that the user has moved the columns into a different order than when we started, and yet the code still executes. This would not be possible if we had used the .getValues() approach or written the status to a hard-coded column number.
The two key design factors that make this possible:
- GSheetsUtils.getRowsData() provides row data in a name-addressed fashion (rather than position-addressed) -- as key-value pairs, and our code references all values by key.
- By dynamically determining the column number of the status column, its position can be changed without impacting execution.
Now imagine replacing AutoVet with something useful, like a call to a function that creates a merged document, a Drive folder, or a change to a domain resource. Using the patterns above, all of this is much closer to being within reach!