Add rows to Google Sheets sheet if not enough-CodePudding

I have a bunch of data that I routinely use a Python script to back up into a Google Sheets sheet. It's currently at 5385 rows filled with 6041 total. I know that if I try to upload more than 6041 rows the update will fail, but I know that I can fix this by opening up the sheet, scrolling all the way to the bottom, and then clicking "All 1000 more rows at bottom" a few times.

Is there a way for googleapiclient to automatically make sure that there's room in the sheet?

Edit:

        cells = 'Backup!A{}:{}{}'.format(start_ind, self._excel_column_index(len(headers)), start_ind   len(to_cache)   1)
       
        values = self._excel_serialize_arb_array(to_cache, headers)
        data = {'values':values}
        self.sheet.values().update(spreadsheetId=self.spreadsheet_ids['FOO'],
                        range=cells, valueInputOption='USER_ENTERED',
                        body=data).execute()

CodePudding user response：

You would need to update the properties of the Sheet itself or create a sheet with the properties of more than 1000 rows.

Note: I build the code over apps script before your edit with your code.

/**
 * Add a new sheet with some properties.
 * @param {string} yourspreadsheetId The spreadsheet ID.
 */
// This funcion would add a sheet with 10,000 rows that would be empty and that they can be filled

function addSheet() {
   const spreadsheetId = "yourspreeadsheetID"; 

  var requests = [{
    'addSheet': {
      'properties': {
        'title': 'Deposits',
        'gridProperties': {
          'rowCount': 100000,
          'columnCount': 2
        },
        'tabColor': {
          'red': 1.0,
          'green': 0.3,
          'blue': 0.4
        }
      }
    }
  }];

  var response =
      Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);
  Logger.log('Created sheet with ID: '  
      response.replies[0].addSheet.properties.sheetId);
}

// This function is to write all 10,000 rows bypassing the 1000 limit that you might be having 

function myFunction() {
  const spreadsheetId = "yourspreadsheetID"; 
  //  Spreadsheet ID.
  const max = 10000;
  const data = [];



  for (let i = 0; i < max; i  ) {
    data.push({range: `Deposits!A${i   1}`, values: [[`A${i   1}`]]});
  }
  Sheets.Spreadsheets.Values.batchUpdate({data: data, valueInputOption: "USER_ENTERED"}, spreadsheetId);
}

I run the first function to create the Sheet with the properties using the "batchUpdate" and was able to add 10,000 strings to it.

I would assume that on Python you need to increase the number of rows in the sheet using an UpdateSheetPropertiesRequest or InsertDimensionRequest check it here: Sheet API update properties!

CodePudding user response：

Here's my solution coded up.

def get_and_update_sheet_properties(self, min_rows):
    ''' Ensure we have enough rows for our arbs cache. '''
    sized_sheets = self.sheet.get(spreadsheetId=self.spreadsheet_ids['FOO'],
                                  fields="sheets(properties(title,gridProperties(columnCount,rowCount)))").execute()
    arb_sheet = [sized_sheet for sized_sheet in sized_sheets['sheets'] if sized_sheet['properties']['title'] == 'BAR'][0]
    cur_rows = arb_sheet['properties']['gridProperties']['rowCount']
    padded_rows = min_rows - (min_rows % 100)   200
    if padded_rows > cur_rows:
        requests = {
                "updateSheetProperties": {
                    "properties": {
                        "sheetId": 1922941665,
                        'gridProperties': {
                          'rowCount': padded_rows,
                          'columnCount': 26
                        },
                    },
                    "fields": "gridProperties",
                }
            }
        body = {
            'requests': requests
        }
        self.sheet.batchUpdate(spreadsheetId=self.spreadsheet_ids['FOO'], body=body).execute()
        return True
    else:
        return False