In this article:

Google Sheets Approval Workflow [Easiest Way in 2024]

May 8, 2024

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.

How to Create a Google Sheets Approval Workflow

1. Click Tools then Select Create a new form

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.

Google Sheets Approval Workflow

In the main toolbar, click Tools, then select Create a new form.

Create an approval workflow in google sheets

A new tab will appear, containing a new Google Forms form.

2. Build a Form for Approval Requests to be Sent 

You will get a default blank form at the start.

Setting up a google sheets approval workflow

To start adding new questions to the form, click the Add question icon on the right side.

Add question to google forms

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. 

Completed google form for approval workflow

3. Insert a Drop-down Box for Approval

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

Insert 1 column to sheet

A new blank column will appear. We can add a label to it now:

Add name to inserted column

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

Right click column, drop down box, data validation‍

The Data validation rules sidebar will appear on the right side of Google Sheets. CLick Add rule. Do the following:

  • Modify the range by editing it to make sure it doesn’t include the first cell in the column. For the example shown, It is done by changing B1 to B2 in the range. 
  • By default, there are already two choices that we can specify. Add their labels. You can also set color to them so it is easier to see in the spreadsheet what option you select in the drop-down box. 
  • If you need more options, just click Add another item

Data validation rules sidebar

Once you have added all the criteria, click Done. 

All the cells in the sheet now have the drop-down box!

Column with drop down box set‍

4. Set General Access Option of the Sheet to Anyone with the Link 

This step is important so we can use Lido to automate sending approval emails. Click the Share button on the upper-right. 

Google sheet share  button

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. 

Google sheets Sharing options

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

Changing general access settings to anyone with the link

You will get the Access updated notification. Click Done

Access settings updated‍

Send an Email on Approval with Lido (Easiest Way)

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:

1. Click Connect Data 

Go to the upper-left corner of Lido, then click Connect Data.

Lido connect data button‍

A large box listing the platforms you can integrate with Lido will appear. Select Google Sheets

Add data from different platforms‍

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.

how to automatically send emails from google sheets

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

Add data, select columns to import

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.

2. Create Approval Email Templates for Subject and Body

Next, we create our email approval templates in a new worksheet. Make these dynamic by using [@column] to reference your table data as variables.

3. Create Email subject and body columns

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.

4. Add another column to send emails

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.

5. Run your SENDGMAIL formula

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


6. Add trigger logic for SENDGMAIL

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:

  1. The request has been approved
  2. We have not 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 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.

7. Automate running your SENDGMAIL column

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.

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. 

Sample approval email sent from Lido

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->