Approval Process with Email Notification using Google Sheets
Tags: googleappsscript, googlesheets, javascript
Published: 2021-01-07 01:14:11

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](https://dev-to-uploads.s3.amazonaws.com/i/ecrzy3wmhtxw2rdr2txr.png)

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

### Approved Sheet

![Approval Sheet](https://dev-to-uploads.s3.amazonaws.com/i/ypcph5eol57r51dcitdm.png)

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

### Email Settings Sheet

![Email Settings Sheet](https://dev-to-uploads.s3.amazonaws.com/i/r6iqzlkrkelj14ibqtks.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/lk49jfh0z5d1xgjga38n.png)

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.

```javascript
function approve() {
 
}
```

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](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet) object, which will provide additional functions we’ll need.

```javascript
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
```

Create a variable called **activeSheet**. This gets the active sheet and returns a [Sheet](https://developers.google.com/apps-script/reference/spreadsheet/sheet) object, which will provide additional functions we’ll need.

```javascript
var activeSheet = spreadsheet.getActiveSheet();
```

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.

```javascript
var activeSheetName = activeSheet.getName();
```

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

```javascript
var approvedSheet = spreadsheet.getSheetByName('Approved');
```

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.

```javascript
var approvedSheetLastRow = approvedSheet.getLastRow();
```

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

```javascript
var emailSettingsSheet = spreadsheet.getSheetByName('Email Settings');
```

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.

```javascript
var emailSubject = emailSettingsSheet.getRange('B1').getValue();
```

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.

```javascript
var emailBody = emailSettingsSheet.getRange('B2').getValue();
```

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

```javascript
var dataRange = activeSheet.getDataRange();
```

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

```javascript
var data = dataRange.getValues();
```

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).

```javascript
var selectedRange = activeSheet.getActiveRange();
```

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

```javascript
var selectedData = selectedRange.getValues();
```

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.

```javascript
var startRow = selectedRange.getRowIndex();
```

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

```javascript
var numRows = selectedRange.getNumRows();
```

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

```javascript
var numCols = selectedRange.getNumColumns();
```

### 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.

```javascript
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');
 
}
```

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.

```javascript
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');
 
}
```

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.

```javascript
if (data.length === 0) {
 
  // If there is no data in the sheet, show message.
  spreadsheet.toast('There are no requests to process.', 'Process Requests');
 
}
```

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.

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

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.

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

### Send Email

Time to send out those email notifications. Keep in mind there is a [limit](https://developers.google.com/apps-script/guides/services/quotas) 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.

```javascript
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);
  }
}
```

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

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

## Custom Menu

Instead of running the approve function from the script editor, let’s create a [custom menu](https://dev.to/nightwolfdev/create-a-custom-menu-in-google-sheets-3nc) 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!

```javascript
// Create custom menu when spreadsheet opens.
function onOpen() {
  var options = [
    { 
      name: 'Approve',
      functionName: 'approve'
    }
  ];
 
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Process Requests', options);
}
```

## Final Code

```javascript
// 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');
 
      }
 
    }
 
  }
}
```

---
#### Visit our website at [https://nightwolf.dev](https://nightwolf.dev) and follow us on [Facebook](https://www.facebook.com/NightWolfDevelopment) and [Twitter](https://twitter.com/nightwolfdev)!
---