Approval Process with Email Notification using Google Sheets

nightwolfdev - Jan 7 '21 - - Dev Community

Imagine collecting requests on a Google sheet and wanting a way to process those requests by moving them to another sheet. At the same time, you’d like to email the users letting them know their requests were approved. Let’s learn how to set up an approval process with email notifications using Google Sheets!

Spreadsheet Setup

Submitted Sheet

Submitted Sheet

  1. Create a sheet called Submitted.
  2. Create four columns:
    • Timestamp
    • First Name
    • Last Name
    • Email Address

Approved Sheet

Approval Sheet

  1. Select the Submitted sheet’s menu and choose Duplicate.
  2. Rename the sheet to Approved.

Email Settings Sheet

Email Settings Sheet

  1. Create a sheet called Email Settings.
  2. In cell A1, let’s create a label called Subject.
  3. In cell B1, enter the text for the subject of the email sent to the user when the request is approved.
  4. In cell A2, let’s create a label called Body.
  5. In cell B2, enter the text for the body of the email sent to the user when the request is approved.

Script Editor

Script Editor

Let’s start writing some code! Google Sheets has a handy script editor available.

  1. Navigate to Tools > Script Editor.
  2. The script editor will include a starting function. You can remove all the code.
  3. Navigate to File > Save. Give the script project a name and select Ok.

Approval Process

Create a function called approve. This function will be used to move rows from the Submitted sheet to the Approved sheet as well as sending the emails.

function approve() {

}
Enter fullscreen mode Exit fullscreen mode

Let’s continue to add code to the function. We’re going to set up some variables we’ll need for the process.

Variables

Create a variable called spreadsheet. This gets the active spreadsheet and returns a Spreadsheet object, which will provide additional functions we’ll need.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
Enter fullscreen mode Exit fullscreen mode

Create a variable called activeSheet. This gets the active sheet and returns a Sheet object, which will provide additional functions we’ll need.

var activeSheet = spreadsheet.getActiveSheet();
Enter fullscreen mode Exit fullscreen mode

Create a variable called activeSheetName. This gets the active sheet’s name. We’ll use this to make sure you’re on the Submitted sheet when running the approve process.

var activeSheetName = activeSheet.getName();
Enter fullscreen mode Exit fullscreen mode

Create a variable called approvedSheet. This gets the Approved sheet so we can move data to it.

var approvedSheet = spreadsheet.getSheetByName('Approved');
Enter fullscreen mode Exit fullscreen mode

Create a variable called approvedSheetLastRow. This gets the last row number that contains data in the Approved sheet. When we move in data, we don’t want to overwrite anything that’s already there. This helps us know where to start.

var approvedSheetLastRow = approvedSheet.getLastRow();
Enter fullscreen mode Exit fullscreen mode

Create a variable called emailSettingsSheet. This gets the Email Settings sheet so we can get some information from it.

var emailSettingsSheet = spreadsheet.getSheetByName('Email Settings');
Enter fullscreen mode Exit fullscreen mode

Create a variable called emailSubject. This gets the text used for the subject of the email sent to the user when the request is approved.

var emailSubject = emailSettingsSheet.getRange('B1').getValue();
Enter fullscreen mode Exit fullscreen mode

Create a variable called emailBody. This gets the text used for the body of the email sent to the user when the request is approved.

var emailBody = emailSettingsSheet.getRange('B2').getValue();
Enter fullscreen mode Exit fullscreen mode

Create a variable called dataRange. This gets the range of data present in the active sheet.

var dataRange = activeSheet.getDataRange();
Enter fullscreen mode Exit fullscreen mode

Create a variable called data. This gets the actual data from the range in the active sheet.

var data = dataRange.getValues();
Enter fullscreen mode Exit fullscreen mode

Create a variable called selectedRange. We’ll use this to check if the user selected the whole row(s), which will be required prior to approving the request(s).

var selectedRange = activeSheet.getActiveRange();
Enter fullscreen mode Exit fullscreen mode

Create a variable called selectedData. This will be the data we’re approving and moving from the Submitted sheet to the Approved sheet.

var selectedData = selectedRange.getValues();
Enter fullscreen mode Exit fullscreen mode

Create a variable called startRow. This will tell us where to start removing data from the Submitted sheet after it was moved to the Approved sheet.

var startRow = selectedRange.getRowIndex();
Enter fullscreen mode Exit fullscreen mode

Create a variable called numRows. This will tell us how many rows to move to the Approved sheet and remove from the Submitted sheet.

var numRows = selectedRange.getNumRows();
Enter fullscreen mode Exit fullscreen mode

Create a variable called numCols. We’ll use this to check if the user selected the whole row(s).

var numCols = selectedRange.getNumColumns();
Enter fullscreen mode Exit fullscreen mode

Logic

The user can only approve requests on the Submitted sheet. So let’s check to make sure they are on that sheet. If they aren’t, we’ll display a helpful message letting them know.

if (activeSheetName !== 'Submitted') {

  // If you're not on the Submitted sheet when trying to approve requests, show message.
  spreadsheet.toast('You can only approve requests on the Submitted sheet.', 'Process Requests');

}
Enter fullscreen mode Exit fullscreen mode

Let’s force the user to select the whole row(s) in order to approve them. If the number of columns selected is less than the last column number having data, we know they didn’t select the whole row. We’ll display a helpful message letting them know.

if (numCols < dataRange.getLastColumn()) {

  // If the user didn't select the whole row(s), show message.
  spreadsheet.toast('Please select the whole row(s).', 'Process Requests');

}
Enter fullscreen mode Exit fullscreen mode

You can’t approve something that doesn’t exist right?! If the user tries to approve requests when there’s no rows of data, let’s display a helpful message letting them know.

if (data.length === 0) {

  // If there is no data in the sheet, show message.
  spreadsheet.toast('There are no requests to process.', 'Process Requests');

}
Enter fullscreen mode Exit fullscreen mode

Ok we’re ready to copy the selected row(s) to the Approved sheet. We need to specify where to write the rows. So we get the range by starting on the row after the last row of data currently there. We start at column 1. We take up however many rows of data we’re moving. We also take up however many columns of data we’re moving. We set the values of the range to the selected rows of data.

// Copy rows to Approved sheet.
approvedSheet.getRange(approvedSheetLastRow + 1, 1, numRows, dataRange.getLastColumn()).setValues(selectedData);
Enter fullscreen mode Exit fullscreen mode

Now that the selected row(s) have moved to the Approved sheet, let’s remove them from the Submitted sheet. We know where the first selected row is. We also know how many rows. Pass that into the function to delete the rows from the Submitted sheet.

// Remove rows from Submitted sheet.
activeSheet.deleteRows(startRow, numRows);
Enter fullscreen mode Exit fullscreen mode

Send Email

Time to send out those email notifications. Keep in mind there is a limit on how many emails can be sent per day.

Loop through each of the selected rows and if there’s an email address and you still have quota for the day, send the email with the subject and body coming from the Email Settings sheet.

for (var i = 0; i < selectedData.length; i++) {
  var row = selectedData[i];
  var emailAddress = row[3];

  // If there's an email defined and you haven't surpassed your daily quota, send the email.
  if (emailAddress &amp;&amp; MailApp.getRemainingDailyQuota() > 0) {
    MailApp.sendEmail(emailAddress, emailSubject, emailBody);
  }
}
Enter fullscreen mode Exit fullscreen mode

All that’s left to do is let the user know the process is complete!

// When approval process is complete, show message.
spreadsheet.toast('The request(s) have been approved.', 'Process Requests');
Enter fullscreen mode Exit fullscreen mode

Custom Menu

Instead of running the approve function from the script editor, let’s create a custom menu so we can run it from the spreadsheet’s menu toolbar instead!

The best time to create a custom menu is when the spreadsheet first opens. Use the onOpen trigger, which is executed when the spreadsheet is first opened.

Add a custom menu to the spreadsheet called Process Requests. Selecting the Process Requests menu will display a menu option called Approve. Selecting Approve will run the function called approve!

// Create custom menu when spreadsheet opens.
function onOpen() {
  var options = [
    { 
      name: 'Approve',
      functionName: 'approve'
    }
  ];

  SpreadsheetApp.getActiveSpreadsheet().addMenu('Process Requests', options);
}
Enter fullscreen mode Exit fullscreen mode

Final Code 

// Create custom menu when spreadsheet opens.
function onOpen() {
  var options = [
    { 
      name: 'Approve',
      functionName: 'approve'
    }
  ];

  SpreadsheetApp.getActiveSpreadsheet().addMenu('Process Requests', options);
}

// Move rows from Submitted to Approved and send email.
function approve() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var activeSheet = spreadsheet.getActiveSheet();
  var activeSheetName = activeSheet.getName();
  var approvedSheet = spreadsheet.getSheetByName('Approved');
  var approvedSheetLastRow = approvedSheet.getLastRow();
  var emailSettingsSheet = spreadsheet.getSheetByName('Email Settings');
  var emailSubject = emailSettingsSheet.getRange('B1').getValue();
  var emailBody = emailSettingsSheet.getRange('B2').getValue();
  var dataRange = activeSheet.getDataRange();
  var data = dataRange.getValues();
  var selectedRange = activeSheet.getActiveRange();
  var selectedData = selectedRange.getValues();
  var startRow = selectedRange.getRowIndex();
  var numRows = selectedRange.getNumRows();
  var numCols = selectedRange.getNumColumns();

  if (activeSheetName !== 'Submitted') {

    // If you're not on the Submitted sheet when trying to approve requests, show message.
    spreadsheet.toast('You can only approve requests on the Submitted sheet.', 'Process Requests');

  } else {

    if (numCols < dataRange.getLastColumn()) {

      // If the user didn't select the whole row(s), show message.
      spreadsheet.toast('Please select the whole row(s).', 'Process Requests');

    } else {

      if (data.length === 0) {

        // If there is no data in the sheet, show message.
        spreadsheet.toast('There are no requests to process.', 'Process Requests');

      } else {

        // Copy rows to Approved sheet.
        approvedSheet.getRange(approvedSheetLastRow + 1, 1, numRows, dataRange.getLastColumn()).setValues(selectedData);

        // Remove rows from Submitted sheet.
        activeSheet.deleteRows(startRow, numRows);

        for (var i = 0; i < selectedData.length; i++) {
          var row = selectedData[i];
          var emailAddress = row[3];

          // If there's an email defined and you haven't surpassed your daily quota, send the email.
          if (emailAddress &amp;&amp; MailApp.getRemainingDailyQuota() > 0) {
            MailApp.sendEmail(emailAddress, emailSubject, emailBody);
          }
        }

        // When approval process is complete, show message.
        spreadsheet.toast('The request(s) have been approved.', 'Process Requests');

      }

    }

  }
}
Enter fullscreen mode Exit fullscreen mode

Visit our website at https://nightwolf.dev and follow us on Facebook and Twitter!

Image
teknik aman dalam judi slots online
Image
Mastering Algorithms: Essential Concepts Every Computer Engineering Student Should Know

algorithms, datastructures, dsa, learning
Image
Yang memicu Kekalahan Waktu Taruhan Judi Online
Image
Happy diwali video animation using html css and js https://www.instagram.com/webstreet_code/

webdev, javascript, beginners, programming
Image
Panduan Mendapat Bonus Referal Agen Judi Online
Image
Learning Goes Hand-in-Hand with Building: My Journey from JavaScript Basics to Real-World Projects.
Image
PHP + ip2location = PHPFirewall

php, ip2location, firewall
Image
20 Essential React Tricks Every Developer Must Know

webdev, javascript, react, reactnative
Image
Diwali animation using html ,css and javascript https://www.instagram.com/webstreet_code/

webdev, javascript, beginners, programming
Image
Server Side Mocking for Playwright in NextJS (App Router) using Mock Service Worker

msw, playwright, mocking, nextjs
Image
This moment.

beginners
Image
🌐 Golang RESTful API with Gin, Gorm, Redis Cache 💾

webdev, go, redis, tutorial
Image
sejumlah pokok utama terkait judi slots online
Image
What You Can Do To Get More From Your Asbestos Litigation Paralegal
Image
The Best Asbestos Lawyer Tricks To Rewrite Your Life
Image
10 Things You've Learned From Kindergarden To Help You Get Started With Drip Coffee
Image
Waktu Yang Sesuai Buat Stop Main Judi Online
Image
Getting Approved For A Car Loan With Bad Credit History
Image
++VIRAL-VIDEO)™ Dolphin Ayan Viral Video 2024
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .