Handling a flood of access requests for your Google Docs and Sheets can be a real headache, especially when you're already juggling old users who no longer need access and new folks eagerly waiting to jump in. Since here you have to provide access of your doc or sheet to the new ones and have to revoke the access from the previous ones as there is a limit on access holders. 

But guess what? There's a super easy way to make this whole process a breeze โ€“ and it's all about automation! Imagine you've got this magical little helper that can automatically perform all the tedious work for you. All you need to do is give it the right instructions only. 

For this, you just need to start by telling your helper where you need to take action. Then, you will have to play a quick copy-paste game only.


How Do You Automate Your Google Doc and Sheet Management Process? 

Here are the following steps that you need to follow to make your access management work automated. 

Letโ€™s start to automate your Google Document as well as sheet access-sharing and removing management processes within a proper record.

First, you need to copy the URL of the sheet in which you want to maintain your access sharing automation and access revoke automation. Once you have copied the sheet URL, you have to go to the Extension option of the page and click on the Apps Script button. As you do, the Apps Script coding section opens to you, here, you need to paste the code first, along with giving a name to your project.


For further, you have to paste the copied URL of the sheet in front of Doc in between inverted commas. Next, you need to paste the URL again from the slash icon after โ€œdโ€ to its next slash icon before the edit option in front of the script's Sheet ID. Now, put your email ID in front of the email address where you want to get the email.ย 

Now, go to Google Workspace and create a New Space by clicking on the New Chat option. Next, click on the โ€œcreate a spaceโ€ button and give an appropriate name like we have given โ€œautoshare-requestโ€ and click on the โ€œcreateโ€ button. Go to the drop-down button of the created space and select the Apps and Integrations option. 

Next to this, click on the โ€œAdd Webhooksโ€ button showing at the bottom right of the page and click on the โ€œSaveโ€™ button by pasting the space name there to start the automation process. Now, click on the three-dot icon, and will copy the link. Once itโ€™s done, go back to your Apps Scriptโ€™s coding section again and paste the copied link in front of the โ€œendpointโ€ between both inverted commas. 



Now, you will see that whenever you will get an email for access share, then that email address will automatically get access. Plus, all entries will automatically get updated in the sheet, like email address, user name, date, time, & more, along with an automated notification on the created space. 


What are Filters? What Do They Do?

Filters in Apps Script are something that allows you to control the programmatic display of data in Google Sheets by specifying criteria. For example, the number of raw that need to be displayed or hidden, enhancement of data analysis, and presentation capabilities as well. 

By applying the filters in Apps Script, you can manage and facilitate selectively displaying data and analysis of that as well. Thereby, filters allow you to enhance the functionality and automation capabilities of your Google spreadsheet and Documents as well.


How to Automate Filters? Stepwise Configuration:

You are allowed to set filters on any of the desirable emails to which you want to bring in the particular label. Hence, to start the process, you first need to open up the email and then click on the three-dot icon. 

As you can see, the โ€œFilter messages like thisโ€ option. Hence, you need to give it a subject and click on the โ€œCreate the Filterโ€ button.

Here, weโ€™ll give the subject โ€œShare request for โ€œAccess Automationโ€ and will click on the โ€œCreate filterโ€ option.

As you will click on the โ€œCreate filterโ€ option, the options list will open up to you. So, you need to click on the โ€œApply the labelโ€ option and then click on the โ€œNew labelโ€ option by selecting on the โ€œchoose labelโ€ button. 

Now, we will give an appropriate name to the label, such as โ€œautoshare-requestโ€ for our label, and click on the โ€œCreateโ€ button. 

To add more filters, weโ€™ll click on the โ€œAlso apply filter to 1 matching conversationโ€ option. And finally, your filter has been added here. Therefore, this is how you can simply add the filter to your access automation.


What are Triggers? What Do They Do?

In Google spreadsheet and documents, Apps Script are functions that automatically run based on specific events or time intervals. They allow developers to automate any tasks, such as sending emails, updating spreadsheets, and responding to user actions without any manual intervention. 

You are allowed to make your triggers time-driven or event-driven and can make your overall workflows streamlined within Google Workspace applications. Since, triggers play a crucial role in automating repetitive tasks, improving efficiency, and reducing errors in Google Workspace workflows. Additionally, by leveraging triggers effectively, developers can streamline processes and focus on higher-value activities.


How to Automate Triggers? Stepwise Configuration:

Triggers in the Apps Script are straightforward to create and update hence, to create your desired Triggers, you just need to execute the following steps: 

Go to the script section of the Apps Script coding page and then click on the Trigger option showing on the left side panel next to the clock icon. You need to add two triggers here, one for โ€œprocessunreadEmailsโ€ and the other for โ€œrevokeAccessโ€ within their specific timing. Since the โ€œprocessunreadEmailsโ€ will automate your access-sharing task, while the trigger for โ€œrevokeAccessโ€ will automate your access-revoking task.

Hence, to add each trigger, go to the โ€œAdd Triggerโ€ button shown in the bottom right corner and add functions accordingly. For this, select the โ€œprocessunreadEmailsโ€ option in the function, the โ€œHeadโ€ option in deployment, the โ€œTime-drivenโ€ option in the Event source, the โ€œDay Timerโ€ option in triggerโ€™s type, and โ€œ2 am to 3 amโ€ option in time of the day means time interval.

To create the next trigger, which is for the automated access removal process, click on the โ€œAdd trigger buttonโ€ and select the โ€œrevokeAccessโ€ option in the function.


Apps Scripts Access Automation

For further, choose the โ€œHeadโ€ option in deployment, the โ€œTime-drivenโ€ option in the Event source, the โ€œWeek Timerโ€ option in triggerโ€™s type, and the โ€œEvery Sundayโ€ option in time of the day of the week, โ€œMidnight to 1 amโ€ in time of day, and finally will click on the โ€œSaveโ€ button.

You are completely allowed to add more than two filters based on your specific requirements and make your work fully automated.  

This is just the viewpoint of the script (code) for both triggers โ€“ one for the revokeAccess function and one for the function 

function revokeAccess() {
  var file = DriveApp.getFileById(sheetId);
  var mimeType = file.getMimeType();
  if (mimeType === MimeType.GOOGLE_SHEETS) {
    revokeAccessFromSpreadsheet(sheetId);
  } else if (mimeType === MimeType.GOOGLE_DOCS) {
    revokeAccessFromDocument(sheetId);
  } else {
    Logger.log("The file is neither a Google Sheet nor a Google Doc.");
  }
}


function processUnreadEmails() {
  var gmailApp = GmailApp;
  var threads = gmailApp.search('label:'+label+' is:unread');
  var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();
  for (var i = 0; i < threads.length; i++) {
    var thread = threads[i];
    var message = thread.getMessages()[0];
    var sender = message.getReplyTo();
    var senderName = sender.substring(0, sender.indexOf('<')).trim();
    var senderEmail = sender.substring(sender.indexOf('<') + 1, sender.indexOf('>')).trim();
    var subject = message.getSubject();
    var documentName = extractDocumentName(subject);
    // Check if email already exists in the sheet
    var existingRow = findRowByEmail(sheet, senderEmail);
    if (!existingRow) {
      var currentTimeStamp = getCurrentDateTime();
      sheet.appendRow([senderName, senderEmail, documentName, currentTimeStamp]);
      postMessageToGoogleWorkspaceChat('Automatically giving access of document "' + documentName + '" for the requested person "' + senderName + ' <' + senderEmail +'>"');
    } else {
      // If email already exists, log or handle as needed
      Logger.log('Email already exists: ' + senderEmail);
    }
    var documentId = findDocumentInDrive(documentName);
    shareDocumentWithSender(documentId, senderEmail);
    thread.markRead();
  }
}

To have a complete code, please drag this article by the end and send your request to us. Hence, weโ€™ll share access to the script with you, and youโ€™ll get the code. 


What are Executions? What Do They Do?

Executions are just like the end result, where you can have all the information about your triggers within their specific timing and function by going to the Executions option. For example,  when triggers were created, their timing, status, and more. Apart from this, you can add filters in execution to check the last five minutes added triggersโ€™ activity or the last triggersโ€™ activity and more.


How to Configure Executions? 

Go to the โ€œExecutionsโ€ option showing on the side panel just down to the Trigger option. And check out the activities of all added triggers, including their multiple parameters such as start time, duration, function, status, type, and deployment.

Within this filter button, you will get many options such as in Start time - last 5 minutes, 30 minutes, 1 hour, 24 hours, in Status - Running, Paused, Completed, Cancelled, Failed, Time out, Disabled, in Type - multiple options again, In Deployment - Head and can put Trigger ID.

For example, if youโ€™ll set the โ€œlast 5 minutesโ€ execution through the Start time by clicking on the Apply button, then only the last 5 minutes added filters will display to you. 


Letโ€™s Recap Before You Go!

In this article, weโ€™ll thoroughly explain to you how you can manage any Google sheet access automatically & get their notification on space with proper entries in the sheet. Furthermore, weโ€™ll let you know how to add filters and create triggers with their proper executions. Therefore, without any perplexion and delay in work, letโ€™s jump into the article directly. 


Please Ask for the complete Code below