using query() with apps script keeps adding 500 rows to sheet

Question

I'm working on a large sheet and cells are precious given gsheets quota.

I have a range, and when data updates automatically a script updates the named range automatically to be the full length of the data.

The named range is called "gadatapull". This range is on the tab "datapull".

Two tabs. "datapull" is where fresh data is dumped and "data_prep" is where I do stuff to the data. After a fresh pull just now datapull has 2,733 rows of data, including the headers.

I would like data_prep to have the same length as datapull. Plus 7 rows for text at top of data_prep.

When my script to update data runs I do this:

 // clear dataprep sheet for new data
  var lastRow = 7; 
  var maxRows = dataprep.getLastRow();
  if(maxRows - lastRow > 0) {
    dataprep.deleteRows(lastRow+1, maxRows-lastRow);
  }

data_prep has 7 rows (because the script just deleted all rows above 7).

Now, in data_prep cell A7 I have:

=query(indirect("gadatapull"),"select *")

Expected result was that all the fresh data in "gadatapull" would appear in data_prep tab and that data_prep tab would expand accordingly.

But what actually happens is all the data arrive as expected, but then there are an additional blank 500 rows at the bottom. This 500 number is too rounded off. Makes me think Gsheets is automatically adding this number as a default under some condition.

How can I prevent Gsheets from adding these additional 500 rows?

Serge insas Serge insas · Accepted Answer · 2015-07-01T21:35:49

Instead of letting the sheet API expanding the number of rows (which is your hypothesis and might well be true ;-) you can add all the necessary cells before importing data.

I didn't try in real conditions but this should work.

Btw, the script imports data as well.

code :

function copyDataToSheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataprep = ss.getSheetByName('data_prep');
  var datapull = ss.getSheetByName('datapull');
  var lastRow = 7; 
  var maxRows = dataprep.getLastRow();
  if(maxRows - lastRow > 0) {
    dataprep.deleteRows(lastRow+1, maxRows-lastRow);
  }
  var datapullSize = datapull.getLastRow();
  dataprep.insertRows(7,datapullSize);// insert exactly the number of rows you need.
  var dataToCopy = datapull.getDataRange().getValues()
  dataprep.getRange(7,1,dataToCopy.length,dataToCopy[0].length).setValues(dataToCopy);
}

using query() with apps script keeps adding 500 rows to sheet

1 Answers

code :