AppSScript Code

Copy Code

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];
    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) {
      // If email doesn't exist, add a new row with the information
      var currentTimeStamp = getCurrentDateTime();
      sheet.appendRow([senderName, senderEmail, documentName, currentTimeStamp]);
     
      processedCount++; // Increment the counter for each processed email
      postMessageToGoogleWorkspaceChat((processedCount + 0) + '. 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();
  }


  // Display the count after processing all emails
  if (processedCount > 0) {
    postMessageToGoogleWorkspaceChat('Processed ' + processedCount + ' new access requests.');
  }
}


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


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










function getCurrentDateTime() {
  var currentDate = new Date();
  var formattedDate = Utilities.formatDate(currentDate, 'GMT', 'dd MMM yyyy hh:mm a');
  return formattedDate;
}




function extractDocumentName(inputString) {
  var regex = /"([^"]*)"/;
  var match = inputString.match(regex);
  if (match) {
    var contentInsideQuotes = match[1];
    console.log(contentInsideQuotes);
    return contentInsideQuotes;
  } else {
    console.log("No content inside double quotes found.");
    return false;
  }
}




function findDocumentInDrive(documentName) {
  var files = DriveApp.searchFiles('title contains "' + documentName + '"');
  if (files.hasNext()) {
    var file = files.next();
    return file.getId();
  } else {
    return null;
  }
}


function shareDocumentWithSender(documentId, senderEmail) {
  try {
    if (documentId) {
      var file = DriveApp.getFileById(documentId);
      file.addViewer(senderEmail);
    }
  } catch (error) {
    console.error('Error sharing document:', error.message);
    // Log more details if needed (e.g., documentId, senderEmail)
  }
}




function findRowByEmail(sheet, email) {
  // Find the row index based on the email in the specified sheet
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][1] == email) {
      return i + 1; // Return the row index (add 1 because row indices start from 1)
    }
  }
  return null; // Return null if the email is not found
}










 
 var sheetUrlIncluded = false; // Flag to track if sheet URL has been included
function postMessageToGoogleWorkspaceChat(msg) {
  var sheetId = "1KxwfptS3huWt--hyxvTQd5mYBJpLcXcKqvYunHSAqSo";
  var sheetUrl = SpreadsheetApp.openById(sheetId).getUrl(); // Obtain sheet URL


  if (!sheetUrlIncluded) {
    // Add sheet URL if it has not been included before
    msg += '\n' + 'Sheet URL: ' + sheetUrl;
    sheetUrlIncluded = true; // Update the flag
  }


  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)
  };
  var response = UrlFetchApp.fetch(endpoint, options);
}




 
function revokeAccessSpreadsheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();








  // Get the list of editors and viewers
  var editors = spreadsheet.getEditors();
  var viewers = spreadsheet.getViewers();








  // Remove access for editors
  for (var i = 0; i < editors.length; i++) {
    spreadsheet.removeEditor(editors[i]);
  }








  // Remove access for viewers
  for (var i = 0; i < viewers.length; i++) {
    spreadsheet.removeViewer(viewers[i]);
  }








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




  return totalCount;
}








function revokeAccessDoc() {
  // Get the active Google Docs document
  var document = DocumentApp.getActiveDocument();








  // Get the list of editors and viewers
  var editors = document.getEditors();
  var viewers = document.getViewers();








  // Remove access for editors
  for (var i = 0; i < editors.length; i++) {
    document.removeEditor(editors[i]);
  }








  // Remove access for viewers
  for (var i = 0; i < viewers.length; i++) {
    document.removeViewer(viewers[i]);
  }








  // Calculate the total count of users whose access was revoked
  var totalCount = editors.length + viewers.length;








  // Log the result
  Logger.log('Access revoked for ' + totalCount + ' users.');




sendEmail(totalCount);
  // Return the total count
  return totalCount;
}








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