Google App Script: TypeError: Cannot read property ‘length’ of undefined (line *, file “Code”)

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

Changes:

  • Added var headerRowNumber = 1;
  • Replaced
    const srcValues = srcSheet.getDataRange().getValues(); with
    const srcValues = srcSheet.getDataRange().offset(headerRowNumber, 0, srcSheet.getLastRow() - headerRowNumber).getValues(); to avoid copying the header row from source.

Error Details:

With or without these changes above, the error still occurs.

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 Goal

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

The full code

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

On first execution of script, its successful, this is because the destination sheet has no blank (empty) rows. Once the script executes (copies row data from source to destination), it also copies the blank (empty) rows in the source sheet. Now that destination has blank(empty) rows, the script fails with this error.


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

This code does not work. The blank rows are still copied. I suspect this is because I am using getDataRange() not getRange() like the original answer?


Option C) Modify the code so it’s not sensitive to spaces in the destination sheet. (no idea how to do this)


Option D) A better solution?