google sheets: how to update a row instead of add a new one?

[rodrigo]

Hello everyone,

I have a workflow that monitor a webhook and each time it receive a lead it connect with Google Sheets app and Add a New Row !

It's working....

Based on this, each event that webhook receive come with a unique ID, and I record this in my google sheets, each new row have:

Example: Date | Name | Phone Number | Message | Unique ID

Let's say that my excel have already 1000 leads...

How can I monitor and use the same workflow to check before add a new row if there's already a row with this unique id and if it exist instead of add a new row, just update the row that already have this unique ID?

I saw other actions like Update Row, Update Cell, etc... but they need know the row index and other informations that I don't have... I need that pabbly connect search in the rows and find which row have the same unique id inside!


Thanks

 

[ArshilAhmad]

Hey @rodrigo,

The use case you are describing will require you to add Google Sheets: Get Row(s) action event, Iterator, and Router with 2 routes to your workflow.

Google Sheets: Get Row(s) will fetch the entire data from your spreadsheet. Iterator will process the data one row at a time, and Router will help you decide what further action is needed based on the Unique ID you get from your trigger application.

If the data captured from Google Sheets does not contain the Unique ID, a new row will be added to your spreadsheet.

If the data captured from Google Sheets does contain the Unique ID, then the row that contains the Unique ID will be updated with new data.

 

[zest83]

Hi team,
Is there a limit to how many rows for the "get rows" or "Iterator" ?
How many Rows Can it Maximum Crawl/Search at one time.
Like, the "LookUp Spreadsheet" can do first 100 only

 

[ArshilAhmad]

Hi team,
Is there a limit to how many rows for the "get rows" or "Iterator" ?
How many Rows Can it Maximum Crawl/Search at one time.
Like, the "LookUp Spreadsheet" can do first 100 only

Please allow me some time to look into this, I will get back to you shortly.

 

[zest83]

Also, can we get "Get Rows" as a Defined Range of 1000 rows instead of only the DATA Containing Rows... Because, We expect data to be filled in those rows eventually... and it will be a headache to manually change this

 

[zest83]

Can we get as in..
Can we set the Range as A1:B1000
While the data is only in A1:B100

So 100 rows have data
but setting 1000 rows as the range

How does this affect the system?

 

[zest83]

Also, incase the iterator has lookedup 100 rows...
The Action works by Going through every Row - Every time there's a scheduled trigger.

So suppose I run the workflow at 8 AM every morning
The Workflow will go through the Sheets 100 rows, one at a time getting all data
Next, what if the workflow has a Filter Condition, Suppose, it needs to match the Word "Orange" on a daily basis
The word "Orange" on this sheet is changing dynamically in its respective column, hence we are crawling through the sheet daily.
As soon as the crawling finds the data "Orange", it will match the Filter Condition and move ahead.
Now, to understand if Pabbly can handle this.. because, Logically, the code should go through each of the rows and match the word orange...
so the workflow will make calls to your server 100 times?
OR
100 x 100 times? = 10,000 calls

 

[zest83]

Please do Reply to all 4 Queries
Thanks

 

[ArshilAhmad]

Is there a limit to how many rows for the "get rows" or "Iterator" ?

The Iterator can iterate a maximum of 500 rows. There is no row limit for the "Get Row(s)" action event, but there is a maximum data limit of 6 MB.

Also, can we get "Get Rows" as a Defined Range of 1000 rows instead of only the DATA Containing Rows... Because, We expect data to be filled in those rows eventually... and it will be a headache to manually change this

This is not possible. The Google Sheets: Get Row(s) action event only fetches the data stored in your spreadsheet. Please refer to the help text in the screenshot attached below.

Also, incase the iterator has lookedup 100 rows...
The Action works by Going through every Row - Every time there's a scheduled trigger.

So suppose I run the workflow at 8 AM every morning
The Workflow will go through the Sheets 100 rows, one at a time getting all data
Next, what if the workflow has a Filter Condition, Suppose, it needs to match the Word "Orange" on a daily basis
The word "Orange" on this sheet is changing dynamically in its respective column, hence we are crawling through the sheet daily.
As soon as the crawling finds the data "Orange", it will match the Filter Condition and move ahead.
Now, to understand if Pabbly can handle this.. because, Logically, the code should go through each of the rows and match the word orange...
so the workflow will make calls to your server 100 times?
OR
100 x 100 times? = 10,000 calls

Filter does not crawl the entire data; instead, the Iterator iterates through the captured data and feeds it to the Filter. Additionally, since the Iterator looks for data through the "Get Row(s)" response, only one call will be made to Google Sheets specifically for the "Get Row(s)" action.

 

[zest83]

ok So a very important question
How many events/actions can be held/queued in the Delay Until Step?
Is there a Maximum Limit to that?

Because the Task is consumed as soon as it activates but whats the limit?
10,000? or more... Or less?

 

[Pabblymember11]

How many events/actions can be held/queued in the Delay Until Step?
Is there a Maximum Limit to that?

There is no such limitation using Delay Until action step.

Because the Task is consumed as soon as it activates but whats the limit?
10,000? or more... Or less?

Kindly elaborate on your condition briefly.

 

[zest83]

There are timed reminders being sent on specific dstes

We are using the delay until trigger for this

Ideally there should be no issue on the queuing part as they are just waiting for their individual dates but still necessary to be informed

There is no such limitation using Delay Until action step.

Kindly elaborate on your condition briefly.

 

[ArshilAhmad]

There are timed reminders being sent on specific dstes

We are using the delay until trigger for this

Ideally there should be no issue on the queuing part as they are just waiting for their individual dates but still necessary to be informed

There is no such limitation using Delay Until action step.

There will be no issue with the queuing part because that's what the Delay is used for.

 

[zest83]

ok So a very important question
How many events/actions can be held/queued in the Delay Until Step?
Is there a Maximum Limit to that?

Because the Task is consumed as soon as it activates but whats the limit?
10,000? or more... Or less?

Great so the count of the queueing does not matter.
Is all I wanted to confirm.

Thank you