Tuesday 21 June 2022

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
Web App code sample

Tuesday 7 June 2022

Bulk convert Excel files to Google Sheet files - version 1.1

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.

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.