Google App Script Service error: Spreadsheets

Question

Were not set up to use a proper SQL database or anything so were working with google sheets. I've been trying to avoid importrange as I have a large amount of data constantly being updated and more rows added to Form responses every day. Importrange constantly fails with "importrange internal server error"

I found this fantastic code to copy from one source spreadsheet to another (as static text) so I can further manipulate the data :

function CopyTaskSource() {
 var sss = SpreadsheetApp.openById('1OPnw_7vTCFkChy8VUKhAG5QRhcpKnDbmod0ZxjG----'); //replace with source ID
 var ss = sss.getSheetByName('TASK Status'); //replace with source Sheet tab name
  var range = ss.getRange('E:L'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1T3tqsHvKxuulYxDnaR3uf-wjVdXwLHBcUgI7tgN----'); //replace with destination ID
 var ts = tss.getSheetByName('TaskSource'); //replace with destination Sheet tab name
 ts.getRange(1, 1, data.length, data[0].length).setValues(data); //you will need to define the size of the copied data see getRange()

}

Now it copies about 15,000 rows of data, and I expect I will end up at 50,000 rows of data (and some other sheets go up to 27 columns).

I started getting this Service error: Spreadsheets line 9 (last line of the code).

imageOfServiceError

Can someone please advise me a workaround to get bulk data transferred to multiple Google spreadsheet files?

importrange doesn't work well, and I have a few Google Forms that I need to combine the source responses to manipulate the data to output presentable spreadsheets.

Thank you

I think the issue is you are exceeding the limit of number of cells allowed per spreadsheet — Jack Brown
Hi there, I've done a count, its well under the limits of 2 million cells in this case. That is why I am trying to copy source data to other sheets to further manipulate otherwise I would exceed the limitations in the original form response when trying to add more information all to one sheet.. — Brandon Marton
Tags should be about related to the core problem. In this case looks that the merging data from Google Forms or any other source doesn't make any difference. — Rubén
@Jack Brown that thread and limit is way out of date, the current limit is 2 Million — Robin Gertenbach

MBenesch MBenesch · Accepted Answer · 2017-07-19T19:08:57

So I am working currently on a script that sends out emails when there is an issue, it then adds an array of values ,containing three values [type, ID, status], to an existing array ending with [[values1],[values2],etc...].

I have gotten the same error when I leave the third parameter of getRange as the array.length. I got it to work once yesterday by subtracting the array.length by 1 as I will show below. Maybe you can try this on line 9 and see if that fixes it?

It is important to mention that today after running the exact same script, it gave me an error stating that the range size was incorrect (due to the same subtraction that seemed to fix the service error)

I think that it may be broken on Google's side, but that is not something I can confirm.

This:

ts.getRange(1, 1, data.length, data[0].length).setValues(data);

Becomes This:

ts.getRange(1, 1, data.length - 1, data[0].length).setValues(data);

Hope that fixes it for you, I am truly stumped as to why it decides to work one day but not another...

I also added a waitLock to make sure it waits for other processes to be finished before trying to write it, but realize the data I write is much smaller, only 3 columns by 6-10 rows at a time. Here is the code for that, though this is to insert the data at the top of the sheet, not the bottom. (From Henrique G. Abreu, Original Post)

function insertRow(sheetI, rowData, optIndex) {
  var lock = LockService.getScriptLock();
  lock.waitLock(30000);
  try { 
    var index = optIndex || 1;
    sheetI.insertRowsBefore(index,rowData.length).getRange(index, 1, rowData.length, 3).setValues(rowData)
    SpreadsheetApp.flush();
  } finally {
    lock.releaseLock();
  }
}

Google App Script Service error: Spreadsheets

1 Answers