Experimenting with Google Apps Script

TLDR;

If you have ever heard of Google Apps Script, it has probably been in connection with some attempt at a mail merge, in which you programmatically send individual emails to a list of recipients. The list is in a Google Sheet. The emails are sent from your Google-hosted email account. If this isn’t familiar to you, but you’re interested, google “gmail mail merge”.

Last week, a friend of mine was interested in using Google Apps Script to streamline the process of printing Avery labels en masse for his food delivery company. I took a stab at it to get my feet wet – here’s what I got.


The Challenge

Here was the problem:

"Basically I need to do a mail merge for printing meal labels, Avery labels. I need to print out 40 labels that say chicken, 20 that say rice, 35 that say beans. The employees need a button they can just push. I’d do a macro in excel, but can’t with google docs."

Screen Shot 2015-05-16 at 1.13.40 PM The current process was to copyand paste the label names into one column of a Google Sheet. If you needed 50 labels for a Mediterranean entree, you had to copy and paste 50 rows of “Mediterranean” in the Label column.

Screen Shot 2015-05-16 at 1.21.08 PM

Once that was done, you used a pretty lengthy and complicated Avery-made online label editor

Altogether, you have more than 15 steps to this process. And, even though there’s quite a growing selection of add-ons , there’s none to help do thisbetter.

The Solution:

The objective of this little project– a process with as few steps as possible and as easy aspossible.

There is an easier mail merge by Avery on the Chrome add-on store (it’s free). As long as you have your labels in a spreadsheet already, it will format them for printing in one or two steps.

The main order of business is simplifying data entry into that spreadsheet. What we want employees to enter is the number of labels for a particular dish – and then rely on the sheet to fill itself out. That’s wherewe needed a Google Apps Script.

Steps:

  1. Went googling “script google sheet auto fill” and found this on stackoverflow. Triggers? Interesting. Let’s look into “onEdit()”
  2. On the Apps Script documentation website, Igot a better grip of onEdit() and the event object it sends to the callback function:
    https://developers.google.com/apps-script/guides/triggers/events
  3. Ok, so when the user edits the page, I want to: - Make sure they are editing a cell I care about (a new menu item or a new number of labels). That means I need to check that the edited field is in one of about 6 columns:
function onEdit(e) {  
   var column = e.range.getColumn(); 
   if (column >= 3 && column <= 9) { ...}
}
  • Then iterate through each of the columns with menu items (main, side, and drink)andcount out the number of labels for each. I went looking for examples, and favored these: in the documentation, about manipulating cell values, and on stackoverflow.
  • Then add an hour of debugging (with the Google Script editor’s particular brand of logging), and voila.Find the rest of the code here on Github.

Or, make a copy of the Google Sheet I created here and click on Tools > Script Editor.