Hello and thanks for trying to help,
I am making a small code to copy some data from a spreadsheet to another specified one. To arrange all the data in the new spreadsheet, I need to put it in different sheets, with references name.
The error occurs when a new sheet is created (by copy) and is going to be renamed.
This is working fine most of the time, but not always, which of course, is a problem.
It tells me the error is line 49 tss.getSheets()[i].setName(ref[i]);
, and it is :
Exception: Invalid argument : name
This is strange because names are almost identical for all sheets, for example S36779
is working, but S36780
is not.
See below the code (it is not finished, but the sheet duplicate part is)
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Fiche Produit')
.addItem('Créer une fiche produit','creaficheproduit')
.addSeparator()
.addToUi();
}
function creaficheproduit() {
var tss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = tss.getSheets();
for (i = 0; i < sheets.length-1; i++) {
tss.deleteSheet(sheets[i+1]);
}
var tsh = tss.getSheets()[0];
var ui = SpreadsheetApp.getUi();
var prompt = ui.prompt('Indiquer le lien de la Fiche CS source', 'lien',ui.ButtonSet.OK_CANCEL)
var response = prompt.getResponseText();
var button = prompt.getSelectedButton();
if (button == ui.Button.OK) {
var sss = SpreadsheetApp.openByUrl(response);
var ssh = sss.getSheetByName('Fiche_eclate_CS');
var lrow = getFirstEmptyRowByColumnArray (ssh,"D");
var ref = ssh.getRange("D3:D"+lrow).getValues();
var gamme = ssh.getRange(7,3).getValue();
tsh.getRange(2,6).setValue(gamme);
var dtotal = ssh.getRange(6,7).getValue();
var htotal = ssh.getRange(7,7).getValue();
var dAJ = ssh.getRange(9,7).getValue();
var hAJ = ssh.getRange(10,7).getValue();
tsh.getRange(4,7).setValue(dtotal + " - h" + htotal);
tsh.getRange(5,7).setValue(dAJ + " - h" + hAJ);
for (var i = 0; i < lrow; i++){
if(i == 0){
tss.getSheets()[0].setName(ref[i]);
}
else {
tss.duplicateActiveSheet();
tss.getSheets()[i].setName(ref[i]);
}
}
for (var i = 0; i < lrow; i++){
var sh = tss.getSheets()[i];
sh.getRange(3,6).setValue(ref[i]);
}
}
else{
}
}
function getFirstEmptyRowByColumnArray(sheet,row) {
var column = sheet.getRange(row + "3:" + row);
var values = column.getValues();
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct);
}
Feel free to ask for more details.
Thanks in advance,
I found the mistake, it is a bad one...
In the For
loop to duplicate and rename new sheet, I used :
for (var i = 0; i < lrow; i++){
lrow is the row number of the last reference I need. This is not good because it is not equal to the array length, so it crashes.
Instead, I have to use ref.length, ref being the references array based on the lrow.
for (var i = 0; i < ref.length; i++){
Thanks to all who answered.