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.
 |
| Remove array duplicates |
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.
This blog post is a development of my previous one on importing CSV data into (multiple) Google Sheet files.
 |
| Bulk combine CSV files into a single Google Sheet |
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. The below Apps Script code is a simplified version of all of that aimed at just getting a file and using a typo in the code to trigger an error.
 |
| Filter an error message in a try/catch |
As of February 2023 'Convert Drive Files' is now available as a Google Workspace Marketplace Add-on.
The following Google Apps Script tool is designed to bulk convert all Google Sheets (only) within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Sheets to be deleted.
 |
| Bulk convert Google Sheets to PDFs using Apps Script |
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.
 |
| CSV File template to copy data into |
The following Google Apps Script is designed to connect to the Qualtrics API for getting the details of a Survey with a given ID, such as its name and the questions.
 |
| Qualtrics API Survey details |
The following Google Apps Script is designed to search Google Drive for all of the files found in the 'Shared with me' space. It will collate this information into a Google Sheet, including:
 |
| Search Google Drive 'Shared with me' and collate the 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.
This post is an updated version of the previous tool that has become redundant due to changes in the Qualtrics API.
 |
| Screenshot of instructional sheet requesting Qualtrics information |
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.
 |
| Autofill Google Sheet Formula |
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).
 |
| Move columns in a Google Sheet |
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.
This is achieved by some Apps Script code in the Google Sheet we want to remove access to (as this is a template file copied each time somebody completes a Google Form) that creates some hyperlink text to send the Sheet ID to the Web App when the user clicks it. The Web App itself extracts this ID, gets the person's email address from their connected session with the Web App and then executes as an admin account (or basically an account that retains access to the Google Sheet - as it will be filed away somewhere in Google Drive).
All of the complicated extras and fancy formatting has been stripped away from this solution so it focuses on the simple task of connecting with a Web App and removing access, to help make it clearer.
 |
| Web App code sample |
As of February 2023 'Convert Drive Files' is now available as a Google Workspace Marketplace Add-on.
The following Google Apps Script is designed to iterate through a Google Drive folder of files and convert only the Microsoft Excel ones into individual Google Sheet files. The code is an upgrade of this original blog post to bulk convert Excel files to Google Sheet files.
We use the method 'getMimeType()' in order to be able to iterate through all of the files and distinguish between them, so we do not try to convert anything other than Microsoft Excel, which would likely result in an error.
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:
 |
| Selected ranges in a Google Sheet |
As of February 2023 'Convert Drive Files' is now available as a Google Workspace Marketplace Add-on.
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. It does not technically check a file is Excel only (eg you may have a Word document in there), but it is recommended to only try from Excel to Google Sheets otherwise the data may be unreadable.
 |
| Bulk convert Excel files to Google Sheets |
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.
 |
| Email notification of Drive file changes |
The following Google Apps Script is designed to search through one level of Google Drive folders and extract the following information into a Google Sheet:
 |
| Enter the Parent folder ID and search the folders within |
The following code is a mixture of Google Apps Script, JavaScript, HTML and jQuery with the purpose of creating a HTML dialogue box in a Google Sheet that will seamlessly transition between 2 pieces of text at a click of a button.
This was developed as part of an Add-on I built where I needed a welcome page that contained a continue button for the user to click. Rather than having the box close and a new one open however (an action which looked very poor to see) I wanted the existing box to remain open and my new set of text to take it's place.
 |
| Bulk Rename Files interface |
It is easy to search for the names of files within Google Drive, but what happens when there are a lot of results that you need to go through?! Scrolling continuously down the page becomes quite clunky and frustrating, so with this tool you can perform that same search in a Google Sheet but have the results collated into the Sheet also, so they are much easier to then work with.
This Google Sheet will contain:
This is a Google Workspace Add-on designed to work in Google Sheets via the Side Panel and is freely available for all.
As of March 2023 'Archive Sheet Data' is now available as a Google Workspace Marketplace Add-on.
The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.
The tool uses an 'onEdit(e) simple trigger' to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.
 |
| Archive a row of data by ticking a box |
The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.
This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself - such as an error message from a try/catch.
 |
| Display Apps Script code directly in a HTML popup |
