Google sheets: getting the row I just appended

I have a problem that I think could be solved in two airtight ways. The problem is that I want to add a row into a spreadsheet and grab the resulting VLOOKUP value in another column on that same row.

Unfortunately appending a row doesn’t give you the row number so you have no way of getting that row back. That’d be one way to solve it.

The other way I can think of to solve it is using a lookup node to look up the exact thing I just appended. But that wouldn’t necessarily work because sometimes I’ll append things with the same value.

I can come up with workarounds such as appending and then looking up the value and only grabbing the last row, or using a unique ID for each row and looking that up. The first one is prone to errors with race conditions and the latter requires adding to the data in a way that I don’t want to do (plus coming up with a unique ID system…)

Any suggestions?

Hi @Giovanni_Segar, I hope you’re well?

First of all, I think returning the row number would make a great feature request for the Google Sheets node.

With this not being available at the moment, I think using a unique ID can make sense for the time being. As for generating a unique ID, you could use a solution like the one suggested here: Function to generate UUID or similar - #2 by jan

That’s actually what I decided to use for now. But it’s definitely not my favorite solution

@RicardoE105 I think this could be a good feature improvement.

@Giovanni_Segar could you convert this post to feature improvement to receive votes.

Thanks for now.

@rodrigoscdc How do I do that? :see_no_evil:

Just changed it to a feature request. Now you can upvote it!

1 Like

Hello @pemontto I saw that you made two recent great features in google sheets node, could you give some attention to this request already that your brain is fresh to documentation of google sheets and developing features to sheets.

Thanks for your attention, wish you regards.

I’m not entirely sure I understand your request, but in the returned metadata on updating/appending we get the specific cell location in responses. Would this work if it returned something like this?

Append:

{
  "spreadsheetId": "xyz",
  "tableRange": "test!A1:C17",
  "updates": {
    "spreadsheetId": "xyz",
    "updatedRange": "test!A18:C18",
    "updatedRows": 1,
    "updatedColumns": 3,
    "updatedCells": 3
  }
}

Update/UpdateAll:

{
  "spreadsheetId": "xyz",
  "totalUpdatedRows": 2,
  "totalUpdatedColumns": 2,
  "totalUpdatedCells": 4,
  "totalUpdatedSheets": 1,
  "responses": [
    {
      "spreadsheetId": "xyz",
      "updatedRange": "test!B2",
      "updatedRows": 1,
      "updatedColumns": 1,
      "updatedCells": 1
    },
    {
      "spreadsheetId": "xyz",
      "updatedRange": "test!B7",
      "updatedRows": 1,
      "updatedColumns": 1,
      "updatedCells": 1
    },
    {
      "spreadsheetId": "xyz",
      "updatedRange": "test!C2",
      "updatedRows": 1,
      "updatedColumns": 1,
      "updatedCells": 1
    },
    {
      "spreadsheetId": "xyz",
      "updatedRange": "test!C7",
      "updatedRows": 1,
      "updatedColumns": 1,
      "updatedCells": 1
    }
  ]
}

That seems like what I was asking for originally—but I don’t believe that was the case when I originally posted this feature request. This should definitely take care of it.

Yes, this is exactly the feature request, on the return of response in node append a new key:value which is row: {{numberRow}}.

Ok, I’ll have to test all operations and see where it makes sense depending on the response.

Then to avoid breaking changes I’ll add it as boolean option (default false).

If it’s not large a change I’ll see if I can squeeze it into the existing request :+1:

2 Likes

I didn’t complete my answer another feature of doing that is, when we need to make update operation we could select use number row to make update, just not key value that is today. This is good when you have duplicate keys on the sheet.