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,

CodePudding user response：

You can use the simple trigger

CodePudding user response：

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

   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.