Tuesday, 10 May 2022

Bulk convert Excel files to Google Sheet files

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
Bulk convert Excel files to Google Sheets

Tuesday, 26 April 2022

Email notification of Drive file changes within last x hours

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
Email notification of Drive file changes

Tuesday, 29 March 2022

Search Google Drive folders for creation/modified dates

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:

  • The folder name as a direct clickable link,
  • The folder creation date,
  • The folder last updated date,
  • The folder ID.
Enter the Parent folder ID and search the folders within
Enter the Parent folder ID and search the folders within

Tuesday, 1 March 2022

Show/hide sections of HTML with jQuery in a Google Sheet

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.

Tuesday, 8 February 2022

Google Add-on: Bulk Rename Files

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.

This is a Google Workspace Add-on designed to work in Google Drive via the Side Panel and is freely available for all here.

Bulk Rename Files interface
Bulk Rename Files interface


Tuesday, 1 February 2022

Google Add-on: Search Drive File Names

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:

  • a clickable link to the file,
  • the file type,
  • the date it was created,
  • when it was last updated,
  • the folder path where it is stored,
  • the ID of the file.

This is a Google Workspace Add-on designed to work in Google Sheets via the Side Panel and is freely available for all.

Tuesday, 25 January 2022

Archive Sheet data using tickboxes

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
Archive a row of data by ticking a box

Tuesday, 11 January 2022

How to pass Apps Script data directly into a HTML page

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
Display Apps Script code directly in a HTML popup