Unable to populate an array with current code

Question

I'm attempting to check a destination sheet if new data in a master sheet is missing. If so it copies the new data from 3 columns in the master sheet to the destination sheet. I am getting an error at the end because my array is undefined and I'm unsure how to go about fixing the issue. The error is TypeError: Cannot read property "length" from undefined. (line 27, file "Code")

Line 27 is the 2nd to last line (.setValues(arr);)

function Copy() {
    var sourceSheet = SpreadsheetApp.openById('1iyxDeeKoPdVwl15bXTOeorbHylw_i39BsM4NQnxDeiY')
        .getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();
if (prevLastRow == undefined) {
        prevLastRow = (sourceSheet.getLastRow() -1);
        }
else {
var prevLastRow = PropertiesService.getScriptProperties.getProperty('lastRow');
}
if (lastRow <= prevLastRow) {
  return;
}
var range = sourceSheet.getRange(prevLastRow,
                                 1,
                                 lastRow - prevLastRow,
                                 sourceSheet.getLastColumn()
                                ); 
    var arr = [];
    var rangeval = range.getValues()
        .forEach(function (r, i, v) {
            if (r[i][1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
        });
    var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
        .getSheetByName('Sheet1');
    destinationSheet.getRange(destinationSheet.getLastRow() +1, 1, arr.length, arr[0].length)
        .setValues(arr);
PropertiesService.getScriptProperties.setProperty('lastRow', lastRow)
}

Master Sheet image

Target sheet image

edit: I've changed the first If statement to the following:

if (prevLastRow == undefined) {
        prevLastRow = 1;
        }
else {
var prevLastRow = PropertiesService.getScriptProperties.getProperty('lastRow');
}

This returns the values on the other spreadsheet but it does not save the last row as I'm trying to do in the last line of the main code.

2nd EDIT: This code works, but doesn't copy the very last row:

function Copy() {
    var sourceSheet = SpreadsheetApp.openById('1iyxDeeKoPdVwl15bXTOeorbHylw_i39BsM4NQnxDeiY')
        .getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();
if (PropertiesService.getScriptProperties().getProperty('lastRow') == undefined) {
        prevLastRow = 1;
        }
else {
var prevLastRow = PropertiesService.getScriptProperties().getProperty('lastRow');
}
if (lastRow <= prevLastRow) {
  return;
}
var range = sourceSheet.getRange(prevLastRow,
                                 1,
                                 lastRow - prevLastRow,
                                 sourceSheet.getLastColumn()
                                ); 
    var arr = [];
    var rangeval = range.getValues()
        .forEach(function (r, i, v) {
            if (r[1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
        });
    var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
        .getSheetByName('Sheet1');
    destinationSheet.getRange(destinationSheet.getLastRow() +1, 1, arr.length, arr[0].length)
        .setValues(arr);
PropertiesService.getScriptProperties().setProperty('lastRow', lastRow)
}

TypeError: Cannot read property "length" from undefined. (line 27, file "Code") — M S
Add that to the question and please indicate which is the line 27. — Rubén
Try logging arr and arr[0] and see what they evaluate to. Logger.log(arr), Logger.log(arr[0]). I suspect arr[0] simply doesn't exist in your case (empty array) so when you try to set the number of columns in the range equal to the length of 'undefined' it obviously throws an error — Anton Dementiev
Actually, the 'previous last row' in your master sheet is 17 and has 'Steve' in the first column. That means this condition if (r[i][1] == 'Amber') will NEVER be true. The 0th element of the array will thus be undefined — Anton Dementiev

M S M S · Accepted Answer · 2018-01-12T02:41:08

Here is a working version for anyone looking for something similar. :)

Solution from my last edit was to shift the line that begins "var range = ..." Changed lastRow - prevLastRow to (lastRow - prevLastRow)+1 and it records the last row.

function Copy() {
    var sourceSheet = SpreadsheetApp.openById('1iyxDeeKoPdVwl15bXTOeorbHylw_i39BsM4NQnxDeiY')
        .getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();
if (PropertiesService.getScriptProperties().getProperty('lastRow') == undefined) {
        prevLastRow = 1;
        }
else {
var prevLastRow = PropertiesService.getScriptProperties().getProperty('lastRow');
}
if (lastRow <= prevLastRow) {
  return;
}
var range = sourceSheet.getRange(prevLastRow,
                                 1,
                                 (lastRow - prevLastRow)+1 ,
                                 sourceSheet.getLastColumn()
                                ); 
    var arr = [];
    var rangeval = range.getValues()
        .forEach(function (r, i, v) {
            if (r[1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
        });
    var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
        .getSheetByName('Sheet1');
    destinationSheet.getRange(destinationSheet.getLastRow(), 1, arr.length, arr[0].length)
        .setValues(arr);
PropertiesService.getScriptProperties().setProperty('lastRow', lastRow)
}

Unable to populate an array with current code

1 Answers