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.

Screenshot of spreadsheet data to search

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:

Screenshot of auto-generated code

Bulk export Qualtrics survey results

The following tool is designed to connect to your Qualtrics account and export the results data for multiple surveys that you specify, in one go. The results will be appended to the Google Sheet using the survey-name to identify each sheet of data. The data itself is exported as CSV.

Screenshot of instructional sheet requesting Qualtrics information

Bulk create Google Drive folders 2.0

The following Google Apps Script code was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It is an improved version of the tool built in April 2019 (original blog link here).

Improvements that have been made include:

• A Log sheet to support troubleshooting problems.
• More use of 'try/catch' to handle errors.
• Adding permissions is optional.
• Some of the column numbers can be tweaked in the Config sheet rather than diving into the code.

Remove Google Form created during copy

The following Google Apps Script is designed to remove the Google Form that is created when copying the Response Sheet file in Google Drive. The script checks to see if the original file has a linked-Form before making a copy, after which it finds the new Form's Url and removes it.

The 'Spreadsheet App' is used to open the new file and get the Url of the Form (if there is one):

var formUrl = SpreadsheetApp.openById(newFileId).getFormUrl();

Next the 'Form App' opens the newly created Google Form and gets its Id:

var form = FormApp.openByUrl(formUrl);
var formId = form.getId();

With the Id we can perform the final 2 steps of un-linking the Google Form from the Response Sheet and then deleting it:

form.removeDestination();
DriveApp.getFileById(formId).setTrashed(true);

Access RandomFox API for images

The following Google Apps Script uses the RandomFox website to access their API and generate a random image of a fox that is then pasted into a Google Sheet. The script is a follow on from this blog post for Numbers API as I continue to develop my skills with APIs.

Screenshot of fox image generated in spreadsheet

Sort through data using JavaScript Filter

The following Google Apps Script is designed to sort through a large amount of data and return only that which matches a certain criteria, using the JavaScript Filter method. In this example there is a list of students belonging to various groups (A1, B1, C1, etc) and we want to compile a list of those that belong to a Group ID that we specify.

Screenshot of dataset sorted by Group ID

Bulk create Google Docs from Google Sheet data

The following Google Apps Script is designed to bulk create Google Docs from rows of data within a Google Sheet and to include some of that data within the new Doc body (so they become customised files). It also creates a link to the new Google Doc back in the Sheet on the relevant row.

Screenshot of Google Doc data