Remove File Access via a Web App

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

Bulk convert Excel files to Google Sheet files - version 1.1

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.

The Code

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.

Get all 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).

Selected ranges in a Google Sheet

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

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

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

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.

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