In this article, we will show you how to create an approval workflow with Google Sheets in just a few simple steps. Perfect for purchase orders, expenses, employee leave requests, and much more.
How to Create an Approval Workflow with Google Sheets
1. Click on Tools, then select Create a new form
It’s best to use a form to receive approval requests. We can use Google Forms for this and automatically create a form connected to Google Sheets. To do this, go to Google Sheets, then create a new spreadsheet.
In the main toolbar, click on Tools, then select Create a new form.
A new window will open, containing a new Google Forms form.
2. Build a form to submit approval requests
You will start with a blank form by default.
To begin adding new questions to the form, click on the Add a question icon on the right side.
Continue to add questions and customize them to fit your needs in an approval request form. Once done, you can now close the tab.
3. Insert a dropdown list for approval
Back in Google Sheets, we will modify it by adding a column for the approval dropdown list. This will not affect the form in any way, so we can position it closer to the first column. In our example, we will insert it in the column after the timestamp field (this column is automatically added by Google Forms to the associated Google Sheets spreadsheet).
One way is to right-click on the column header, then select Insert 1 right column.
A new blank column will appear. We can add a label to it now:
- Select the entire column by clicking on the column label bar (the one with the letters A, B, C, etc.). Then, right-click on it and select Data validation.
The Data validation rules bar will appear on the right side of Google Sheets. Click on Add a rule and perform the following steps:
- Edit the range by ensuring it doesn’t include the first cell of the column. For the given example, this is done by replacing B1 with B2 in the range.
- By default, there are already two choices that we can specify. Add their labels. You can also assign them a color to make it easier to see in the spreadsheet the option you select from the dropdown list.
- If you need more options, simply click on Add another item.
Once you have added all the criteria, click on Done.
Now, all the cells in the sheet have the dropdown list!
4. Set general sheet access options to “Anyone with the link can access”
This step is important so that we can use Lido to automate the sending of approval emails. Click on the Share button in the top right corner.
The box containing the sharing settings will appear. By default, the general access settings are set to Restricted. This means that only users listed in People with access can open the spreadsheet via the link which you can copy from the sharing settings.
We will modify the general access options. Click on the arrow next to Restricted. You will get a dropdown list with two options: Restricted (set by default) and Anyone with the link can access. Select Anyone with the link can access.
You will receive the notification Access updated. Click on Done.
Send an Approval Email with Lido (The Easiest Method)
Next, we will integrate the Google Sheets spreadsheet we created with Lido, and Lido will take care of sending approval emails to those who have been approved. If you don’t have an account yet, you can create one here.
Here are the steps:
1. Click on Connect Data
Go to the top left corner of Lido, then click on Connect Data.
A big box listing the platforms that you can integrate with Lido will appear. Select Google Sheets.
You will be asked to paste the URL of the spreadsheet. Then, click on Next.
If it’s your first time connecting a Google Sheets spreadsheet to Lido, you will be directed to a page to connect your Google account. Make sure to connect an account that has access to the Google Sheets spreadsheet you’re connecting to. Also, make sure to give Lido the necessary permissions to access your Google Sheets by checking this box.
Lido will display the sheets and columns present in the Google Sheets spreadsheet you’re connecting to. By default, all columns are selected. Click on Add data.
A new sheet will appear with the synchronization of the Google Sheets spreadsheet. If you add new data to the Google Sheets spreadsheet, it will appear in Lido simply by refreshing the toolbar.
2. Create approval email templates for the subject and body of the email
Next, we will create our approval email templates in a new spreadsheet. Make them dynamic by using [@column] to reference the data from your table as variables.
3. Create columns for the email subject and body
Now, we need to add columns to our table that reference the email subject and body templates we just created.
Go back to your table. Add a new calculated column.
This is a column type that applies a formula to each row of a table. We will use Lido’s STRINGTEMPLATE formula to replace the variables in our email templates with the actual data from each row.
=STRINGTEMPLATE(template_cell)
In our example, the formula for the subject is:
=STRINGTEMPLATE(Sheet1!$B$1)
And the formula for the body is:
=STRINGTEMPLATE(Sheet1!$B$2)
You should now have two new columns: Subject and Body.
4. Add another column to send the emails
We are now ready to add the formula to send the approval email in Google Sheets.
The formula to send an email in Lido is =SENDGMAIL(sender, recipient, subject, body, status)
- sender: the email address from which you will send the emails
- recipient: the email address that will receive the emails
- subject: the email subject
- body: the email body
- status: the location of a cell in the spreadsheet that will be updated with the status of sending the email
In this example, the formula to enter in cell G2 is:
=SENDGMAIL("your@email.com", C2, E2, F2, H2)
This will create another calculated column for the SENDGMAIL formulas.
5. Execute your SENDGMAIL formula
SENDGMAIL is an action formula. This means it needs to be triggered to send an email. To trigger the sending of an email, right-click on any SENDGMAIL cell and click Run action.
If it’s your first time sending an email in Lido, you will need to log into your Google email account. Make sure to log in with the same email address designated as the sender in the SENDGMAIL formula. After a successful email sending, you will briefly see it in the cell, and “success” will be added to the cell you specified as the status_cell for the formula (see: cell H2).
6. Add triggering logic for SENDGMAIL
First, create a linked column in column H for the status.
Name this column Status. This column will now store the statuses of our SENDGMAIL formulas after they are executed.
Now, we need to write our triggering logic. We want our emails to be sent only if:
- The request has been approved
- We haven’t already sent an email
We need to update our SENDGMAIL formula in column G to account for this. We will use an IF statement in combination with an AND statement to check these conditions.
=IF(AND(approved = TRUE, status <> "success"), SENDGMAIL("your@email.com", "recipient", "subject,", "body", "status"))
This formula states that if the approved cell is marked as TRUE and an email has not been sent yet, display the SENDGMAIL formula. Otherwise, display FALSE.
So, our SENDGMAIL formula in column G becomes:
=IF(AND(A2=TRUE, H2<>"success"), SENDGMAIL("your@email.com", C2, E2, F2, H2))
Remember to update your cell references to match your data if you follow these steps!
To test this, change a value in your approval column in Google Sheets to mark it as TRUE. Then, refresh your data in Lido. Notice how this row now displays a SENDGMAIL formula ready to be triggered, but all other rows do not.
7. Automate the execution of your SENDGMAIL column
Now, we’re ready to automate our spreadsheet. You can automate an entire column in Lido. When a column automation runs in Lido, it triggers all action formulas in the column at that time. That’s why it was important to add our IF statement in the previous step.
Click on the column menu you want to automate and select Run column on a schedule. This will open the automation panel.
The Edit Automation sidebar will open on the right side of the spreadsheet. Create a name for the automation, then set the schedule to Every 5 minutes. Click Save afterward.
You’re done now! You can close Lido.
From now on, whenever you approve a request, the person will receive the approval email a few minutes later.
