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

Question

The Goal

Prevent the script from copying blank (empty) rows from source to destination which is causing the error.

---

Error Details:

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

This error, refers to the line: dstSheet.getRange(dstLastRow + 1, 1, values.length, values[0].length).setValues(values);

---

The full code

I am using the following code from HERE which has been slightly modified. The error occurs with and without the modification. The logic of the code is: If destination sheet does not contain ID, import ID, and specific columns from source to destination.

function 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();

  // 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.
  dstSheet.getRange(dstLastRow + 1, 1, values.length, values[0].length).setValues(values);
}

Troubleshooting

[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|

---

Rookie Solutions that I can think of:

Option A) At the beginning of the script, I could first delete all empty rows from the destination. This is less efficient, and does not fix the root of the issue, but at least it fixes my problem.

function removeEmptyRows(){
  var sh = SpreadsheetApp.getActiveSheet();
  var maxRows = sh.getMaxRows(); 
  var lastRow = sh.getLastRow();
  if (maxRows-lastRow != 0){
    sh.deleteRows(lastRow+1, maxRows-lastRow);
    }
}

Code found: HERE

---

Option B) Update the original code so it does not copy the blank rows.

[BEFORE]

  const srcValues = srcSheet.getDataRange().offset(headerRowNumber, 0, srcSheet.getLastRow() - headerRowNumber).getValues();

[AFTER]

  const srcValuesAndBlankRows = srcSheet.getDataRange().offset(headerRowNumber, 0, srcSheet.getLastRow() - headerRowNumber).getValues();
  const srcValues = srcValuesAndBlankRows.filter(String); //Removes Blank Rows

Code Found: HERE

Answer 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);
  }
}