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.