Tuesday, 27 October 2020

Search spreadsheet for multiple criteria

The following Google Apps Script has been extracted from a tool I am developing to manage a Google Group via a Google Sheet. The purpose of this particular script is to enable searching through rows of spreadsheet data and simultaneously looking for matches against 2 pieces of criteria:

  1. That the user running the script (their email address) is listed in the sheet (Email address column) and has the role (Role column) of either 'manager' or 'owner'.
  2. That there is at least 1 'owner' listed somewhere in the Role column.

Now what we want to avoid is looping through all of the data twice just to look for our 2 pieces of criteria. We also want to avoid unnecessarily scanning all of the rows once our criteria is met. So this script shows a way of doing that by using true/false flags to determine how the single loop should behave.

Rows of email address and role in spreadsheet
Screenshot of spreadsheet data to search

Tuesday, 6 October 2020

Generate Apps Script code for Forms

The following Google Apps Script is designed to automate the generation of further Apps Script code typically used when capturing Google Form submission data (create a Google Doc from a Form post). I tend to find there are 2 lines of code per Form question that I have to write which when scaled up to 60+ questions is a lot of time and chance for human error. They are:

  1. var data = formValues['question title'][0];
  2. Logger.log('data is: ' + data);

So this code is designed to gather all of the column headings from the response sheet attached to the Form, then loop through each one in turn and construct the necessary syntax to automatically produce the 2 above lines for each question into a Google Doc:

Auto generate Apps Script code lines
Screenshot of auto-generated code