Google Sheets - Review/Scan entire sheet every run

[Joseph_Schultz]

Hi, I’m seeking some help regarding Google Sheets. I’m looking for the correct google sheet module and/or the correct module setting to configure so that each time my scenario runs, it scans the entire sheet.

Ideally, I’d like to use the Watch New Row module, but have the scenario scan the entire sheet each time it runs.

Background on my scenario: I’m loading a list of prospects onto a google sheet, each prospect has a communication status along with a status timestamp that the scenario applies. My scenario contains a filter based on status timestamp.

So I seemingly can not use the Watch Changes module, as at the time of running the scenario will pick up on the row that was updated based on the last scenario run, but it will get filtered out unless it meets my status timestamp requirement… and seemingly will not get picked up again on the next run since there wasn’t a subsequent change.

Any suggestions/tips would be much appreciated.

[Stoyan_Vatov]

Hey Joseph,

sorry could you clarify a bit more?

You want the scenario to trigger when the status timestamp changes? Or when a new row is added but has certain information inside?

[Joseph_Schultz]

Hi @Stoyan_Vatov sure.

Below is a screenshot of my existing scenario.

Each week I add about 40-50 new prospects to my google sheet, new prospects are automatically assigned a status of ‘New’ within my google sheet. The intention is that New prospects receive an Email, then the google sheet status column is updated for that prospect (From ‘New’ to ‘Email No.1 Sent’) along with a time stamp and a small delay of about 30 secs before moving on to the next new row/prospect.

Each time I run the scenario I’d like it to execute on 5 rows at a time. (So I’m looking to run this on a custom schedule or very 30 mins or so each day, so that over the course of a day or so it will run through all my prospects/rows.

The intention of the router is to filter prospects so that they receive Emails No.2, No.3, etc. With Email No. 2 going out 7 days after the first email… and Email No. 3 going out 14 days after the the 2nd email.

My filter is working fine.

The challenge I’m having is using the right google sheets module based on how I’d like it to run.

For example when I choose the Watch New Rows, it works fine for the first execution… but then it only picks up the new set of prospects I load the following week… it doesn’t run on the rows that had the status and status timestamp updated. So Email No.1 goes out… but none of the sequential emails.

If I choose the Watch Changes Module, when I run the scenario it picks up the updated rows, but those row don’t pass my filter, then next time it runs, doesn’t pick up that row anymore.

i.e Today it runs on a New Prospect and emails the prospect Email No.1, updates the status to Email No. 1 sent and timestamps it with todays date. The Second path in my router is filtering to status = Email No. Sent no earlier than 7 days ago… so if the Watch Changes Module run tomorrow, it will pick up that prospect but they don’t pass my filter and then are not picked up again my the module.

Hope this helps clarify my post.

[Stoyan_Vatov]

Ah I see.

If you want it to run only at specific times and process all entries, then I suggest the Search Rows module. You can set it to return 5 rows at a time and check all rows inside to find ones that match the criteria.

[Joseph_Schultz]

Hi @Stoyan_Vatov, thank you… That seemed like it might be a solution, however while testing, it seems that setting the limit to 5, prevents the scenario from working its way down my google sheet… I’ve set the Search Row Module criteria to be very basic with just one criteria: Prospect Name - Existing.

When I run the scenario the first time, it picks up 5 rows as expected, however when I run it again, it simply picks up the same rows (since those are the first 5 rows on the google sheet).

I think we’re close, any suggestions for additional criteria or settings that could be set to ensure that the scenario works it’s way down the list each time it runs?

[Stoyan_Vatov]

Mark them as processed and have the search module look for ones that have not been processed yet.

[Joseph_Schultz]

Thank you @Stoyan_Vatov I think by adding the processed note along with a processed timestamp (execution date), this solves my inquiry. Thank you again for taking time to respond and share your knowledge/suggestions.