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.

score 1 · Answer 1 · answered Jun 19 '14 at 10:03

1

I use GScript instead of Python but I hope I can still help:

"cell.value = 'O_o'" actually issues a call to the API and after 50k calls you get an error. Leave out the update_cells command at the end. If I am right you will still get the error.

You have several ways to avoid this which are much faster: The range has a setValue(Object) function built in, so this line should do all you need:

worksheet.range('A1:C7').setValue('O_o');

Or if you need different Values for each field you can build your 2D Array with the same size first and then pass it to the setValues (not setValue!) function.

answered Jun 19 '14 at 10:03

Fabian Tschachtli

361
2
10

`.setValue`? are you sure that is for `gspread`? what language is that? it sounds like `Google-app-script`. I'm using `python`. I'm confused. – jason Jun 19 '14 at 14:42
worksheet.range('A1:C7') is a list in python. It has no attribute setValue or similar. Would be great if someone know how the same works in python! – feilchenfeldt Dec 19 '14 at 14:35
Assignment to Cell.value does not appear to issue an API call in the current version [I'm looking at HEAD as of 2016-01-19]. – blais Jan 20 '16 at 02:31

score 1 · Accepted Answer · answered Jun 17 '16 at 08:02

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;
}

score 0 · Answer 3 · answered Oct 28 '15 at 13:09

Google spreadsheets have complexity limits. Every time a cell is updated, any cell that references it is recalculated. If formulas become too complex or take too long to calculate, the spreadsheet will timeout during calculation. In order to read over 50000, you can reduce the complexity of you spreadsheet. please look here

gspread update cells in batch, 50k limit?

3 Answers3