Protect sheets after time

Question

I have a spreadsheet with multiple sheets inside. What I want to achieve is for the editors to not be able to edit the sheets after a certain date. That I can do with creating a script lock function for a sheet but what about the other sheets? Do I create a lock script for each individual sheet? Then how do I program them to run. Basically, I want for 1st script which locks the sheet1 to run today for example, then the next script which locks the sheet2 to run tomorrow same time, the 3rd script which locks sheet3 to run day after tomorrow and so on. How do I do that, if that's even possible. Or maybe there's an easier way.

Thanks,

Answer 1

I think there are 2 ways we can achieve that result:

1. You can share the file as always but set an access expiration date, you will share access to a file but the access will expire after a specified date https://support.google.com/a/users/answer/9308784.

2. You can create an Apps Script project, give it a time-driven trigger so a certain function is executed after some period. This function in question should read a list somewhere (perhaps a form or sheet) and remove the access permissions.

@Bryan approach is very similar to mine. Here is my solution:

The code works with a Form with this structure (change the order by modifying the code under the reviewPermissions() function):

And using the Script Editor in the form add the following code:

let deletionSwitch;

function readResponses() {
  var responses = FormApp.getActiveForm().getResponses();

  responses.forEach(function (response) {
    deletionSwitch = false;
    reviewPermissions(response);
    
    if (deletionSwitch)
      FormApp.getActiveForm().deleteResponse(response.getId());
  });
}

function reviewPermissions(response) {
  var fileId = response.getItemResponses()[0].getResponse();
  var email = response.getItemResponses()[1].getResponse();
  var date = response.getItemResponses()[2].getResponse();

  var nextPageToken;

  if (Date.now() > new Date(date))
    do {
      var response = getPermissions(fileId, nextPageToken);
      var permissions = response.items;

      permissions.forEach(function (permission) {
        if (permission.emailAddress.toLowerCase() == email.toLowerCase()) {
          deletionSwitch = true;
          deletePermission(fileId,permission);
        }
      });
    } while (nextPageToken = response.nextPageToken)
}

function getPermissions(fileId, token = null) {
  return permissions = Drive.Permissions.list(fileId, {
    fields: "nextPageToken,items(id,emailAddress,role)",
    pageToken: token
  });
}

function deletePermission(fileId,permission){
  if (permission.role != "owner")
    Drive.Permissions.remove(fileId,permission.id);
}

This code needs Google Drive to be added as an Advanced Google service, add it with the name "Drive". Information about Advanced services is available in this documentation https://developers.google.com/apps-script/guides/services/advanced.

Necessary triggers:

• Form onSubmit, execute the readResponses() function.
• Time-driven (clock), execute the readResponses() function at the interval you prefer, I recommend every day.

Short code explanation:

The trigger will read all Form entries. If there is a response that has an older date than today (expired) the code will check all the permissions of the file and will delete all permissions assigned to that email address address in the entry (not case sensitive).

Note:

• Entries will be removed once their date expires.
• Entries with dates in the future are ignored and checked in future runs.
• Permission deletion is retroactive so submitting an entry with a date in the past will cause the permission to be deleted immediately (if exists).
• The owner permission can't be removed, the deletion won't be attempted and the entry removed.
• This code only works with files you own or have permission editor access to, you can request other people to copy the form with the script and use it with their own files.
• Linking the Form responses to a Google Sheet file will allow you to have a historical record of what permissions should expire, this is not necessary for the code to work, just convenient for record purposes. Requesting the email address in the Form should not affect functionality.

Answer 2

You can use the simple trigger onOpen(), this will run this script every-time a user opens the file:

function onOpen() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets(); //Getting all the sheets from the file.
  const lockDates = ss.getSheetByName("LockDates").getDataRange().getValues(); //Getting list of sheets and their lockdates.
  const now = new Date(); //Getting today's date.
  
  for (i=0; i < sheets.length; i++){
    var currentSheet = sheets[i];
    var sheetIndex = (lockDates.flat().indexOf(currentSheet.getName())/2); //This is to get the index where the current sheet name is located.
    if(sheetIndex >= 0){ //If the sheet is not on the list we get -1.
      var sheetLockDate = lockDates[sheetIndex][1]; //Assiging the lockDate to a variable.
      if (now >= sheetLockDate && sheetLockDate > 0){ //Evaluating if today's date is on or after the lockDate.
        currentSheet.protect();
        console.log('Sheet -' + currentSheet.getName() + '- was protected');
        break;
      } 
      else { //The sheet is unprotected if it's still not time to protect it.
      currentSheet.protect().remove();
      }
    }
  }
}

Note the following:

• This script will determine the lock dates based on a table at "LockDates" sheet, the code might break if you add additional columns.
• If the sheet is not included in the list it will not be affected.
• If the sheet is included in the list but doesn't have a lockDate it will be unprotected. This will let you modify the lockdate of specific sheets if needed.
• You could protect the control sheet "LockDates" and it will not be affected by the script while it is not added to the list.

This is the setup where the code worked: