CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
David Gall
David Gall

Reputation: 21

setvalues is Adding Rows

I have a simple script that gets values into an array and when I setvalues, it adds rows to my document.

I getvalues starting at row 6115 thru 6154, and when I setvalues to the same range, it adds 6000+ rows to the document and throws an error because the google sheet now has too many cells. The simple script below is taken from a larger function, but even the simplest script below does not work.

Error Msg: This action would increase the number of cells in the workbook above the limit of 2000000 cells.

function test3() {
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sheet=ss.getActiveSheet();

     data = sheet.getRange(6115,88,6154,89).getValues(); 
     sheet.getRange(6115,88,6154,89).setValues(data); 
}

I appreciate any help. thanks.

Upvotes: 1

Views: 1589

Answers (1)

Tanaike
Tanaike

Reputation: 201378

getRange() is "getRange(row, column, numRows, numColumns)". The detail information is here. https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows-numcolumns

The number of all cells in a spreadsheet can be expressed as follows.

f1

From your script, getRange(6115,88,6154,89) can be written as follows.

f2

This value is larger than the limitation of spreadsheet which is displays as an error.

In the case of getValues(), even if there are no data in the range, the size of data array is created for the range. But this is not reflected to the sheet. So no errors occur, even when this getValues() is executed.

But in the case of setValues(), it imports data by expanding sheet, when number of data is larger than the size of sheet. At this time, the error occurs when the data range is over the limitation. In order to avoid this error, it is necessary to be careful for number of cells and range.

In your case, if you want to avoid the error, please reduce the range or create new spreadsheet which is not sheets in this spreadsheet and import the data.

If I misunderstand your question, I'm sorry.

Upvotes: 3

Related Questions