Google Sheets: Obtain the row number of an "Insert row" action result

Need help
1

What is the best way to obtain the row number of the row that was created with the “Insert row” action in Google Sheets?
Why does the response of the “Insert row” action contain the row number in the format “updatedRange”: “Sheet1!A6”, which means that row number 6 was added, but there is no separate value in the output of the action for the row number of the inserted row?
How can the row number be extracted from this step and used in the subsequent steps, such as “Update Row”?

2 Likes
2

This is something I also asked for too (yesterday, coincidentally), hopefully the developer of the action/piece can add it.

In the meantime, a temp fix: [Google Sheets] Update "Insert Row" Action with Updated Row Number - #3 by S_M

3

Thank you for providing the link to the related topic.
I have tried implementing the code, but I encountered an error message that says:
“missing ) after argument list”.
Can you please provide a solution for this issue?

4

Can you record a video (i.e. Loom) going through your piece? It will be much easier to help, since I am barely a novice, but I can tell you that it worked without issue for me.

Also to reiterate:

  1. You added the code block
  2. You added the shared code (make sure to update the second line to match the key-value pair if you change the name below, mine was “const updatedRowRange = inputs.updated_row_range” but yours would be const updatedRowRange = inputs.KEY with KEY being the name of your key in the key-value pair)
  3. You added the npm package (@types/node)
  4. You added the key-value pair that links to the Google Sheets updatedRowRange (again make sure the KEY is the same in line 2 of index.ts where it should read inputs.KEY)
5

Here is a Loom video that demonstrates the issue I am experiencing: Loom | Free Screen & Video Recording Software | Loom

6

On line 2, try inputs.updated_row_range instead of inputs.key

And put updated_row_range as the key

7

That’s what I initially did. Then I attempted to change it to “key”. Now I have reverted back to using “updated_row_range” again. The outcome remains unchanged: “missing ) after argument list”

8

image
image796×458 17.8 KB

9

image
image684×758 33.3 KB

12 
{
  "updates": {
    "updatedRows": 1,
    "updatedCells": 1,
    "updatedRange": "RatesBYN!A6",
    "spreadsheetId": "1JfgQYW1-Q1kiguxgW4ccBInvcg-FXOQ3hPCkl_GZJu0",
    "updatedColumns": 1
  },
  "tableRange": "RatesBYN!A1:G5",
  "spreadsheetId": "1JfgQYW1-Q1kiguxgW4ccBInvcg-FXOQ3hPCkl_GZJu0"
}
13

I copied and pasted this code and tested it again:
image

14

hey @UKarpuk,

Do you just want the row number as output?
If so, the Text Helper piece might be an easier solution:

image
image428×545 10.1 KB

Regex:

[A-Z]+(\d+)

template

edit:

For future refence, this regex is the best solution for retrieving the row number!

1 Like
15

This might be a better solution LOL

1 Like
16

haha, I must admit that, initially, I always used the code piece for everything as well, as it would certainly solve the problem. Now, I’m getting used to using all the Core pieces, which work amazingly and save time!

1 Like
17

Nice! Problem for me is there are two numbers that show up “Name!A19:E19” hence the need for the code piece @Dennis (unless you have a regex for that as well?)

EDIT: Actually, it gives two values, so I can always take the second one. LOL clearly I am a novice.

EDIT 2: Thank you AI (?<=A)\d+ this just takes the number 19 for me.

Thanks, Dennis, this solution is much better.

1 Like
18

That worked! Thank you so much!

2 Likes
19

That shouldn’t be an issue. The Regex I used is as follows:

  • [A-Z]+ matches “A”, which is the column part.
  • (\d+) captures “4”, which is the row number part.

Regex functions return both the overall match and any captured groups within that match. In this case, “A4” is the entire match, and “4” is the captured group representing the row number.

An issue would be if you name your sheet “A4” as an example, since it matches the Regex. To solve this you could change your Regex to:

!(?:[A-Z]+)(\d+)
  • !: Looks for the exclamation mark, which separates the sheet name from the cell range.
  • (?:[A-Z]+): A non-capturing group (?:…) that matches one or more uppercase letters, representing the column part of the cell range. Non-capturing groups don’t capture their match for output.
  • (\d+): A capturing group that matches one or more digits. These digits represent the row number.

As long as you don’t name your sheet “A-followed by numbers” this should work fine sa well!

You’re welcome!

1 Like
20

Sweet explanation, thanks again!

21 
(\d+)$

This pattern will match and capture the last set of consecutive digits at the end of the range address. It will extract the row number correctly, whether it consists of one or multiple digits.

2 Likes
22

Ah, good one, that’s much easier!

When I’ve been looking at something for a while, I tend to use overly complex solutions. Your solution should always be effective!

1 Like