Google Sheets - Script; getRange getLastRow copyTo-CodePudding

I'm rewriting a daily tracker that takes a row of data, and appends it to the last row on a second sheet for historical data.

var ss = SpreadsheetApp.getActiveSpreadsheet (); 
var source = ss.getRange ("TODAY_TRACKER!P2:T2"); 
var destSheet = ss.getSheetByName("DATA"); 
var destRange = destSheet.getRange(destSheet.getLastRow() 1,1); 
source.copyTo (destRange, {contentsOnly: true});

This just copies the row to the last row beginning at 1,1 on the data sheet.

I'm updating the tracking sheet to take the days numbers and make a table of weekly data; I want to append this row of data to the last row at a specific position on the same sheet.

var source = ss.getRange("NEW_TTRACKER!J3:O3"); 

var destRange = ss.getRange(ss.getLastRow(7,10,1,6) 1,1);

source.copyTo (destRange, {contentsOnly: true});

This obviously doesn't work, I was just taking a stab.

I'm attempting to copy the first row of data to column 7 row 10 for 1 row and 6 columns. Then subsequent rows in the days following to go beneath the last.

Help is appreciated, thanks in advance.

CodePudding user response：

It does not work since there's a syntax error on your destRange. First it's missing one opening parenthesis, second you're adding an incorrect syntax on the getRange function. The first parameter you used did not include some additional methods to pull the range and get the last row of the range. Also using getLastRow() will get the last row on the entire sheet so if you have any other data below the required range it will probably count that as well. So instead, we can approach this by getting values of data on the range and using length to determine which subarray has the last data and use that as reference to when moving to the next blank row.

See code below

function copyDataArray() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var destSheet = ss.getSheetByName("NEW_TTRACKER");
  var source = destSheet.getRange("J3:O3");

  //Define the position of the last available data on the weekly section
  var lastrowonrange = destSheet.getRange("J7:J11").getValues().filter(String).length;

  //Selects the area whare to past the data based on the last available data
 var destRange = destSheet.getRange(lastrowonrange 7,10,1,6);
 source.copyTo(destRange, {contentsOnly: true});
};

I'm able to run it properly that it appends data on the destRange.