Tuesday 22 December 2020

Bulk manage members of a Google Group - overview

The following Google Apps Script is designed to allow bulk management of members of a Google Group all from a Google Sheet. So with a list of email addresses and roles (owner, manager, member) you can add/remove/edit a large number of users with the click of a button. The tool provides an easier way of doing this as opposed to the web portal for managing members (www.groups.google.com).

Bulk manage a Google Group from a Google Sheet.
Screenshot of the tool in a Google Sheet.

Features of the tool

  • Overcomes some of the web portal limits on bulk adding members.
  • Checks that the person running the tool is at least a 'manager' of the group so they do not accidentally lose access.
  • Checks there is an 'owner' of the group present to prevent permanent loss of access to the group for everyone.
  • Creates a full log of all changes made in the Google Sheet so you have a record of actions.
  • Will stop if it encounters and issue with an email address (for example) but will not need to re-sync any members prior to that point.
  • Uses toast popups to inform you of the progress as it may take several minutes to run depending on how many members you have.
  • Uses an in-house API url to connect to Google Groups, for which the address has been removed from the Apps Script code (you will need to insert your own).


Bulk manage members of a Google Group - the code

Following on from the overview blog post here, I have included the code itself from which the tool is built.

Note: this is not a complete solution here - it relies on API authentication which was provided at an institutional level and is unavailable outside of this. The code still provides invaluable insights however and you may have your own authentication you can plug in.

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

Tuesday 28 July 2020

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.

 

Update 18/08/2022

Unfortunately recent changes to the Qualtrics API means this tool no longer works. There is now an updated version of the tool.

There is a list of instructions to be completed with Qualtrics account data to be entered.
Screenshot of instructional sheet requesting Qualtrics information

Tuesday 23 June 2020

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.

Tuesday 16 June 2020

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);

Tuesday 9 June 2020

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
Screenshot of fox image generated in spreadsheet

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

Tuesday 7 April 2020

Bulk change ownership of Google Drive items

The following Google Apps Script is designed to bulk transfer ownership of Google Drive files/folders by iterating through them. It gets the current owner and compares that to the new designated owner before making the change. This particular example will retain the previous owner as an editor of the item.

 

Update 08/03/2023

Unfortunately this tool no longer appears to work for Consumer accounts, only Workspace ones, due to changes in a user needing to accept a request to transfer ownership. Stack Overflow page with some further details.

Screenshot of transfer tool requirements
Screenshot of transfer tool requirements

Tuesday 31 March 2020

Google Drive Picker for Folder ID

The following Google Apps Script and HTML code is used to create a simple Google Picker that will let a user navigate their Google Drive and select a folder from which we can then extract its ID and create a clickable Url link.
Screenshot of Google Picker in Drive
Screenshot of Google Picker in Drive

Tuesday 24 March 2020

Batch add data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to insert a Google Sheet of data into an existing SQL Table in batches, rather than row-by-row, to greatly improve performance. This post improves upon this one for bulk adding data.

By using the 'addBatch' method we can collate each row of student data into a single SQL statement that can be used to connect to the database once and execute multiple times. In order to do this we use 'prepareStatement' to which we can then feed in our values
var stmt = db.prepareStatement("INSERT INTO students (StudentNo, FirstName, LastName, Shoesize) "
                                 + "VALUES (?, ?, ?, ?)");

Tuesday 17 March 2020

Create a Tickbox to select all other Tickboxes

The following Google Apps Script is designed to enable a select-all Tickbox within Google Sheets so that multiple Tickboxes can be un/ticked by clicking just the one. In this example there are 3 master Tickboxes which will un/tick a selection of others based on the specified ranges.
Screenshot of Google Sheet with Tickboxes
Screenshot of Google Sheet with Tickboxes

Tuesday 10 March 2020

Animate an element in jQuery

The following jQuery code animates a square box by sliding it across the webpage after clicking a button.
Screenshot of button and square to animate
Screenshot of button and square to animate
The blue square is created by using CSS styling. Clicking the button then applies the 'animate' method to the #square 'div' moving it a set distance across the webpage (800px in this example) at a speed of 1 second.

Tuesday 3 March 2020

Search for artist in iTunes API

The following Google Apps Script takes the name of an artist from a Google Sheet, passes it to the iTunes API and returns a list of albums, song titles, artwork, etc all formatted into a Google Sheet table.
Screenshot of artist details in Google Sheet table
Screenshot of artist details in Google Sheet table

Tuesday 25 February 2020

Access Numbers API for maths fact

The following Google Apps Script is my first look at accessing an API and handling the data that comes back. I am using Numbers API to return a random maths fact and append it to a Google Sheet.

API Url: http://numbersapi.com/random/math

We use the 'UrlFetchApp' to then call the API and 'getContentText' to store the returned data. From there we can simple 'appendRow' in the spreadsheet to build up a list each time we run the function.

Tuesday 18 February 2020

Bulk create test folders/files in Google Drive

The following Google Apps Script is designed to bulk create a set number of Google Drive folder/files as quickly as possible. Its purpose was to support a bigger project I was working on with bulk moving folders/files from My Drive to Shared Drive.
Screenshot of example folder structure in Google Drive
Screenshot of example folder structure in Google Drive

The script makes use of 4 'loops' with the option to define how many folders/files you want to create at each level. There is an initial top row of folders each with files, then a row of sub-folders under this each with files too.

Tuesday 11 February 2020

Create a slide down panel in jQuery

The following jQuery code creates a clickable area that then slides down a panel with further information displayed.
Screenshot of side panel with extra content
Screenshot of side panel with extra content
The 2 panels are created with different 'div' tags so that a 'click' method can be assigned to the first (topbar) which then initiates the 'slideDown' method on the second (panel). Some CSS styling is applied for 'padding', etc.

Tuesday 4 February 2020

Create buttons to hide/show text in jQuery

The following jQuery code is designed to have 2 buttons - 1 which hides the webpage text and the other which shows it again.
Screenshot of show/hide buttons
Screenshot of show/hide buttons
Each button has its own 'id' name depending on which task it will perform: show or hide. We use the '#id Selector' to target each id and use the 'click' method to either 'show/hide' all elements within a paragraph (<p>) tag.

Tuesday 28 January 2020

Display an alert box on mouse enter in jQuery

The following jQuery code is designed to create an alert dialogue box in the browser when the mouse passes over a particular element on the webpage.
Screenshot of mouse over alert box
Screenshot of mouse-over alert box
In this example we assign the 'id' p1 to the paragraph element so that we can target it for the 'mouseenter' action. We then create an 'alert' message box in the browser with some custom text.

Tuesday 21 January 2020

Hide text with a button in jQuery

The following jQuery code is designed to hide elements within a paragraph (<p>) tag when a button is clicked.
Screenshot of webpage with text and button
Screenshot of webpage with text and button
We make use of the 'click' method to create a function that runs when the button is clicked to then 'hide' all <p> elements on the webpage.

Monday 13 January 2020

Bulk save emails from Gmail - overview

The following Google Apps Script is designed to bulk save emails (and associated attachments) from a specified Gmail label into a Google Drive folder, with relevant threads combined into a single PDF document. The tool is set to automatically schedule itself by default as the process can take well over an hour depending on the length/number of emails to save.
Screenshot of setup sheet for tool
Screenshot of setup sheet for tool

Bulk save emails from Gmail - the code

Following on from the overview blog post, here I have included the code itself from which the tool is built.

Tuesday 7 January 2020

Bulk add unique data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table, but only after checking the record does not already exist so that it can be safely re-run. This post improves upon this one for bulk adding data.

getStudentsTableUnique
This function starts the process by getting data from the Table so that we can later compare what we want to upload with what already exists. It uses a 'select' query detailed in this blog post to return current student numbers (StudentNo column). These are then pushed into an empty array and passed to the next function.

bulkAddUniqueStudents
Now that we have an array of student numbers currently in the SQL Table, we can pick up the data from the Google Sheet, loop through each row in turn and use an 'if' to check for matching student numbers. We can make use of a flag variable to determine what should happen when all student numbers have been compared.