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

Create Google Doc from Form submission

The following Google Apps Script is designed to create a Google Doc for each submission of a Google Form and translate some of that Form data into the Doc. It puts a link to the newly created Google Doc back into the spreadsheet alongside the relevant row. The Name field from the Form is used as part of the filename for the created Doc and there is a Log sheet to support any troubleshooting errors.

Screenshot of spreadsheet with data and Doc link