AppsScriptPulse

Organise Google Drive files into a JavaScript object for use with Google Apps Script

Organise Google Drive files into a JavaScript Object so that they can be looped through for each named individual.

Files to loop through and organise

Files to loop through and organise

The following Google Apps Script is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (specifically their IDs) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.

What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.

In this example we have a file name pattern of ModuleCode – Tutor Name – Group Number for example ABC – Jane Doe – Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).

The collated data will look like this:

{
"Jane Doe":[
"FILE ID HERE",
"FILE ID HERE",
"FILE ID HERE"
],
"Micky Mouse":[
"FILE ID HERE",
"FILE ID HERE"
]
}

Source: The Gift of Script: Organise files into a JavaScript Object

Bulk add files to existing Google Drive folders using Google Apps Script

Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.

Bulk add files to existing Google Drive folders

Bulk add files to existing Google Drive folders

The following Google Apps Script tool is designed to take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.

The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.

Source: The Gift of Script: Bulk add files to existing Google Drive folders

Google Apps Script Tip: Sorting through an array of duplicates

Sort through an array of duplicates and extract unique values into a new array.

Remove array duplicates

Remove array duplicates

The following Google Apps Script is designed to go through an array of values that contains duplicates and create a new array of only the unique ones, arranged alphabetically. I needed this code when looping through files in a Google Drive folder where tutors and their groups formed part of the filename that I needed to extract for the end file that was created.

Source: The Gift of Script: Sort through an array of duplicates

Bulk combine CSV files into a single Google Sheet with Google Apps Script

Bulk combine multiple CSV files into a single Google Sheet, putting the data in specific locations and creating multiple tabs.

Bulk combine CSV files into a single Google Sheet

Bulk combine CSV files into a single Google Sheet

The following Google Apps Script is designed to take a number of CSV files (structured in an identical way) stored in a Google Drive folder and combine them all into a single Google Sheet file. It maps the data within the CSV file to specific cells in the Google Sheet, along with creating a tab for each of the CSV files to separate them out.

Source: The Gift of Script: Bulk combine CSV files into a single Google Sheet

Filter a try/catch error message when coding with Google Apps Script

Search a try/catch error message for keywords to determine the error message to log using JavaScript match.

Filter an error message in a try/catch

Filter an error message in a try/catch

The following Google Apps Script is designed to exercise how you might go about searching the error message in a ‘try/catch’ for keywords. I wanted this specifically for a tool I built that contains a lot of code (and hence a lot of potential error messages) between a try/catch, for which a very small number of people were experiencing a timezone issue with their Google Sheet file.

The aim was to use a JavaScript ‘match’ to find the keyword timezone and display a set of instructions for the user to resolve the issue themselves instead of just a generic error message.

Source: The Gift of Script: Filter a try/catch error message

Bulk convert Google Sheets to PDFs using Google Apps Script

Bulk convert Google Sheets within a given Google Drive folder into PDFs and optionally delete the original file.

Bulk convert Google Sheets to PDFs using Apps Script

Bulk convert Google Sheets to PDFs using Apps Script

Features

  • Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
  • Continue from where it left off – if you have a lot of Google Sheets to convert and the above runtime is reached the tool will save its progress and prompt you to run it again, avoiding any file duplication.
  • HTML popup – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
  • PDF counter – after successfully running the tool will include the number of PDFs created as part of the success popup to the user.

Source: The Gift of Script: Bulk convert Google Sheets to PDFs

Import CSV data into Google Sheet using Google Apps Script

Loop through CSV files in Google Drive and extract their contents into specific areas in Google Sheet files.

CSV File template to copy data into

CSV File template to copy data into

The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.

Source: The Gift of Script: Import CSV data into Google Sheet files

Reading Qualtrics survey details with Google Apps Script

Use the Qualtrics API to connect with a Survey and get its details.

Qualtrics API Survey details

Qualtrics API Survey details

Most of the complexity with this simply comes in getting the correct syntax for connecting to the Qualtrics API. This Get Survey Qualtrics webpage provides a little bit more technical detail for what exactly is sent/received.

You will need to complete the 3 pieces of information at the top of the Apps Script code for your own setup:

  1. API Token from the Qualtrics website,
  2. ID of the Survey you want the details of,
  3. Your Data Center ID.

Source: The Gift of Script: Get Qualtrics Survey details

Search Google Drive for ‘Shared with me’ with Google Apps Script

Search Google Drive for all of the files found in the Shared with me space and collate the results into a Google Sheet.

Search Google Drive 'Shared with me' and collate the results

Search Google Drive ‘Shared with me’ and collate the results

It will collate the following information into a Google Sheet:

  • The file name as a direct clickable link,
  • The file ID,
  • The type of file eg PDF, Google Sheet/Doc,
  • The file creation date,
  • The file last updated date,
  • The folder path,
  • The file owner.

Source: The Gift of Script: Search Google Drive for Shared with me

Autofill Google Sheet Formula with Google Apps Script

Use Google Apps Script to Autofill a formula down rows in a Google Sheet.

Autofill Google Sheet Formula

Autofill Google Sheet Formula

The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.

Source: The Gift of Script: Autofill Google Sheet Formula

Move column positions in Google Sheets with Google Apps Script

Move columns in a Google Sheet

Move columns in a Google Sheet

The following Google Apps Script is designed to reposition 2 columns within a Google Sheet. It is a small and simple bit of code but it was something new to me when I was collating lots of files together and appending columns at the end – which then needed to be moved.

In this example I am moving columns ‘collegename’ (E) and ‘shoesize’ (F) to the left of ‘postcode’ (D). So that ‘postcode’ will be the final column (F).

Source: The Gift of Script: Move Column Positions

Removing Google Drive file access permissions via a Google Apps Script Web App

Remove a users edit access to a Google Drive file via an Apps Script Web App.

Web App code sample

Web App code sample

The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.

Source: The Gift of Script: Remove File Access via a Web App

Get all selected Ranges in Google Sheets with Google Apps Script

Get all selected Ranges in a Google Sheet along with values, row and column details.

Selected ranges in a Google Sheet

Selected ranges in a Google Sheet

The following Google Apps Script was from some learning around multiple ranges being selected/highlighted by a user in a Google Sheet. I have always worked with individual cells or continuous ranges within Sheets, but during the development of my Archive Sheet data Add-on I wanted to enhance it by allowing more rows to be acted upon in one go.

I practiced getting a few values after looping through each selected range:

  • A1 Notation (e.g. A2:C5),
  • Cell values,
  • Starting row,
  • End row,
  • Number of rows (calculated from the above),
  • Starting column,
  • End column,
  • Number of columns (calculated from the above).

Source: The Gift of Script: Get all selected Ranges in a Google Sheet

Bulk convert Excel files to Google Sheet files

Iterate through a Google Drive folder of Microsoft Excel files and convert them into individual Google Sheet files.

Bulk convert Excel files to Google Sheets

Bulk convert Excel files to Google Sheets

The following Google Apps Script is designed to iterate through a Google Drive folder of Microsoft Excel files and convert them all to individual Google Sheet files.

Source: The Gift of Script: Bulk convert Excel files to Google Sheet files

Email notification of Drive file changes within last x hours

Periodically check for any changes in a given Google Drive file and receive an email notification if there are any, within your given time-frame.

Email notification of Drive file changes. Image credit: Phil Bainbridge

The following Google Apps Script is designed to periodically check for any changes that have occurred to a given Google Drive file (ie edits to it) and send an email to notify of that. It goes through the Revision (Version History) of the file and looks at the modification dates for those that match with your given timeframe, to then collect the Username & Email address of the person that made those edits for inclusion in the email.

So as an example you may have a Google Sheet where you want to setup a check every 4 hours, to then be emailed if there have been any edits to it, along with who made those edits.

Source: The Gift of Script: Email notification of Drive file changes within last x hours