Putting in Place a Two-Step Google Sheets Email Notification System for Approval

Automating Approval Notifications in Spreadsheet Workflows

Operational operations can be greatly impacted by the effectiveness of approval processes in the fast-paced business environments of today. Because Google Sheets is so accessible and flexible, a lot of businesses use it to handle activities like approving requests. The implementation of an automated system for these procedures is sometimes fraught with difficulties, particularly when a two-step approval mechanism is involved. When both preliminary and final approvals are given, this system requires an automated email to be sent to the IT department, provided that the request status changes to "approved."

Nevertheless, there is a unique difficulty in using Google Apps Script to automate this operation. Only modifications made by direct user interaction cause the built-in "onEdit" trigger, which is essential for starting the email dispatch, to activate. modifications done programmatically do not. When a script is used to update the status from "pending" to "approved," this restriction presents a substantial challenge. In order to ensure timely communication and process efficiency, this introduction lays the foundation for investigating ways to smoothly incorporate automatic email notifications into an approval workflow based on Google Sheets.

Command	Description
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approvals")	Gets the "Approvals" sheet from the active spreadsheet.
getDataRange()	Retrieves all of the sheet's data as a range.
getValues()	Gives back a two-dimensional array containing the values of the cells in the range.
MailApp.sendEmail(email, subject, body)	Sends an email to the recipient, topic, and body that are supplied.
emailSentColumn + 1 + i + sheet.getRange( 1).setValue("sent")	Indicates that an email has been sent by setting the value of a certain cell to "sent".
google.script.run	Uses a web application to invoke a Google Apps Script function.
withSuccessHandler(function())	Indicates the action to take in the event that the google.script.run call is successful.
withFailureHandler(function(err))	If the google.script.run call is unsuccessful, this method should be called, sending the error as an argument.
updateStatusInSheet(approvalId, status)	An additional function in the code snippet that updates the status of an approval request in the spreadsheet is a custom Google Apps Script function.

Understanding the Automated Email System

The main goal of the automated email trigger system I created for Google Sheets is to expedite the approval process inside businesses, especially in situations when several approvers must agree before moving forward. The Google Sheets where approval statuses are kept is directly accessed by the Google Apps Script-crafted first portion of the solution. The script searches every row on the "Approvals" page for rows where approvers 1 and 2 have both checked the box next to "approved." This is important since the script is designed to run only in the event that both approvals are granted, indicating that the request is completely permitted. In order to accomplish this, the script goes through each row iteratively, looking at the particular columns that correspond to the decisions made by each approver as well as the request's general status. The script sends an email to the IT department when a row matches the requirements—both approvers have approved, and the status is set to "approved". The MailApp service, a feature of Google Apps Script that enables email sending straight from the script, is used to send this email notification. It guarantees that the authorized request is communicated to the IT department in a timely manner, enabling rapid response.

The frontend equivalent of the automated email system is the web application mechanism for changing the approval status. This part is particularly important because Google Sheets' "onEdit" trigger only reacts to manual edits—not programmed modifications. A straightforward web interface enables users to modify the approval request's status in order to get around this restriction. When user interaction occurs, like clicking a button to designate a request as "approved," the web application uses the `google.script.run} command to invoke a Google Apps Script function. This command is quite strong since it allows the script to simulate human edits by acting on inputs via the online interface in the Google Sheet. After then, the script can continue to send emails as intended and check for changes, filling in the gap left by the constraints of the "onEdit" trigger. This two-part approach makes sure that the approval procedure is effective and flexible, meeting the requirements for both automated and manual workflow interventions.

function checkApprovalsAndSendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Approvals");
  var range = sheet.getDataRange();
  var values = range.getValues();
  var emailSentColumn = 5; // Assuming the fifth column tracks email sending status
  var approver1Column = 2; // Column for approver 1's status
  var approver2Column = 3; // Column for approver 2's status
  var statusColumn = 4; // Column for the overall status
  for (var i = 1; i < values.length; i++) {
    var row = values[i];
    if (row[statusColumn] == "approved" && row[emailSentColumn] != "sent") {
      if (row[approver1Column] == "approved" && row[approver2Column] == "approved") {
        var email = "it@domain.com";
        var subject = "Approval Request Completed";
        var body = "The approval request for " + row[0] + " has been fully approved.";
        MailApp.sendEmail(email, subject, body);
        emailSentColumn + 1 + i + sheet.getRange( 1).setValue("sent");
      }
    }
  }
}

<!DOCTYPE html>
<html>
<head>
<title>Approval Status Updater</title>
</head>
<body>
<script>
function updateApprovalStatus(approvalId, status) {
  google.script.run
    .withSuccessHandler(function() {
      alert('Status updated successfully.');
    })
    .withFailureHandler(function(err) {
      alert('Failed to update status: ' + err.message);
    })
    .updateStatusInSheet(approvalId, status);
}
</script>
<input type="button" value="Update Status" onclick="updateApprovalStatus('123', 'approved');" />
</body>
</html>

Increasing Workload Productivity using Spreadsheet Automation

One clever way to improve organizational procedures is to automate email notifications in Google Sheets as a part of a two-step approval process. Manual interventions have been a standard practice in approval sequences historically, necessitating human activities to advance processes. Nevertheless, we shift to a paradigm where these interventions are reduced to a minimum, resulting in increased productivity and decreased error rates, by utilizing Google Apps Script. This change expedites the approval process in its entirety and guarantees that notifications are sent at the appropriate point, that is, after a request has been granted by both parties involved, as shown by the request's status changing to "approved."

This method avoids the constraints of the "onEdit" trigger and emphasizes the importance of programmatically managed status changes within the spreadsheet. Through the use of a customized script that monitors status changes and triggers email notifications in response, businesses may get around the manual bottleneck and automate a vital part of their operational routine. This methodological shift allows for a more dynamic and responsive workflow management system by streamlining the clearance process and introducing a level of scalability and adaptability that was previously unreachable through manual processes.

Investigating Google Sheets' automated email triggers for a two-step approval procedure provides important insights into the drawbacks and possible fixes for optimizing these workflows. Because the default onEdit trigger cannot detect programmed modifications, inventive scripting techniques are required to guarantee that notifications are issued only when approvals are fully verified. This case emphasizes how crucial it is to create tailored Google Apps Script solutions to fill in the holes in Google Sheets' built-in functionality so that more responsive and dynamic approval procedures may be created. Organizations can improve their operational efficiency and communication flow by utilizing Google Apps Script to construct customized triggers and functions. This will guarantee that important stakeholders are notified as soon as approval steps are completed. The conversation emphasizes the need for flexibility in the face of platform constraints and promotes proactive problem-solving strategies for automated systems.