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

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”?

1 Like

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

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?

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)

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

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

And put updated_row_range as the key

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”

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

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

hey @UKarpuk,

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

Regex:

[A-Z]+(\d+)

template

edit:

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

1 Like

This might be a better solution LOL

1 Like

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

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

That worked! Thank you so much!

2 Likes

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

Sweet explanation, thanks again!

(\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

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