gspread update cells in batch, 50k limit?

Question

I have been using gspread with no problem for a while. I recently went over 50000 cells when updating to Google sheets. I'm getting a

File "build\bdist.win32\egg\gspread\httpsession.py", line 81, in request raise HTTPError(response) HTTPError

Did it take so long to update that the connection timed out?

My cell range to update is A1:CL560. I sliced the output to just under 50000 cells and it the code worked fine.

the code I'm using is just what is referenced on github:

cell_list = worksheet.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
worksheet.update_cells(cell_list)

everything seems to be fine under 50000 cells. Not sure what to do.

This is the error: error: [Errno 10054] An existing connection was forcibly closed by the remote host

I've tried to keep the connection alive, still doesn't work.

Answer 1

A bit late, but I ran into this issue, I solved it by doing the setvalues in chunks, see below my solution using GoogleScript. It is a bit convoluted, but I was figuring it out as I went along :)

 function importtxt(url) {   
  var parameters = {method : "get", payload : "", muteHttpExceptions: true, validateHttpsCertificates: false};
  var result = UrlFetchApp.fetch(url, parameters);
  var responseCode = result.getResponseCode();

  var contents = result.getContentText().toString().replace("^[^<]*", "");
  var line = contents.split("\n");

  var j = 0;
  var start = 1;
  var chunk = new Array();
  var chunksize = 5000;
  var rows = contents.length;

  var ImportData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ImportData");
  var range = ImportData.getRange("B2:B").clearContent();

  for (var i = 0; i < rows; i++) {
    if ( line[i]) {
      chunk.push([line[i]]);
      j++;
      if ( j > chunksize || i == contents.length ) {
        j += start - 1;
        var end = j;
        Logger.log("writing chunk to " + start + " : " + j + " : " + chunk.length);
        // write chunk to sheet, i will denote the start
        var range = ImportData.getRange("A" + start + ":A" + j);
        range.setValues(chunk);
        // set variables for next run
        start = end + 1;
        var chunk = new Array();
        j = 0;
      }
    }
  }
  Logger.log("finished - last row was " + end + " but last row should have been " + contents.length);
  if ( j < i ) {
    j += start - 1;
    var end = j;
    Logger.log("writing end chunk to " + start + " : " + j + " : " + chunk.length);
    // write chunk to sheet, i will denote the start
    var range = ImportData.getRange("A" + start + ":A" + j);
    range.setValues(chunk);
  }
  return;
}