How to Transfer Row based on Criteria - Google Apps Script

Question

I am setting up a tool that transfers rows to a different spreadsheet based on the age of the row (>= 30 Days Old).

I have figured out to delete the row based on the criteria (>= 30 Days Old), but I cannot seem to figure out how to move the row over successfully. I have been working on two bits of code that seem like they only need a tweak or two but I cannot figure out what those tweaks are.

The First:

function CopyDataToNewFile() {
  var ss SpreadsheetApp.openById('ID'); // sss = source spreadsheet
  var sheet = ss.getSheetByName('Sheet1'); // sh = source sheet
  var ass = SpreadsheetApp.openById('ID'); // ass = archive spreadsheet
  var as = ass.getSheetByName('Sheet2'); // as = archive sheet 
  var datarange = sheet.getDataRange(); //Get full range of data source
  var a_datarange = as.getDataRange(); // Get full range of archive
  var lastrow = datarange.getLastRow(); // get last row of data source
  var values = datarange.getValues(); // get all data in a 2D array
  var A1Range = datarange.getA1Notation(); //get A1 notation source
  var monthOld = Date.now() + -30 * 24 * 3600 * 1000; //adds 1 month to today

  for (i = lastrow; i >= 1; i--) {
    var tempDate = values[i - 1][4];
    if ((tempDate != "") && (tempDate <= monthOld)) {

      sheet.getRange(datarange.getRow(), 1, 1, sheet.getLastRow()).moveTo(a_datarange);

    } //closes if
  } //closes for loop
} //closes function

Here, I receive the error: "Target range and source range must be on the same spreadsheet. (line 49, file "Delete"):

I believe this means that moveTo() cannot be used here, but I do not know what do use instead.

The Second

function MoveOldEntries() {

  var sss = '1kKFvRT4hCclHQRsQ_4Z3wNifGirXczfViCi31Nw_wD0';
  var ass = '1K2xDQEGnFcUcs3u3Cj86z6UsxR35kdRV_awMFFeXKt0';
  var s_sheet1 = SpreadsheetApp.openById(sss).getSheets()[0];
  var a_sheet1 = SpreadsheetApp.openById(ass).getSheets()[0];
  var lr = s_sheet1.getLastRow();

  var rows = s_sheet1.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (i = lr; i >=1; i--) {
    var oldDate = Date.now() + - 90 * 24 * 3600 * 1000;     // or var oldDate =  new Date('values[i][1]')
    var curDate = new Date();
    var tempDate = values[i-1][4]
    if ((tempDate!="") && (tempDate <= (oldDate))) {
      a_sheet1.appendRow([values])
  }
}
}

This returns the correct number of rows to the archive sheet, but it is gibberish:

[Ljava.lang.Object;@1534974f    
[Ljava.lang.Object;@5fd49386    
[Ljava.lang.Object;@199384d5    
[Ljava.lang.Object;@6cd6e3a6    
[Ljava.lang.Object;@152fec74    
[Ljava.lang.Object;@58300fb3    
[Ljava.lang.Object;@1e50f226

Any ideas on what to change are appreciated. I have been working on this for days!

ra89fi ra89fi · Accepted Answer · 2019-04-17T18:09:55

Use below code. Since you can't move, you need to get values, filter and then set values to destination.

function CopyDataToNewFile() {
  var ss = SpreadsheetApp.openById('ID'); // sss = source spreadsheet
  var sheet = ss.getSheetByName('Sheet1'); // sh = source sheet
  var values = sheet.getDataRange().getValues(); // get all data in a 2D array

  var ass = SpreadsheetApp.openById('ID'); // ass = archive spreadsheet
  var as = ass.getSheetByName('Sheet2'); // as = archive sheet

  var monthOld = Date.now() + (-30 * 24 * 3600 * 1000); //adds 1 month to today
  var results = [];

  values.forEach(function(row) {
    var tempDate = row[4];
    if (tempDate != '' && tempDate <= monthOld) {
      results.push(row);
    }
  });

  // results.unshift(values[0]); // uncomment this if you want the column names from source sheet to copy

  as.getRange(as.getLastRow()+1, 1, results.length, results[0].length).setValues(results);
}

How to Transfer Row based on Criteria - Google Apps Script

1 Answers