Things I learned from creating a Google apps script

Recently, I had to create a Google Apps script, and I learned a few nuances along the way. If you’re new to creating Google Apps scripts, you may want to keep these in mind.

You can select spreadsheets by ID but have to select sheets by name.

I’d love to be corrected on this, but I couldn’t find a way to select by ID, and this StackOverflow question and its answers back up that it can’t be done without a workaround.

So if you want to get all of a spreadsheet’s data into a multidimensional array, here’s how you do it:

var ss = SpreadsheetApp.openById(‘THEIDOFYOURGOOGLEAPPSSPREADSHEET‘);
var ss_sheet = ss.getSheetByName(“THEACTUALNAMEOFYOURSHEET“);
var range = ss_sheet.getDataRange();
var values = range.getValues();

Replace THEIDOFYOURGOOGLEAPPSSPREADSHEET with the actual ID of your spreadsheet, which is the gobbledygook after https://docs.google.com/spreadsheets/d/ and before edit#gid=0.

Replace THEACTUALNAMEOFYOURSHEET with the actual name of the sheet you want to get data from. Of course, that means if someone renames the sheet within your spreadsheet, your script will break.

The getDataRange() function just gets the range of filled cells on the sheet. You can use other functions to get a more specific subrange. More details in Google’s API documentation.

Writing back sheet ranges starts with 1 and not 0

So you have your data in an array called values and can reference, for example, cell B3 as

values[2][1]

with the 2 representing row 3 and the 1 representing column B, because JavaScript arrays by default will start numbering at 0.

If, however, you want to write values back to the spreadsheet, Google Apps’s API will expect numbering to start from 1 instead of 0, so you’d write something like this:

ss_sheet.getRange(j+1, k+1).setValue(“This cell was updated at ” + Date());

so if J was 2 and k was 1, then now you’re writing back to 3 and 2 respectively (or cell B3).

There is a maximum execution time that cannot be exceeded

According to Google’s documentation, there is a maximum script execution time of 6 minutes. If you exceed it, your script will just error out and say the maximum execution time has been exceeded.

I’ve done a bit of research on this, and the only “fix” I found for this was to preemptively end the script—something like this:

// Prevent maximum execution time problem
var startTime = (new Date()).getTime();

// Start your loop
for ( j = 0; j < values.length; j++){ // Run your loop stuff
var k = 1;

// Put this at the end of the loop
// Prevent maximum execution timeout
var currTime = (new Date()).getTime();
if( currTime – startTime > 270000 ){
ss.toast(“Getting close to maximum execution time. Ending with ” + values[j][k], “Warning”, 2);
break;

// End your loop

}

270000 is 4.5 minutes, because the getTime() function outputs Epoch time in milliseconds. Even though the max is 6 minutes, I like to put in a little bit of buffer, so the script isn’t part way through the loop and then cut off.

The toast() function allows you to display a little pop-up message in the lower-right corner of the Google doc/spreadsheet.

You can copy an existing Google Drive doc as a template

Well, of course you can do it, but it can be hard to track down simple code for this that works. I had to do a bit of GitHub searching, tweaking, and then reconciling with the actual up-to-date APIs. Here’s a working (as of 2016) example:

// Make a copy and get the ID of the new document
var docid = DriveApp.getFileById(‘IDOFYOURGOOGLEDOCTEMPLATE‘).makeCopy(‘NAMEOFTHENEWDOCYOURECREATING‘).getId();

// Open the new document
var doc = DocumentApp.openById(docid);

// Get the body of the document in focus
var body = doc.getActiveSection();

// Replace text in the document with whatever custom text (this is where your values[j][k] would come in handy)
body.replaceText(“TEXTINTHEDOCYOURWANTTOREPLACE“, values[j][k]);

// Add an editor to the document besides you
doc.addEditor(‘EMAILOFUSERYOUWANTTOADDASEDITOR‘);

// Save the doc and close it
doc.saveAndClose();

You can send an email using Gmail

Again, pretty obvious you can do it, but here’s a simple template:

// The address of the recipient
var emailRecipient = values[j][k];

// The actual body text of the email, in which you can use HTML
var emailText = “Here is the text of the email you would write with something in bold or italics, for example.”;

// The email subject
var emailSubject = “The actual subject of the email you want to send”;

// Send the email
GmailApp.sendEmail(emailRecipient, emailSubject, emailText, {htmlBody: emailText, name: “HOWYOUWANTYOURSENDERNAMETOAPPEAR“});

The email will come from your actual email address, but if you want a name to appear (your office’s name, for example), you can sub that in for HOWYOUWANTYOURSENDERNAMETOAPPEAR.