TypeError: Cannot read property 'getRange' of null [duplicate]

Question

I have a spreadsheet with data in columns A:Z. In column AA is the name of a sheet in the workbook. I'm trying to copy the data in A:Z to the sheet with the same name as in AA. I found this script which seemed promising:

function myFunction() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CLAIMED");
  let range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn())
  for (var i = 1; i <= range.getLastRow(); i++) {
    let sourceval = sheet.getRange(i, 1, 1, range.getLastColumn());
    let destname = sheet.getRange(i, range.getLastColumn()).getValue();
    let destsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(destname);
    sourceval.copyTo(destsheet.getRange(destsheet.getLastRow() + 1, 1));
  }
}

However, I'm getting the error

TypeError: Cannot read property 'getRange' of null.

I've looked up answers here but most of them seem to be about using contained/bound scripts. This is a script I put directly in the script editor, so it should be fine. Changing SpreadsheetApp.getActiveSpreadsheet() to open by spreadsheet id did not help.

Thanks for any suggestions!

Answer 1

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("CLAIMED");
  const rg = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn());
  const vs = rg.getValues();
  vs.forEach((r,i)=>{
    let lc = r.length - 1;
    let s = ss.getSheetByName(r[lc]);
    r.pop();
    s.getRange(1,1,1,lc).setValues(r);
  });
}