1

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,

mat
  • 23
  • 6
  • ref seems to be a 2d array here. What do you get when you log or inspect ref ? You probably need to get the element from the ref properly e.g. ref[i][j]. – Karan Nov 03 '21 at 16:15
  • Try `String(ref[i][0])` instead. – TheMaster Nov 03 '21 at 16:26
  • I agree with the existing comments, ref[i] is an array. If that will not fix your issue, please provide a sample sheet for replication. [Share a test sheet](https://webapps.stackexchange.com/a/138383) – Ron M Nov 03 '21 at 16:49
  • Thanks for all the replies. I tried to change with string `String(ref[i][0])`, but this is changing the problem to : "TypeError: Cannot read property '0' of undefined". The one working before are still working, the one that was not working have the error. – mat Nov 04 '21 at 08:14
  • @RonM sorry I can't share datas I need to put into those sheets. If you want to try the code, you can just put random data in range D3-Dxx in spreadsheet 1 and launch the code in spreadsheet 2 using spreadsheet's 1 URL. – mat Nov 04 '21 at 08:23

1 Answers1

1

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.

mat
  • 23
  • 6