Google App Script: TypeError: Cannot read property 'length' of undefined (line *, file "Code")

Solution 1:

In your situation, for example, how about the following modification? I modified 2 parts in the script.

Modified script:

function Q70718274_importNewEmployeeIds() {
  const srcSheetName = 'Source';
  const dstSheetName = 'Destination';
  const srcSheet = SpreadsheetApp.openById("SpreadsheetID1").getSheetByName(srcSheetName);

  const dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dstSheetName);

  // Retrieve values from source sheet.
  var headerRowNumber = 1;
  const srcValues = srcSheet.getDataRange().offset(headerRowNumber, 0, srcSheet.getLastRow() - headerRowNumber).getValues().filter(r => r.join("") != ""); // <--- Modified

  // Retrieve values from destination sheet and create an object for searching the ID.
  const dstLastRow = dstSheet.getLastRow();
  const dstObj = dstLastRow == 0 ? {} : dstSheet.getRange("A1:A" + dstLastRow).getValues().reduce((o, [a]) => (o[a] = true, o), {});

  // Create an array for putting to the destination sheet.
  const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], r[19], "", r[18]]);

  // Put the values to the destination sheet.
  if (values.length > 0) { // <--- Added
    dstSheet.getRange(dstLastRow + 1, 1, values.length, values[0].length).setValues(values);
  }
}

• By const srcValues = srcSheet.getDataRange().offset(headerRowNumber, 0, srcSheet.getLastRow() - headerRowNumber).getValues().filter(r => r.join("") != "");, the empty rows are removed.
• By if (values.length > 0) {}, when values has no values, no error occurs.

Added:

From your following additional information,

[Source] Before script is run.

|  A   |B|  C   |  D   |
|200001| |Email2|Email1|
|200001| |Email2|Email1|
|200006| |Email4|Email3|
|200007| |Email6|Email5|
|      | |      |      |

[Destination] Before script is run.

|  A   |B|  C   |  D   |
|200001| |Email2|Email1|

[Destination] After script is run.

|  A   |B|  C   |  D   |
|200001| |Email2|Email1|
|200006| |Email4|Email3|
|200007| |Email6|Email5|
|      | |      |      | <-- Notice the blank row. I don't want the blank row.

Once the script runs, it copies row data from source to destination. The problem is, the script will also copy the blank (empty) rows to the source sheet.

[Destination] Expected Results - No blank rows

|  A   |B|  C   |  D   |
|200001| |Email2|Email1|
|200006| |Email4|Email3|
|200007| |Email6|Email5|

I understood that you want to achieve the bottom table from the top 2 tables. From your provided script, I had thought that you wanted to use const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], r[19], "", r[18]]);. But, from your additional situations, I understood that my understanding was not correct. For your additional sample situation, how about the following script?

Sample script:

function importNewEmployeeIds2() {
  const srcSheetName = 'Source';
  const dstSheetName = 'Destination';
  const srcSheet = SpreadsheetApp.openById("SpreadsheetID1").getSheetByName(srcSheetName);

  const dstSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dstSheetName);

  // Retrieve values from source sheet.
  var headerRowNumber = 1;
  const srcValues = srcSheet.getDataRange().offset(headerRowNumber, 0, srcSheet.getLastRow() - headerRowNumber).getValues().filter(r => r.join("") != ""); // <--- Modified

  // Retrieve values from destination sheet and create an object for searching the ID.
  const dstLastRow = dstSheet.getLastRow();
  const dstObj = dstLastRow == 0 ? {} : dstSheet.getRange("A1:A" + dstLastRow).getValues().reduce((o, [a]) => (o[a] = true, o), {});

  // Create an array for putting to the destination sheet.
  // const values = srcValues.filter(r => !dstObj[r[1]]).map(r => [r[1], r[19], "", r[18]]);
  const values = srcValues.filter(r => !dstObj[r[0]]).map(r => [r[0], "", r[2], r[3]]); // <--- Modified

  // Put the values to the destination sheet.
  if (values.length > 0) { // <--- Added
    dstSheet.getRange(dstLastRow + 1, 1, values.length, values[0].length).setValues(values);
  }
}