In this article we will show you how to create a google sheets approval workflow in just a few simple steps. Perfect for purchase orders, expenses, employee time off and more.
It is best to use a form for receiving approval requests. We can use Google Forms for it, and we can create one automatically connected to Google Sheets. To do so, go to Google Sheets, then create a new spreadsheet.
In the main toolbar, click Tools, then select Create a new form.
A new tab will appear, containing a new Google Forms form.
You will get a default blank form at the start.
To start adding new questions to the form, click the Add question icon on the right side.
Keep adding questions and then modifying them to suit what you need to have in an approval request form. Once you are done, you can now close the tab.
Back in Google Sheets, we will modify it a bit by adding a column for the approval drop-down box. It won’t affect the form in any way so we can position it closer to the first column. For our example, we will insert it in the column after the Timestamp (this column is automatically added by Google Forms to the attached Google Sheets spreadsheet.)
One way is to right-click the header of the column, then selecting Insert 1 column right.
A new blank column will appear. We can add a label to it now:
Select the whole column by clicking the column label bar (the one with letter A, B, C, etc.). Afterwards, right-click on it, then select Data validation.
The Data validation rules sidebar will appear on the right side of Google Sheets. CLick Add rule. Do the following:
Once you have added all the criteria, click Done.
All the cells in the sheet now have the drop-down box!
This step is important so we can use Lido to automate sending approval emails. Click the Share button on the upper-right.
The box containing sharing settings will appear. By default, the General access settings is set to Restricted by default. This means that only users listed under People with access can open the spreadsheet via the link you can copy from the sharing settings.
We change the general access options. Click the arrow besides Restricted. You will get a drop-down box with two options: Restricted (set as default) and Anyone with the link. Select Anyone with the link.
You will get the Access updated notification. Click Done.
Next, we will integrate the Google Sheets spreadsheet we made with Lido, and Lido will handle the task of sending approval emails to those who got approved. If you don't already have an account, you can create one here: www.lido.app/go/signup
Here are the steps:
Go to the upper-left corner of Lido, then click Connect Data.
A large box listing the platforms you can integrate with Lido will appear. Select Google Sheets.
You will be asked to paste the spreadsheet URL. Then click Next.
If it's your first time connecting a Google Sheet to Lido, you will be taken to a screen to connect your Google Account. Make sure you connect an account that has access to the Google Sheet you are connecting to. Make sure that you give Lido permissions to access your Google Sheets by checking this box.
.png)
Lido will list the sheets and columns that are present in the Google Sheet you are connecting. By default, all the columns are selected. Click Add Data.
A new sheet will appear with the Google Sheet synced. If you add new records to the google sheet, they will appear in Lido if you simply hit refresh in the toolbar.
Next, we create our email approval templates in a new worksheet. Make these dynamic by using [@column] to reference your table data as variables.
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. Make a new computed column.
This is a column type that applies a formula to every row in a table. We will use Lido's STRINGTEMPLATE formula to replace our email template variables with the actual data from each row.
=STRINGTEMPLATE(template_cell)
In our example, the formula for subject is:
```=STRINGTEMPLATE(Sheet1!$B$1)```
And the formula for body is:
```=STRINGTEMPLATE(Sheet1!$B$2)```
You should now have two new columns: Subject and Body.
We are now ready to add the formula for sending the approval email to Google Sheets.
The formula to send an email in Lido is =SENDGMAIL(sender, recipient, subject, body, status)
sender: the email address you will send emails from
recipient: the email address that will receive the emails
subject: the email subject
body: the email body
status: the location of a spreadsheet cell that will be updated with the status of whether the email has been sent
In this example the formula to enter into G2 is:
```=SENDGMAIL("your@email.com", C2, E2, F2, H2)```
This should create another computed column for SENDGMAIL formulas.
SENDGMAIL is an action formula. This means that it needs to be triggered in order for an email to send. To trigger an email, right click on any SENDGMAIL cell and click Run action.
If this is your first time sending an email in Lido, you will need to login to your Google mail account. Make sure that you log into the same email account that is designated the sender in the SENDGMAIL formula. After an email has been successfully sent, you will see it briefly in the cell and "success" will be added to the cell you specified as the status_cell for the formula (see: cell H2).
First, create a Linked Column in column H for status.
Name this column Status. This column will now store the statuses of our SENDGMAIL formulas after they run.
Now we need to write our trigger logic. We want our emails to send only if:
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 for these conditions.
=IF(AND(approved = TRUE, status <> "success"), SENDGMAIL("your@email.com", "recipient", "subject,", "body", "status))
This formula says that if the approved cell is marked TRUE and also an email has not been sent, display the SENDGMAIL formula. Otherwise, display FALSE.
Our SENDGMAIL formula in column G now becomes:
```=IF(AND(A2=TRUE, H2<>"success"), SENDGMAIL("your@email.com", C2, E2, F2, H2))```
Remember that if you are following along, you may need to update your cell references to fit your data!
To test this, change a value in your approval column in Google Sheets to TRUE. Then refresh your data in Lido. Notice how that row now displays a SENDGMAIL formula that is ready to be triggered, but all of the other rows do not.
Now we are ready to automate our spreadsheet. You can automate an entire column in Lido. When a column automation runs in Lido, it triggers all of the action formulas in the column at that time. This is why it was important to add our IF statement in the prior step.
Click on the menu for the column that you want to automate and select Run column on a schedule. This will open the automation panel.
.png)
The Edit Automation sidebar will load on the right side of the sheet. Create a name for the automation, then set the schedule Every 5 Minutes. Click Save afterwards.
You are now done! You can now close Lido.
Now, whenever you approve someone’s request, they will get the approval a few minutes later.
