Pages

Tuesday, 2 June 2020

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
Screenshot of dataset sorted by Group ID

Tuesday, 26 May 2020

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
Screenshot of Google Doc data

Tuesday, 19 May 2020

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
Screenshot of spreadsheet with data and Doc link

Tuesday, 12 May 2020

Create a Shortcut in Google Drive

The following Google Apps Script is designed to create a Shortcut to an existing Google Drive folder, within another folder.
Screenshot of Shortcut file in Google Drive
Screenshot of Shortcut file in Google Drive

Tuesday, 5 May 2020

Bulk extract text from Google Docs for analysis

The following Google Apps Script is designed to go through a number of Google Docs and extract specific text from the body that represents an answer to a question. The tool was developed as a result of a Researcher needing to analyse hundreds of files that were each an answer to various survey questions. There were two specific sections that needed to be targeted and the content collated into a spreadsheet so further analysis could be performed.
Screenshot of Doc Analysis results in spreadsheet
Screenshot of Doc Analysis results in spreadsheet

Tuesday, 28 April 2020

Create a copy of a file using try/catch

The following Google Apps Script code is designed to create a copy of a template file and return its Url & ID. It is designed as a Function that would sit within your existing code (hence there are input parameters) and it makes extensive use of 'try/catch' in order to capture any errors that could occur. It also makes use of our log actions code detailed in this blog post so we can output the error somewhere.

We start by getting the template file that we want to make a copy of but wrap this within a try/catch. As part of getting the file we include an additional variable (gotTemplateFile) set to either true or false depending upon success, which we can then use to determine if the script should proceed with the next step. We also make use of the logEvent Function to output any errors messages:
try {
    // get template file
    var templateFile = DriveApp.getFileById(templateFileId);
    var gotTemplateFile = true;
  }
  catch(e) {
    logEvent('Problem with template file, error: ' + e);
    var gotTemplateFile = false;
  }

Tuesday, 21 April 2020

Drive API - share a file without email notification

The following Google Apps Script makes use of the Drive API to allow an item to be shared (eg edit access) without the recipient receiving the automated email typically generated when using the DriveApp.

Enable Drive API
In Script Editor go to Resources > Advanced Google services ... > scroll down and turn on Drive API > click OK.

The Code
First we create an array of objects that defines 3 items: value, type and role. These details will form the 'permissions resource':
var resource = {
    // enter email address here
    value: 'user@example.com',

   
    // choose from "user" or "group" if using a Google Group
    type: 'user', 
              
   
    // choose from: "owner", "writer" or "reader"
    role: 'writer'              
};

Tuesday, 14 April 2020

Google Form - use a switch to determine save location

The following Google Apps Script is designed to take a specific option selected on a Google Form and use that to determine where something could be stored. The example it was developed for was Risk Assessments where depending on the type of assessment selected on the Form the generated file was to be saved in a specific Google Drive folder.

The example here is a stripped down version that uses colours to determine which Google Drive folder ID should be selected from the Config sheet for the script to proceed. I have another example of the 'switch' process in this blog post on getting a weekday name from a date.
Screenshot of Google Form question
Screenshot of Google Form question