Google Sheets - Add new Row - retrieve row number
Status: Open · Asked by Ed Harmoush on · 0 views
I'm using the Google Sheets action "Add new row" -- is there a way to use a variable which represents the row number that was just added to?
Right now I have to do something rather complicated: =INDIRECT("AnotherSheet!A" & ROW())
This fetches the value in the sheet "AnotherSheet", in column "A", at the Row of the currently edited cell. Alas, the =INDIRECT function causes the entire spreadsheet to re-calculate, and is sort of janky.
Is there a variable available in the "Add new row" function that can represent the row being added to?
At the very least to allow later actions to use the Row# to retrieve data without requiring a "Lookup SPreadsheet Rows" action?
Hi @erh,
The 'Google Sheets: Add New Row' action step does provide you with the row in which the data has been added. You can extract it in the manner suggested below.
https://connect.pabbly.com/workflow/share/CEBYalcAUDNTGQJoD1xRdl1JCg4JUwA8XUQEFlBfAnoATlMGUxIJYwFNA2leEVMyUBkJYwJeDTEAFAsPCV8GdVxGUX1VVlwmB0oGNQJLDD8IVlhwVzY#
Hi @erh,The 'Google Sheets: Add New Row' action step does provide you with the row in which the data has been added. You can extract it in the manner suggested below.
https://connect.pabbly.com/workflow/share/CEBYalcAUDNTGQJoD1xRdl1JCg4JUwA8XUQEFlBfAnoATlMGUxIJYwFNA2leEVMyUBkJYwJeDTEAFAsPCV8GdVxGUX1VVlwmB0oGNQJLDD8IVlhwVzY#
Great idea. I think it can be even easier, actually, using Text Formatter: Extract Pattern
I was able to use this to extract the last digit: (\d+)$
This is an example string from your linked workflow:
2. Updates UpdatedRange : Sheet1!A19:D19The result was 19.
Question, do you know for a fact that the Add New Row action's UpdatedRange return string's Last Row variable will always be the row that was just recently added to?
Yes, the Last Row variable will always be the row in which data was added.