Google Script - Copy dynamic range to new spreadsheet

Question

I'd appreciate some help on my first google script if possible.

I'm trying to archive our daily processing log by copying and pasting them to the bottom of an end of day log spreadsheet.

The issue is that the number of rows processed each day will vary so I can't set a fixed number of rows to copy to the target spreadsheet.

I just want to copy from row B7 down to the last row with values entered.

function pasteprocessinglog() {

  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sss.getSheetByName('Sheet_name');
  var range = ss.getRange('B7:J');
  var data = range.getValues();

  var tss = SpreadsheetApp.openById('URL');
  var ts = tss.getSheetByName('Processing Log');
  ts.getRange(ts.getLastRow() + 1, 2, ss.getMaxRows(), 9).setValues(data);
}

I'm currently receiving error: Incorrect range height, was 6467 but should be 6473

I'm guessing this is because it's trying to copy the empty rows too and the spreadsheet isn't long enough.

Any help would be appreciated :)

Thank you!

Tanaike Tanaike · Accepted Answer · 2016-12-22T08:58:38

In data retrieved using ss.getRange('B7:J').getValues(), empty cells are included. So the length of retrieved data is larger than that of real data. 6467 and 6473 means the length of data array and the value from getMaxRows(), respectively.

And getMaxRows() retrieves the number of most bottom cell including empty cells. So in the case for using setValues(), data can be copied by using the length of data array for setValues as numRows of getRange (https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer) ).

The script is as follows.

function pasteprocessinglog() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sss.getSheetByName('Sheet_name');
  var firstrow = 7; // 7th row
  var range = ss.getRange(firstrow, 2, ss.getLastRow() - firstrow + 1, 9);
  var data = range.getValues();

  var tss = SpreadsheetApp.openById('URL');
  var ts = tss.getSheetByName('Processing Log');
  ts.getRange(ts.getLastRow() + 1, 2, data.length, 9).setValues(data);
}

If my understanding have mistaken, I'm sorry.

Google Script - Copy dynamic range to new spreadsheet

1 Answers