At the end of this tutorial we will have a workflow where:
While our example is for vacation requests, you can easily use the same steps to create almost any approval flow.
Go to google forms and create a new form. Add the questions that you want to collect to determine whether or not to grant their request.
Once you are happy with your questions, click Send in the upper right corner to share your form.
We will collect responses to our Google Form in a Google Sheet. In the Google form, click on Responses. Then click on Link to Sheets.
Select Create a new spreadsheet. Then click Create.
You will be automatically redirected to the newly created Google Sheet. When new responses are submitted via Google Forms, they will be added to your Google Sheet.
Now we need to add a column for our managers to approve or deny the requests.
In the same tab, create a new column at the end of your Google Sheet for Approval Status.
Next, select the cells in the new Approval Status column. Then right click and select Dropdown.
This will open a panel to create dropdowns in the cells. We will make two options: Approved and Denied.
Then, click Done to add.
We now have the ability to select Approved or Denied for each new form submission.
Now we need to automate sending an email when new requests are approved or denied. There is no functionality to do this in Google Sheets, so we will use Lido for this step. Lido is a spreadsheet that automates work for you. You can create a free account here: www.lido.app/go/signup
Copy the URL from your Google Sheet. Then make a new file in Lido.
Click Connect Data, then select Google Sheets.
Paste in your Google Sheets URL and click next.
If it's your first time connecting to Lido, you'll be asked to log into a Google Account with access to the Google Sheet. Make sure you check the box that gives Lido permission to access your spreadsheets.
In the next screen you will be asked to select which columns from the Google Sheet to connect to Lido. In this example we will just select all of them and click Add data.
Your data should now be visible in Lido in a table.
These tables are connected to Google Sheets, so as new approval requests are submitted they will populate in Lido automatically. You can always click the Refresh button in the toolbar to fetch the latest data.
Create a new worksheet. We will write our email approval templates in this new sheet. You can use column names as variables inside of emails by using the syntax [@column]. When we connect these to our response data in the next step, these [@column] references will become dynamic. These are your template cells that we reference later.
Next, go back to your table. Create a computed column. This is a column type in Lido that applies the same spreadsheet formula to every row in a table. Type a column name in the first row of a cell next to your table (cell G1 in this example) to create a new column, then select Computed Column as the type.
Inside of the computed column we will use Lido's STRINGTEMPLATE formula to replace the variable placeholders from the prior step with the actual content of each row. The formula for STRINGTEMPLATE is:
=STRINGTEMPLATE(“template_cell_location”)
In our example, our template cell for email subject is located in Sheet1!$B$1. So our formula for cell G2 of the computed column is:
```=STRINGTEMPLATE(Sheet1!$B$1)```
Create a second computed column, and replace the same steps for email body. Make sure to change the template_cell_location to the location of the email body. In our example, this is Sheet1!$B$2. Remember to anchor the cell reference with $.
```=STRINGTEMPLATE(Sheet1!$B$2)```
Your table should now look like this. Notice how in the email body column, the [@Name] reference from our template cell has now been replaced with the actual Name in the row.
Now we are ready to set up our SENDGMAIL formula. This is an Action formula in Lido, which means that it only runs (e.g. sends an email) when it is explicitly triggered, either manually or via an automation.
The formula for SENDGMAIL is:
=SENDGMAIL(“sender@example.com”, “recipient@example.com”, “subject”, “body”, “status”)
Create another computed column for “Send Email”. In this example the new computed column will be in column J. Then, in cell J2, write the following formula:
```=SENDGMAIL("your@email.com", G2, H2, I2, K2)```
Where:
your@email.com is the email address that will send the emails. Make sure that this is a Gmail account you have access to.
G2 is the recipient email. Make sure that you change this to match the order of your
H2 is the Email subject
I2 is the Email body
K2 is an empty cell in the next column where we will place the status of whether an email has been sent
Your table should look like this now:
To test this action, right click on a SENDGMAIL formula and select “Run action.”
You will be prompted to login to the sender email the first time. After the email is sent, you'll notice a "success" message briefly appear in the cell. The cell you designated as the status cell (K2 in this example) will also be updated to display "success."
In column K (or whichever column held your SENDGMAIL status_cell in the previous step), create a new Linked column for "Email Sent Status".
You'll be prompted to choose a unique ID column for the linked column. This is how Lido keeps track of which emails have been sent, so it's important to choose an ID column with non-repeating and non-blank values. Timestamp is a good unique ID column.
Now we are ready to configure our logic. We want to send an email when two conditions are met:
We can account for both of these using an IF() statement in our Send email column.
Go back to your Send email computed column and update it to check for both of these conditions:
```=IF(AND(K2<>"success", F2<>""), SENDGMAIL("your@email.com", G2, H2, I2, K2))```
Make sure you update this formula to match your data.
K2<>"success" : K2 should be the same as the status_cell in your SENDGMAIL formula. This checks for whether an email has been sent already
F2<>"" : F2 should be the cell in your data for Approval status. This is the cell that changes when something has been approved or denied. This formula checks for whether F2 is not blank. If it's been populated, then we want an email to trigger.
Your table should now look like this. Notice how in Row 2, there is no SENDGMAIL formula because an email has already been sent (K2 says "success"). However, there is an action formula in row 3 because Approval status is Approved (Cell F3) but no email has been sent (K3 is blank).
All that is left is to automate our SENDGMAIL column so that the emails are automatically triggered as new rows get added.
Click in the 3-dot menu for your Send email column and select Run column on a schedule.
This will open the automation panel. In this screen, you will need to select an interval for how frequently you want Lido to check for updates.
Every time an automation runs, Lido first fetches the newest data from your Google Sheet, which is connected to your Google Form, then triggers all of the SENDGMAIL actions in the column.
We will choose a 15 minute automation interval in this example. Then hit save.
Your automation is now running! When the approval status is changed in your Google Sheet, Lido will automatically send an email to the requestor to communicate whether the request was approved or denied. You now have a fully automated Google Forms approval workflow.
