How best to watch for changes in sheets

Hi everyone,
i’m trying to create a scenario flow from Google Sheets to Asana, when upon certain conditions being met (eg. a date is passed), a formula column changes from ‘not due’ to ‘due’ and then details from the sheet is sent to create a task in Asana.

I’ve achieved this but however I implement it has issues:

  • using the Integromat sheets function works perfectly but then when the scenario is active is triggers endlessly, creating many duplicate operations and tasks
  • using the watch for changes trigger chews through operations as any change to the sheet counts as an operation and must be filtered out; additionally only cells changed by a user are detected and not IF formula output cells

basically I’m looking for a way to trigger the function once, and then stop looking in that row.
I think this may be achievable using google scripts but I can’t figure out how to include the Make webhook in a script.

Hi @Seb,

how often do you want to check the condition of the Sheet <-> Asana?
We built everything ontop of Sheets and use the “Watch Changes” modules but especially if you want to catch changes in IF-formula your best option might be the “Get Range Values” Module.

This way you just grab the whole sheet, filter out the “not due” rows and only keep the “due” rows. I’d advice you to add a column in the google sheet containing =row(). This way your “Get Range Values” modules retrieves the current values of each row and you get the row number to update processed rows.

Hope it helps!

Best,
Richard

2 Likes

Hi Richard,

Thanks for your advice!
Daily checks would be fine. Basically I want to create a task when a due date in the sheet comes about, not when the sheet is edited directly.
My problem with watch changes is it only ‘sees’ changes made by a user and not changes that happen by formula, although I realise now the sheet probably has to be open for the change to occur.
I’ve just realised I’ve probably been going about this the wrong way and it would be better to look in the sheet with Make for the date and filter for the current date instead, although that would only avoid duplicates if checking for today’s date rather than dates <=today.
This will still consume a lot of operations, which I was using the IF function in sheets to filter that side instead.

Hi Seb,

alright! Then I’d go with daily checks then.
Filter date = today and think about the formatting.
If you want to transfer historic task you might to date < today and then daily date = today :slight_smile:
Hope it helps! :slight_smile:

If not, we are using Google Sheets + Make for everything so I probably did most of the mistakes already haha

Best,
Richard

1 Like