Get the return value of pygsheets.Worksheet.append_table()

[ktpss95112]

I'm trying to use pygsheets to append data to google sheet every 5 minutes. Also, I want to set the note of the newly appended cells. Since the values parameter of pygsheets.Worksheet.append_table() accepts only pure, so I'll need to append_table() and then obtain the index of the last row.

I found some issues and stackoverflow topics and they said that there is no such API that returns the index of the last row. However, I found that the underlying API used by append_table() will return the updated range in the response body, which contains the row index.

Digging into the source code, I found that the API is called by sheet.py:

https://github.com/nithinmurali/pygsheets/blob/ee414273a301dc8f0e2ef6a164ee2884dfe1c2ea/pygsheets/sheet.py#L263-L268

However, the return value is ignored by worksheet.py:

https://github.com/nithinmurali/pygsheets/blob/ee414273a301dc8f0e2ef6a164ee2884dfe1c2ea/pygsheets/worksheet.py#L1135-L1143

Maybe we can return the response of self.client.sheet.values_append() instead of ignoring it to provide more similar behavior of the API itself?

[ktpss95112]

Here is the PoC:

gc = pygsheets.authorize(service_account_file='...')
sh = gc.open_by_key('...')
wks = sh.sheet1
print(wks.client.sheet.values_append(
    '...',
    [[1, 2, 3], [4, 5, 6]],
    'ROWS',
    target_sheet._get_range('A1'),
    insertDataOption='INSERT_ROWS',
))

And the result is

{
    'spreadsheetId': '1h-...OoA',
    'tableRange': 'result!B5',
    'updates': {
        'spreadsheetId': '1h-...OoA',
        'updatedCells': 6,
        'updatedColumns': 3,
        'updatedRange': 'result!B6:D7',
        'updatedRows': 2
    }
}

, showing that the updated range is actually obtainable.

[nithinmurali]

If you just want to get the last row of the sheet, then you can can use sheet.rows . If you want to access the newly appended data then the suggested change makes sense.

But we dont return the underlying raw json in any api calls. We always wrap the data in sensible data-structure and return them. In this case i would suggest that it should return GridRange appropriately.

The result should look something similar to this

{
    'tableRange': <GridRange Sheet1!A1:D4>,
    'updates': {
        'updatedRange': <GridRange Sheet1!A1:D4>,
        'updatedCells': 6,
        'updatedColumns': 3,
        'updatedRows': 2
    }
}

[ktpss95112]

Sorry that I did not clearly explain the "last row". What I meant is the last row which contains data, which is used by google form when appending new replies or when you press Ctrl+A in google sheet (the first press would select until the last row which contains data, and if press the second time the selected range would become the whole sheet).

sheet.rows returns the number of the rows, not the number of rows containing data. However, I did not find any google sheet api that provides this information. That is why I proposed this issue.