How to Trigger for ALL Changes to a Trigger Column in Google Sheets (Sheets Last Modified Timestamp)

  • 17 February 2022
  • 0 replies
  • 710 views
How to Trigger for ALL Changes to a Trigger Column in Google Sheets (Sheets Last Modified Timestamp)
Userlevel 6
Badge

In Zapier - all Polling and Hybrid Triggers use a process called Deduplication to ensure we only trigger for new records added or updated after the Zap is turned on.

For Google Sheets New or Updated Row Triggers - this means we will only trigger the first time we see a value in the Trigger Column.

Let’s assume in my Sheet below - that the Email Column C is my Trigger Column.

jM6iX24N9ECuAT2irXAG-BQ_ylVWxT_jkozQAijCognHlQVpqAOt9ElTS0HPO_HXwPdJh6JAbSPHH1EFdNXFuWmOyDLnzZiRO_1kXfLUMAKY1eNyS1rTt1TIPFX0AxOwDUH1HA86

 

In my Zap it would look like this - where Email is configured as my Trigger Column.

HRnBFW7KJWB7LW6FxNHay_88Eu9dUZ3REa5su9IgO1nbeKN-93NyySlWHYJFN4eb8xHelwzXsX_Plid-JQvr5YyV71pfWo3WyI2sMRExOJezb7Kiiuwzu1A2u7uVmrp8veOb83IH

 

So what will happen when we turn this Zap ON?

First - if we add a new row where Email is Blank - the Zap will trigger - since it triggers for all New Rows.

The Zap will trigger for Row 3 below - even though the email column is blank.

PCYTWEXKiDO37htMbRkDZxnHfVPFHicZTVXTtDRz-dTvKStUefsDtkN426LqyMterPGexQn5AOjCUrlprgdWLFErbF6Ia-Yo05sC_AmrEUzueqvJjJyJkZFZIjcWudcXjYMi2TR6

 

We can always add a Filter at step 2 if we only want the Zap to continue only for specific values or when the Email value exists.

Once the row is on the Sheet - if we change the value to cell C3 to “jaredzapier+2@gmail.com” - the Zap will trigger - since it’s the first time we’ve seen the value of “jaredzapier+2@gmail.com” in Cell C3.

6FwavY6vzgjhx08e8GaGDc-cJp6wnE65qZoSn_i2qfDt-n2leVCBb529JfT8aJ5SRfEDZwl93s3eSohozduD1-8M-FZd2Tb01x-NTo4jmjqKFc_qPxwU6UsP5EQtM3r6kzoZ4mQ6

 

If I change it to “jaredzapier+3@gmail.com” the Zap would also trigger - since we have not seen the value of “jaredzapier+3@gmail.com” in cell C3 before either.

4yeHEpzfuO6uax-bGyBMynQfjniHn0THdo9fedjhFzHFL5LzLeQvHJG1DZASsRhILB7mvnytiXKfSGPmhLSagAZAF6ZB2IsTU2Fd7IqG5yp1wS__fdwORKf4TkIBtwA9C0pAinhT

 

But if I then change it back to “jaredzapier+2@gmail.com” the Zap would not trigger again - since we have already seen the value of “jaredzapier+2@gmail.com” in cell C3.

Every time we turn a Zap OFF/ON it resets the deduplication table that we use to look for these types of changes.

The Problem

If we want the Zap to trigger for every change to the Email Column (including values we’ve seen before) - this won’t work given the way that Deduplication works in this Trigger.

So we need another solution.

Sometimes - we’d work around this by adding a Zapier Manager trigger to turn the Zap OFF/ON on a schedule to reset the deduplication table.

But that will only work if we know the schedule to expect changes to the Emaill Column.

If the Email column can be updated at any point - then turning the Zap OFF/ON to reset the deduplication table is not a reliable solution.

The Solution

In order to get the Zap to trigger for every update - we need to put unique data into a trigger column each time our target column is updated.

The easiest way to do this is with a Last Modified Timestamp - since the date and time will always be unique - and will always be data that we have not seen before in the Trigger Column.

So every update to the Email column will update our Last Modified column - and trigger the Zap.

Unfortunately - Google Sheets doesn’t have a built-in way to add a Last Modified Timestamp Column.

But with a few easy steps - we can add this functionality to the Sheet - and generate a timestamp every time the Row (or a specific column in the row) is updated.

Step 1 - Add a Google Script to our Sheet

To make this work - we’re going to add a Script to our Sheet by going to Extensions and App Scripts.

3spMQ5pbXIsvCu5HoClL4JSRLW255bMSQmHoWVozSac5N6ytiZHlC_ElyoR6GMr-inWX0P6pkBpVVteUnoPSRe659Jr6BObdOmILttid3y0jco5pTb_7zB1cxvqN4mcDFltvjF6U

 

This script will ultimately add the Timestamp in a column on our Sheet.

I would highly encourage you to watch this fantastic YouTube video which is where I learned how to add + configure this script. :) 

I’ve also shared my Sheet above - which will already have the code for my example below configured.

You can click here to open the Shared Sheet in a view only format.

And then make a copy to add an editable version to your Sheets account.

xt-F5bwkxzvwdq0gYPZ9OmutF5-o2nBM9YVBIRDczvwBleRo0B2hUaHyAUmgyJqwRE-4IcJ6G-FVrFG-zYJWtJpDKACCNBBOErq2UbYzcekkmHcxyjPR-sZZelGF_dAJ43HydZSv

 

When you click Extensions > Apps Script - it should open this Timestamp code.

uWyr3-Lp_G20PEWnGLTg53rsaIBZg3q5SBcVgYU6WnyERwI6RYF7wWB518Xb-ER4hDsbNhrkTFMdNngikygrE5Q6vMvDjcLA-m9hxIVvmJQKXE8AfjjdpLLlG_GT5FAJbAP7mRtf

 

I want to point out the variables - since we can edit these variables (and then click save) to adjust exactly which changes on the worksheet are adding a timestamp.

The first variable “ws” controls which Worksheet the script will run on.

The second variable “startRow” controls which row we will start looking for changes. In this case I have it set to 2 so we don’t add a timestamp when the header row is updated.

The third variable “targetColumn” controls which column we are monitoring for changes.

In this case - I want to watch for changes to my Email Column - which is in Column D (or the 4th column).

The fourth variable “dateColumn” is the column where we want the timestamp recorded.

PiqJNXk8-4FZiyHPEKjMMHFA-PdE9OYoGqms9vqwLqkYaUw4f0aRovhvWV62WuH35QAOYhCrOwN5CDH3jLotha9-wujZGVxVq3yclkcv9KzUy9YBZT3PsSfDxBxu5346svDLV91D

 

You can edit any of these variables to match the data on your Sheet - just make sure to click the Save Disk icon after making any changes.

jQyMx7U5j6cJGNW0Fce3UdFPs8rk-AXrRYfsoWkO3S7SFF-avZVcYGr4fAiF3VGEuFb-sy7Z_dPd8s9BAUVVJSOsKmT8dQjVDETlnza3lF77xVQbbouxMtGjf0_a1iTEalB7gJvX

 

Step 2 - Testing our Timestamp

Now that our script is configured - we want to update some data on our Worksheet to make sure the timestamp is added when we change column 4 (Email).

What we should notice is that every time we make a change to the Email Column - the timestamp should update automatically.

lQ3gsDdRMKixFNbsuUuyFD3RwfhHaik4rsijgU3D5YYMwkrBQVICeH3LLhd-tEoPgb9sAR1gvaXVoxwDye-FyML9zysE8m2TBJtR7BngHNe41UwClQd09ieZPGKW6lsxVo6Vcx2j

 

We now have a timestamp being added in Column A every time the Email column on the Sheet is updated. :) 

Configuring our Zap

Back in our Zap - we want to adjust our Trigger configuration to watch the “Last Modified” column as our Trigger Column rather than the “Email” column directly.

U6XV_DS2an6Ki4vpFbmepgQtWs6D7QPtdtMYTU30jzoqN328IDurzQr9F9LB80lb2sgq02jTy5p-jPAPRzUSFHRorkFT43ZVUnvrBD1m7HX_Q-jQF3KOGXG_g_Nf5LOXAtKscaw-

 

Now any time the Last Modified date/time changes (which is every time the email address column is changed) - our Zap will trigger.

I’ve also added a filter to my Zap so it only continues when the Email Address on the Sheet exists (I don’t want to be notified of blanks).

cDT6JVkoX9S6FNyoUK9mrdDpEooMWYQ-H_-A3IrOl58otcLU6pv5y8ma6zKuHt9m3_MUsNAmSBrICYHCU9xI_01yTT6N-DIW73v7828_tGfthy0moIRlyxWCDdkEn5xUw-g6R9mb

 

And then I’m sending myself a Gmail message to alert me to this change (since I’m not the one that updates the Sheet).

YHXvo_RwMfd4Wqpis6ncUqx5YtEutllIYezjAFTja5CEG9dyQALXNRDzGpjguKltU0P58cpHc_zcYSbc8D_9yL-1dzFG78MphHCwAnMCEW58i8lM63CWba034E5wmLA4yoyszOlZ

 

Live Testing our Zap

Now we’re ready to turn the Zap ON and live test it.

When I turned the Zap ON - these were the 2 email addresses in Column D.

XHYFcZS_gHzayotci8lOd05-opb6H2ZLbmLAp7stvXShT3vQP2bzmW-SddWaYfEPuW4PwRuC6eP1CxdvEs-_EhFnu1RZh1ciX1axHxvol9VQOMSQha3O_27At55MAtNVJbeUqzLH

 

I changed the email address in cell D3 to “jaredzapier+3@gmail.com”.

djfcc9xBeZU3iepTey7IuvtbnfV_tPDy6CnbJbs2uLwvgyzpWijQ5dbmTH1vMW-z6DIsHRJ8qZxyVzIRQEvmEIZlFA9W-jdDC-IBbXZ0e-zDOCUtNpUjKUhygN4F1ibYOkQ5AlqF

 

And the Zap triggered as expected.

F7f-ZZxvwksibLggahnwI_YoWaG6mEmuElInER-o-t8buUr3IjfUVX3excDg61LYEYXxMA-QTsVlIizsitTIjHyNAQgbU5Dz0Lxo0zRiV5q0o-OUyWSCXVAzxQVpGReJkxmeGqYr

 

So far so good.

What happens when we change the email address in cell D3 back to “jaredzapier+2@gmail.com”?

Our timestamp in A3 changes.

xekL3p3f0GU04ewuIffyOJwQkHs4aoo6RaTI71BrKRahb7Vhs8MGPyHWyV1BbPuYflUSt63uO4SkudXqTx9jeg4m-FaO78PiRQNGlwow_OvenlLYX9vVymLDoPrgKtScAtq_Zcx3

 

And the Zap triggers again!

G8KjKWcBABiywvUqBunaEI2B7xxXZx3Bkvz1W-UMLCWeMIYJSKG_RuJC39LOLXqoIyzTg7ivD_5uJ16Sjbr0J_4DBwZnxc-c4iLnouIznF6aoYOiXkqkhZJ3a2H5PJ0wjV_SWWvp

 

We have succeeded in creating a Zap that will trigger for ALL changes to a specific column on a Google Sheet.

Summary

Since Polling and Hybrid triggers in Zapier use deduplication to identify new records - we will only trigger Google Sheets New or Updated Row Zaps the first time we see a value in the trigger column on the Sheet.

By adding an Apps Script to the Sheet - we are able to create a timestamp each time a specific column is updated.

We can then adjust our trigger in Zapier to use this timestamp column as our trigger column.

Since the timestamp will update automatically each time the target column changes - we will have a unique value in our trigger column which enables the Zap to trigger more than once for the same value in the target column.

If you do give this a try - I’d love to hear your experience in the comments below!


0 replies

Be the first to reply!

Reply