Auto-Access & Smart Alerts in Space & Sheets:Free Apps Script with Triggers & AutoRemoval

// ------------------------------
// Main function to process unread emails with label "Access Requests"
// ------------------------------
function processUnreadEmails() {
  var gmailApp = GmailApp;
  var threads = gmailApp.search('label:Access Requests is:unread');

  // Open the Google Sheet by ID
  var sheetId = "1KxwfptS3huWto"; 
  var sheet = SpreadsheetApp.openById(sheetId).getActiveSheet();

  var processedCount = 0; // Counter for processed emails

  for (var i = 0; i < threads.length; i++) {
    var thread = threads[i];
    var message = thread.getMessages()[0];

    // Extract sender name and email
    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) {
      // Add new row with email details
      var currentTimeStamp = getCurrentDateTime();
      sheet.appendRow([senderName, senderEmail, documentName, currentTimeStamp]);
      processedCount++;

      postMessageToGoogleWorkspaceChat(
        processedCount + '. Automatically giving access of document "' + documentName + 
        '" for the requested person "' + senderName + ' <' + senderEmail + '">'
      );
    } else {
      Logger.log('Email already exists: ' + senderEmail);
    }

    // Share document with sender
    var documentId = findDocumentInDrive(documentName);
    shareDocumentWithSender(documentId, senderEmail);

    // Mark email as read
    thread.markRead();
  }

  // Notify total processed
  if (processedCount > 0) {
    postMessageToGoogleWorkspaceChat('Processed ' + processedCount + ' new access requests.');
  }
}


// ------------------------------
// Trigger Setup to run periodically
// ------------------------------
function setupTrigger() {
  // Delete existing triggers
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  // Set up new trigger to run every 30 minutes
  ScriptApp.newTrigger('processUnreadEmails')
    .timeBased()
    .everyMinutes(30)
    .create();
}


// ------------------------------
// Utility: Get current date & time formatted
// ------------------------------
function getCurrentDateTime() {
  var currentDate = new Date();
  return Utilities.formatDate(currentDate, 'GMT', 'dd MMM yyyy hh:mm a');
}


// ------------------------------
// Utility: Extract document name from subject
// ------------------------------
function extractDocumentName(inputString) {
  var regex = /"([^"]*)"/;
  var match = inputString.match(regex);
  if (match) {
    return match[1];
  } else {
    console.log("No content inside double quotes found.");
    return false;
  }
}


// ------------------------------
// Utility: Find a document in Drive by name
// ------------------------------
function findDocumentInDrive(documentName) {
  var files = DriveApp.searchFiles('title contains "' + documentName + '"');
  if (files.hasNext()) {
    return files.next().getId();
  } else {
    return null;
  }
}


// ------------------------------
// Utility: Share document with sender
// ------------------------------
function shareDocumentWithSender(documentId, senderEmail) {
  try {
    if (documentId) {
      var file = DriveApp.getFileById(documentId);
      file.addViewer(senderEmail);
    }
  } catch (error) {
    console.error('Error sharing document:', error.message);
  }
}


// ------------------------------
// Utility: Find row in sheet by email
// ------------------------------
function findRowByEmail(sheet, email) {
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][1] === email) {
      return i + 1; // Row index starts from 1
    }
  }
  return null;
}


// ------------------------------
// Google Workspace Chat integration
// ------------------------------
var sheetUrlIncluded = false; // Flag to track sheet URL inclusion
function postMessageToGoogleWorkspaceChat(msg) {
  var sheetId = "1KxwfptS3huWt--hyxvTQd5mYBJpLcXcKqvYunHSAqSo";
  var sheetUrl = SpreadsheetApp.openById(sheetId).getUrl();

  if (!sheetUrlIncluded) {
    msg += '\nSheet URL: ' + sheetUrl;
    sheetUrlIncluded = true;
  }

  var message = { text: msg };
  var endpoint = "https://chat.googleapis.com/v1/spaces/AAAAsBsOjB0/messages?key=AIzaSyDdI0hCZtE6vySjMm-WEfRq3CPzqKqqsHI&token=A8tyOKXwkoJsxOnXEZBMaFMA4h29rWh0pdPHgQ1zCmM";
  var options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(message)
  };

  UrlFetchApp.fetch(endpoint, options);
}


// ------------------------------
// Revoke access for a spreadsheet
// ------------------------------
function revokeAccessSpreadsheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var editors = spreadsheet.getEditors();
  var viewers = spreadsheet.getViewers();

  editors.forEach(function(user) { spreadsheet.removeEditor(user); });
  viewers.forEach(function(user) { spreadsheet.removeViewer(user); });

  var totalCount = editors.length + viewers.length;
  Logger.log('Access revoked for ' + totalCount + ' users.');

  sendEmail(totalCount);
  return totalCount;
}


// ------------------------------
// Revoke access for a Google Doc
// ------------------------------
function revokeAccessDoc() {
  var document = DocumentApp.getActiveDocument();

  var editors = document.getEditors();
  var viewers = document.getViewers();

  editors.forEach(function(user) { document.removeEditor(user); });
  viewers.forEach(function(user) { document.removeViewer(user); });

  var totalCount = editors.length + viewers.length;
  Logger.log('Access revoked for ' + totalCount + ' users.');

  sendEmail(totalCount);
  return totalCount;
}


// ------------------------------
// Send email summary
// ------------------------------
function sendEmail(count) {
  var emailAddress = 'ashish@outrightcrm.com'; // Replace with your email
  var subject = 'Access Revocation Summary';
  var message = 'Access has been revoked for ' + count + ' users.';
  MailApp.sendEmail(emailAddress, subject, message);
}