Python gspread API not appending rows if multiple functions called

Question

I'm using the gspread module in Python to create an application involving a dynamic number of rows in the spreadsheet.

Here's what the spreadsheet looks like:

Whenever the user adds a new Pinterest Pin, the program is supposed to append one more row to the bottom. I can simulate this row appending with the following code:

client = gspread.authorize(SAC.from_json_keyfile_name("WROS-Editor.json", ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']))
spreadsheet = client.open("WROS")

pinterest_sheet = spreadsheet.worksheet("Pinterest Pins")
pinterest_sheet.append_row([""])  # Simulating when the user creates a new pin

This works as intended adding 1 row at the bottom:

But, when I call the function twice, there is no new row appended. The output for the following code is the same as the previous screenshot.

pinterest_sheet.append_row([""])  # Just adds 1 new row, should add 2 new rows.
pinterest_sheet.append_row([""])

This same problem occurs when I use the add_rows() function. When I call the function twice (adding 1 rows each time, ie pinterest_sheet.add_rows(1)), it only adds 1 row. If I change the 2nd number of the add rows to 2, it adds 2 rows totally (should add 3).

pinterest_sheet.add_rows(1)  # Only adds 2 rows in total, should add 3 rows.
pinterest_sheet.add_rows(2)

Why is this an issue and how can I fix it?

Answer 1

About your situation:

I understand your situation as follows.

1. As the initial situation, the sheet has only 4 rows.
2. For the sheet with 4 rows, when you run append_row([""]), one empty row is appended, and the sheet has 5 rows.
3. For the sheet with 5 rows, when you run append_row([""]), the sheet has 5 rows. And the one empty row is not appended.
   • You want to know the reason of this.

About append_row():

It seems that append_row() of gspread uses the method of "spreadsheets.values.append" in Sheets API. In this method, at first, the last row is retrieved and the value is appended to the next row of the last row. I think that when the last row is searched, it searches whether the value is in the cell at the internal server. By this, when 2nd flow is run at above flow, the last row is the row 4. And when 3nd flow is run at above flow, the last row is the row 4, because the last row of cells with the values is the row 4. I think that this is the reason of your issue of append_row().

This specification is the same with the method of appendRow(rowContents) of Class Sheet in Spreadsheet service. From this situation, at Google side, it seems that the method of "spreadsheets.values.append" and the method of appendRow(rowContents) suppose that "" is not the value.

About add_rows():

When I saw add_rows() of gspread at GitHub, it seems that the inputted number is used rows=self.row_count + rows. Ref When I saw resize(), after the row was added, it seems that self.row_count is not updated. Ref By this, when you run the following script,

pinterest_sheet.add_rows(1)
pinterest_sheet.add_rows(2)

At pinterest_sheet.add_rows(1) and pinterest_sheet.add_rows(2), rows=self.row_count + rows is 6 and 7, respectively. I think that this is the reason of your issue of add_rows().

Workaround:

When you want to append 3 empty rows even when the last row of the values is not changed, how about the following workarounds?

Pattern 1:

pinterest_sheet.add_rows(3)

And/or

pinterest_sheet.append_rows([[""], [""], [""]])

Pattern 2:

When you want to add new row when the script is run every time, even when the last row of the values is not changed, how about the following script?

# client = Please set your client here
spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "Sheet1"  # Please set the sheet name.
spreadsheet = client.open_by_key(spreadsheetId)
sheetId = spreadsheet.worksheet(sheetName)._properties['sheetId']
req = {
    "requests": [
      {
          "appendCells": {
              "rows": [
                  {
                      "values": [
                          {
                              "userEnteredValue": {
                                  "stringValue": ""
                              }
                          }
                      ]
                  }
              ],
              "fields": "userEnteredValue",
              "sheetId": sheetId
          }
      }
    ]
}
res = spreadsheet.batch_update(req)
print(res)

• When the length of rows is increased to 3, the 3 empty rows are appended.
• In this sample, "AppendCellsRequest" of the method of "spreadsheets.batchUpdate" in Sheets API is used. In this case, it seems that "" is used as the value.

References:

• Method: spreadsheets.values.append
• appendRow(rowContents) of Class Sheet
• Method: spreadsheets.batchUpdate
• AppendCellsRequest