Home > Mobile >  Google Apps Script Error "Exception: Invalid argument: options. Should be of type: Map"
Google Apps Script Error "Exception: Invalid argument: options. Should be of type: Map"

Time:10-30

I'm trying to make a quick script to copy information from a series of spreadsheets into a single spreadsheet. I'm getting an error

"Exception: Invalid argument: options. Should be of type: Map".

I know very little about maps, and this error makes no sense to me.

function make_new_contact_lists() {
  DriveApp.getFileById("1IZBpDno4MOBeiGJeIKd2ykHpCr2359jhcV_SJpSuQ94");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var sheet = ss.getSheetByName("Sheet2")
  ss.setActiveSheet(sheet);
  var lr = sheet.getLastRow();
  var range = sheet.getRange("A2:B"   lr).getValues();
  var list = [];
  list.push([range]);
  for (i = 0; i < list.length; i  ) {
    var entry = list[i];
    var name = entry[0];
    var fileID = entry[1];

    var newsheet =
      SpreadsheetApp.openById("1IZBpDno4MOBeiGJeIKd2ykHpCr2359jhcV_SJpSuQ94");
    SpreadsheetApp.setActiveSpreadsheet(newsheet);

    /**-->This is where the error gets flagged**/
    newsheet.insertSheet(name);
    var newsheet2 = newsheet.setActiveSheet(name);

    var file = SpreadsheetApp.getFileById(fileID);
    var spreadsheet = file.setActiveSpreadsheet();
    var current = spreadsheet.getSheetByName("Current Contacts");
    spreadsheet.setActiveSheet(current);
    var range2 = current.getRange("A1:G3").getValues();
    current.setValues
  }
}

Thank you for any help you can provide

CodePudding user response:

Issue:

var range = sheet.getRange("A2:B" lr).getValues();//[[a2,b2],..]
var list = [];
list.push([range]);  //[[[[a2,b2],[a3,b3],..]]]
for (i=0; i < list.length; i  ){
  var entry = list[i];//[[[a2,b2],[a3,b3],..]]
  var name = entry[0];//[[a2,b2],[a3,b3],..]

list is a 4D array and name is a 2D array. newsheet.insertSheet(name) expects name to be a string or object, whereas the script is providing a 2D array.

Solution:

Directly access range as a 2D array, which makes name a string

Snippet:

const range = sheet.getRange("A2:B" lr).getValues();//[[a2,b2],..]
for (const i=0; i < range.length; i  ){
  var entry = range[i];//[a2,b2]
  var name = entry[0];//"a2"

CodePudding user response:

Some of your code simply does not make sense. You might be better of to start again with this:

function make_new_contact_lists() {
  DriveApp.getFileById("1IZBpDno4MOBeiGJeIKd2ykHpCr2359jhcV_SJpSuQ94");
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet2")
  const lr = sh.getLastRow();
  const vs = sh.getRange("A2:B"   lr).getValues();
  
  const ss1 = SpreadsheetApp.openById("1IZBpDno4MOBeiGJeIKd2ykHpCr2359jhcV_SJpSuQ94");
  for (i = 0; i < vs.length; i  ) {
    let entry = vs[i];
    let name = entry[0];
    let fileID = entry[1];
    let nsh = ss1.insertSheet(name);
    let ss2 = SpreadsheetApp.openById(fileID);
    let csh = ss2.getSheetByName("Current Contacts");
    let vs2 = csh.getRange("A1:G3").getValues();
    //the rest of the code makes no sense
    csh.setValues
  }
}

And don't just make up methods go read the documentation about them paying attention to parameters and return values.

  • Related