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

Google Sheet Code

This is where we need to prepare the hyperlink text that the person clicks to send the Google Sheet ID to the Web App. So we need to get the Sheet ID:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetID = ss.getId();

After picking up the relevant cell from the Sheet and checking to see if it is blank (as we want the code to write the hyperlink to the cell only if it has been removed - e.g. the person may have accidentally deleted it) we can piece together the Web App Url:

// Web App URL
var linkText = "WEB APP URL HERE";

// append Sheet ID to URL
linkText += "?fileID=" + spreadsheetID;

// create full URL
var hyperlink = '=HYPERLINK("' + linkText + '","' + "Click this text to send data to Web App" + '")';

// insert the hyperlink into the cell

From this point each time the Google Sheet is opened the code will run and create the hyperlink if required.

Web App Code

This is a standalone Apps Script file. The Function is a 'doGet' as the user visits the app when they click the hyperlink in the Google Sheet. Here are the steps to deploy a script as a Web App (set 'Who has access' to anyone).

We need to extract the Sheet ID from the Url and get the person's email address:

  // get Event parameters passed through
  var eventParameters = event.parameters;

  // extract File ID
  var fileID = eventParameters.fileID;

  // get user's Email Address from connected Session with Web App
  var emailAddress = Session.getActiveUser().getEmail();

With this we can now access the file, get its name and remove the person as an Editor:

  // get File
  var file = DriveApp.getFileById(fileID);

  // get File name
  var fileName = file.getName();

  // remove User access to file

The final step is that we want to create an on-screen message for the person and use the 'HTMLService' to display this:

  // create some text to display on Webpage
  var body = "File ID is: " + fileID + "<br /><br />";
  body += "Your Email Address is: " + emailAddress + "<br /><br />";
  body += "Your access to the file '" + fileName + "' has now been removed.";

  // create the HTML output for displaying on the Webpage
  return HtmlService.createHtmlOutput(body);


Remove File Access via a Web App download folder (please make a copy of each of the files for your own version).

No comments:

Post a Comment