Issue with ss.insertSheet - inserted sheet has wrong name

Question

Hello I wrote a small script to copy one template sheet in a spreadsheet, as a new sheet in the same spreadsheet.

I wrote two versions of it, one driven by a menu that asks for the name of the new sheet to be created:

function addonenewSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var temp = ss.getSheetByName('template');
    var naame = Browser.inputBox("CustomerID to be created");
    try {
        ss.setActiveSheet(ss.getSheetByName(naame));
    } 
    catch (e) {
        ss.insertSheet(naame, {template:temp});
    }
}

This one works as intended, and names the new sheet 234 if I say so in the inputbox.

The second function is very similar, but parses some values and attempts to create many sheets at once:

function addmissingSheets() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var temp = ss.getSheetByName('template');
    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getDataRange().getValues();
    for (var i = 10; i < data.length; i++) {
        if(typeof data[i][1] == 'number'){
            try {
                ss.setActiveSheet(ss.getSheetByName(data[i][1]));
            } 
            catch (e) {
                Logger.log('Customer ID: ' + data[i][1]);
                var insertpage = data[i][1];
                ss.insertSheet(insertpage, {template:temp});
            }
         }
     }
 }

As long as Logger.log is concerned, data[i][1] has the right value, but somehow insertSheet creates sheets named "copy of template", "copy of template 2"... Instead of taking the value assigned in data[i][1]

Would anyone know why this behaviour and how I can solve this issue?

Some of the data on the sheet is sensitive, and when I remove it, the functions don't have an input anymore... However, the output of logger (thus value of data[i][1]) is [15-11-02 15:04:17:475 CET] Customer ID: 59 [15-11-02 15:04:17:476 CET] Customer ID: 58 [15-11-02 15:04:17:477 CET] Customer ID: 57 [15-11-02 15:04:17:478 CET] Customer ID: 223 ....... thus the values should be similar to the 234 I gave in the inputbox example — user1747036

Vytautas Vytautas · Accepted Answer · 2015-11-03T06:58:01

your second script does not use correct variable types. The method you are using insert sheet uses types (<string>, {template:<sheet>}). Since your customer ID is a number it does not work. There is a simple fix you can do

Change

var insertpage = data[i][1];

into:

var insertpage = data[i][1].toString();

and you will now be able to use the customer ID (which is a number) to create a sheet name (which is a string)

Issue with ss.insertSheet - inserted sheet has wrong name

1 Answers