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

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.
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 - the code

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

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

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.