Google Add-on: Convert Drive Files update December 2023

 

What it does

Bulk convert Google Drive™ files quickly and easily with this Add-on. Convert between Google™, Microsoft Office™, OpenDocument, PDF, etc. Point the Add-on at a Google Drive™ folder, select the file types you want to convert, select your destination folder and whether you want the original file to be deleted, then hit go! Saves having to go into each file individually, selecting the file type to download and then selecting the save destination.

Update

• Remove original file extension option
• Based on feedback, the default behaviour for automatically removing any existing file extension has been stopped. There is now a simple Yes/No option allowing you to choose for yourself exactly what you want to happen. The main reason for this change was due to file names containing 'full stops' that would otherwise be lost as the tool treat them as part of the extension.

 

Find out more

Convert Drive Files Add-on webpage - includes instructions and demo videos.

Check for existing Form Submit Triggers

The following Google Apps Script is designed to check existing user triggers of a Google Sheet for any onFormSubmit triggers. Any that are found are then deleted and a new onFormSubmit trigger is created.

Check existing user triggers of a Google Sheet

Google Add-on: Bulk Rename Files/Folders update November 2023

What it does

Bulk rename Google Drive™ files/folders quickly and easily with this Add-on. You define exactly what you want to search for in the file/folder name and the text to replace it with. Along with options for handling special characters and append/prepend to the existing file/folder name. Works across My/Shared drives.

Update

• Regular Expressions
• New regular expressions options now available - create your own powerful search queries to target exactly what you want.
  
  
• Upper / Lower Case
• New upper/lower case options now available.

 

Find out more

Bulk Rename Files Add-on webpage - includes instructions and demo videos.

Control Form submissions with Script Lock

The following Google Apps Script is one example of how the Lock Service can be used to prevent concurrent running of code. Here we have a Google Form that can be submitted by users at any point, the code then takes some of those details and appends them to another Google Sheet row. In normal circumstances this will happen relatively quickly and without clashes, but what if multiple people submit the Form at the same time!?

The likelihood is that whilst the Form data will still be collected correctly in the response sheet, the append Sheet will either overwrite data as it tries to keep up or miss it completely. By using the Lock Service we can allow each Form submission time to complete its bit of code before the next one starts.

Lock Service code snippet

Format and tidy a Google Sheet report

The following Google Apps Script is designed to take a Microsoft Excel file, convert it to a Google Sheet, remove unnecessary data/columns/rows, add some nice formatting and concatenation to create email addresses. The purpose of this is to show you some nice and easy ways of working with spreadsheet data, as well as creating something that will become a time-saver if you regularly do this manually.

Use Google Apps Script to easily format a Report

Google Add-on: Bulk Rename Files update September 2023

What it does

Bulk rename Google Drive files quickly and easily with this Add-on. You define exactly what you want to search for in the file name and the text to replace it with. Along with options for handling special characters and append/prepend to the existing file name. Works across My/Shared drives.

Update

• Bulk rename folders
• A new tickbox feature has been added to now allow you to rename folders.

 

Find out more

Bulk Rename Files Add-on webpage - includes instructions and demo videos.

Autofill Google Sheet Formula each day

The following Google Apps Script is designed to check a Google Sheet once per day and if the date is in the past it Autofills another row with the existing formulas used across the columns. This post is a variation of the Autofill Google Sheet Formula one.

Autofill Google Sheet formulas each day

Sort through an array of duplicates with Sets

The following JavaScript code is a quick and easy alternative to sorting through an array of duplicates. It was highlighted to me by my colleague Tom Smith. As before it is designed to go through an array of values that contains duplicates and create a new array of only the unique ones, but not arranged alphabetically in this instance.

Sort an array with JavaScript Sets

Bulk create Drive folders with subfolders

The following Google Apps Script is designed to bulk create Google Drive folders with multiple subfolders. You control the naming convention of each folder and exactly how many you want.

Use this tool to bulk create folders with subfolders

Bulk duplicate Google Drive files

The following Google Apps Script tool is designed to take a single Google Drive file e.g. a Doc / Sheet / Slide and make duplicates/copies of it with unique file names. The tool will also create a clickable link within the Google Sheet to each new file copy.

Screenshot of the tool for duplicating Drive files

Auto close Google Form after X responses

As of January 2024 this tool is now available as a Google Workspace Marketplace Add-on.

The following Google Apps Script is designed to automatically close a Google Form once it has reached the number of responses you specify.

 

Download

Auto close Google Form after X responses download (please use 'Overview' > 'Make a copy' for your own version).

Create a Google Doc for a single row of Sheet data

The following Google Apps Script is designed to create a Google Doc for the selected row of data in a Google Sheet and to include some of that data within the new Doc. it also creates a link to the new Doc back in the Sheet on the relevant row.

This tool is a singular / simplified version of my Bulk create Google Docs from Google Sheet data one.

Screenshot of Sheet data to create a Doc for

Google Add-on: Bulk Rename Files update May 2023

What it does

Bulk rename Google Drive files quickly and easily with this Add-on. You define exactly what you want to search for in the file name and the text to replace it with. Along with options for handling special characters and append/prepend to the existing file name. Works across My/Shared drives.

Update

• Sequential numbering
• Append/prepend a sequential number to the existing file name that will automatically increment as it loops through the files.
• In the search text enter '<<SEQUENCEXX>>' where 'XX' is the number sequence you wish to use (it can be more/less that 2 digits long). In the replacement text enter a combination of '<<SEQUENCE>>' and '<<KEEPNAME>>' depending on if you want to append/prepend.
• Original file name example 1: 'My sample file'.
• New file name example 1: 'My sample file 01'. So search text would be '<<SEQUENCE01>>' and replacement text '<<KEEPNAME>> <<SEQUENCE>>'.

• Original file name example 2: 'Building code'.
• New file name example 2: '005 Building code BC'. So search text would be '<<SEQUENCE005>>' and replacement text '<<SEQUENCE> <<KEEPNAME>> BC' .

Add sequential numbers to existing file names

Google Form Checkbox responses

The following Google Apps Script is an example of one way to get the responses from a Checkbox-type question on a Google Form and how you might go about differentiating them.

This came up for a project I was working on where I need to put a Yes/No value into 3 separate Google Sheet cells based on 3 options in a question. The slight challenge is that all of the responses come out as a single array for this question, containing the strings of the values that have been ticked only.

Get all checkbox responses from a Form

Switch view to a specific Google Sheet

The following Google Apps Script code is designed to switch the active sheet that the user is currently looking at to that of another within the same Google Sheet file. This is a snippet of code that could be placed at the end of a function when archiving data from one sheet to another for instance, and you want the destination to be shown to the user.

Apps Script Code to switch the active view for a user

Read & write multiple User Properties

The following Google Apps Script is a few snippets of some larger code where I needed to write (and then later read back) a number of User Properties in one go. Rather than creating multiple single write requests it is more efficient to do this in bulk.

I also needed a way to bulk read/extract these values later from a JavaScript Object that would allow me to easily call the Property name and get its value in return.

Access User Properties and get the values in an Object

Delete Google Calendar Events by keyword and date range

The following Google Apps Script is designed to search a date range for Google Calendar events containing a given string (something to help target those events) and then delete them.

Search for and delete Google Calendar events

Google Add-on: Bulk Rename Files update Feb 2023

 

What it does

Bulk rename Google Drive™ files quickly and easily with this Add-on. You define exactly what you want to search for in the file name and the text to replace it with. Along with options for handling special characters and append/prepend to the existing file name. Works across My/Shared drives.

Update

1. Special characters
• The tool can handle: - [ ] / \ { } ( ) * + ? . ^ $ | # 
• You could remove individual characters:
• Original file name example: 'Archive backup (ready)'.
• New file name example: 'Archive backup ready'.
• You could replace these characters with others:
• Original file name example: '.backup folder'.
• New file name example: '_backup folder'.
• You could both remove and replace them at the same time:
• Original file name example: 'This experiment belongs to _[Sample]'.
• New file name example: 'This experiment belongs to Phil'.
  
2. Append / Prepend
• You can add text/characters to the beginning and/or end of the existing file name.
• In the search text enter '<<KEEPNAME>>' and in the replacement text enter 'Something before <<KEEPNAME>> something after'.
• Original file name example: 'is my'.
• New file name example: 'This is my Document'. So replacement text would be: 'This <<KEEPNAME>> Document'

Append / Prepend to the existing file name

Google Add-on: Convert Drive Files

What it does

Bulk convert Google Drive files quickly and easily with this Add-on. Convert between Google, Microsoft Office, PDF, etc.

Point the Add-on at a Google Drive folder, select the file types you want to convert, select your destination folder and whether you want the original file to be deleted, then hit go!

Saves having to go into each file individually, selecting the file type to download and then selecting the save destination.

Convert File Types

Google Doc™ to PDF | Microsoft Word™ | OpenDocument Text | Rich Text Format | HTML

Google Sheet™ to PDF | Microsoft Excel™ | OpenDocument Spreadsheet

Google Slide™ to PDF | Microsoft PowerPoint™ | OpenDocument Presentation

Google Drawing™ to PDF | JPEG | PNG | SVG

Microsoft Word™ to PDF | Google Doc™ | OpenDocument Text | Rich Text Format

Microsoft Excel™ to PDF | Google Sheet™ | OpenDocument Spreadsheet

Microsoft PowerPoint™ to PDF |Google Slide™ | OpenDocument Presentation

Plain Text to Google™ Doc | Microsoft Word™

PNG | BMP to JPEG

More being added ...

Find out more

Convert Drive Files Add-on webpage - includes instructions and a demo video.

Organise files into a JavaScript Object 2.0

The following Google Apps Script is an enhanced version of this blog post for organising files into a JavaScript Object. The difference this time however is that we are extracting 3 (not 1) pieces of information from the CSV file name and coding them into an iteratble JavaScript Object. This is more likely the process you will want to use going forwards given the extra flexibility.

The code 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 (their IDs, the module code and group number) 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:

{Micky Mouse=[{fileId=FILE ID HERE, group=Grp 05.csv, moduleCode=CDE}, {moduleCode=CDE, fileId=FILE ID HERE, group=Grp 01.csv}], Jane Doe=[{group=Grp 04.csv, fileId=FILE ID HERE, moduleCode=ABC}, {moduleCode=ABC, fileId=FILE ID HERE, group=Grp 03.csv}, {fileId=FILE ID HERE, moduleCode=ABC, group=Grp 02.csv}]}

Files to loop through and organise

Organise files into a JavaScript Object

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"]}

Files to loop through and organise

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.

Bulk add files to existing Google Drive folders