Exception: Invalid argument: id

Question

I have a code that's doing what I expect it to do but I'm still getting the captioned error message. I don't want to have to ignore it each time I run the script. I'd be happy if someone can help me clean this up.

A bit of background: I'm using this code to copy a template to multiple different workbooks and each time its copied to give it a new name. The sheet has two/three tables so the rest of the code adds additional column headers and data validations depending on where the tables start and end and this will vary.

Like I mentioned. It appears to be working fine but I can't understand why there's an exception error.

Exception: Invalid argument: id at unknown function at copyTemplate(copyTemplate:13:7)

function onOpen(e) {
      SpreadsheetApp.getUi()
        .createMenu('Copy Template')
        .addItem('Copy Template', 'copyTemplate')
        .addToUi();
}

function copyTemplate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const template = sheet.getSheetByName('Oct 22');
  const ish = sheet.getSheetByName('Sheet IDs'); 
  const ivs = ish.getRange('B2:B' + ish.getLastRow()).getValues().flat(); 
  ivs.forEach((id,i) => {
  let destination = SpreadsheetApp.openById(id);   // <-- destination sheet ID
  var copied_sheet = template.copyTo(destination);
  var newName = Browser.inputBox("New Sheet Name","Input new sheet name",Browser.Buttons.OK_CANCEL); //creates a dialog box asking for a new name input
  copied_sheet.setName(newName); //renames your sheet
  copied_sheet.activate();
  copied_sheet.getRange("E2").setNumberFormat('@STRING@').setValue(newName);
  destination.moveActiveSheet(1); // move the copied sheet in first position
  
  var osh = destination.getActiveSheet();
  const r = osh.createTextFinder("Client Name").findAll();
  for (var i = 0; i < r.length; i++) {
    var range = r[i];
    var comments = [["Comments", "Additional Comments"]];
    var rowValue = osh.getRange(range.getRow(), 15, 1, 2).setValues(comments);

    var ddSheet = sheet.getSheetByName('Dropdowns');
    var arRange1 = ddSheet.getRange('A2:A' + ddSheet.getLastRow());
    var wipRange2 = ddSheet.getRange('B2:B' + ddSheet.getLastRow());

    var dataRange1 = osh.getRange(r[0].getRow(),1,osh.getLastRow(),16);
    var dataRange2 = osh.getRange(r[1].getRow(),1,osh.getLastRow(),16);
    var firstBlankRow = dataRange1.getValues().findIndex(row => !row.join(''));
    var secondBlankRow = dataRange2.getValues().findIndex(row => !row.join(''));
    var arCell = osh.getRange(r[0].getRow() +1,15,firstBlankRow-1,1);
    var wipCell = osh.getRange(r[1].getRow() +1,15,secondBlankRow-1,1);

    var arRule = SpreadsheetApp.newDataValidation().requireValueInRange(arRange1).build();
    var wipRule = SpreadsheetApp.newDataValidation().requireValueInRange(wipRange2).build();
    arCell.setDataValidation(arRule);
    wipCell.setDataValidation(wipRule);

    var arComments = osh.getRange(r[0].getRow() +1,15,firstBlankRow-1,2);
    var wipComments = osh.getRange(r[1].getRow() +1,15,secondBlankRow-1,2);
    arComments.setBorder(true,true,true,true,true,true,"red",SpreadsheetApp.BorderStyle.DOUBLE);
    wipComments.setBorder(true,true,true,true,true,true,"red",SpreadsheetApp.BorderStyle.DOUBLE);
  }

  })
}

score 0 · Answer 1 · answered Nov 03 '22 at 05:46

From your showing error message of Exception: Invalid argument: id, I'm worried that in your actual situation, the value of const ivs = ish.getRange('B2:B' + ish.getLastRow()).getValues().flat(); might include the empty value. If my understanding is correct, how about the following modification?

From:

const ivs = ish.getRange('B2:B' + ish.getLastRow()).getValues().flat();

To:

const ivs = ish.getRange('B2:B' + ish.getLastRow()).getValues().flat().filter(String);

Or, if your values of ivs include the invalid file IDs, how about the following modification?

From:

const ivs = ish.getRange('B2:B' + ish.getLastRow()).getValues().flat(); 
ivs.forEach((id,i) => {
let destination = SpreadsheetApp.openById(id);

To:

const ivs = ish.getRange('B2:B' + ish.getLastRow()).getValues().flat().filter(String);
ivs.forEach((id, i) => {
  if (UrlFetchApp.fetch(`https://drive.google.com/thumbnail?id=${id}`, { muteHttpExceptions: true }).getResponseCode() != 200 || DriveApp.getFileById(id).getMimeType() != MimeType.GOOGLE_SHEETS) return;
  let destination = SpreadsheetApp.openById(id);

This method is from https://stackoverflow.com/a/74249592 .

Note:

This answer is for your error message of Exception: Invalid argument: id. Please be careful about this.

Exception: Invalid argument: id

1 Answers1

From:

To:

From:

To:

Note: