Deleting sheets through script

问题:

I've got this code going on:

function deleteSheets(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();
 var transp = ss.getSheetByName("aux").getRange("A2:A").getValues();

 var i =0;
 var c = 0;
 var name;

  for(i in sheets){

  for(c in transp){  

  var nome_transp = transp[c][0];

  switch(name = sheets[i].getName()){


       case name == transp[c]:
       ss.deleteSheet(sheets[i]);
       break;

      case name == "aux":
        ss.deleteSheet(sheets[i]);
        break;

      case name == "nulos":
        ss.deleteSheet(sheets[i]);
        break;

       case name == "Sem Transportadora":
        ss.deleteSheet(sheets[i]);
        break;

  }



}

}

}

I can see through the debbuging that the counters and the values in "sheets" and "transp" are correct, but they never delete their respectives sheets, neither does the "aux", "nulos", and "Sem transportadora" ones. And yes, the sheets got the same name as the arrays.

Any insights? Thanks in advance!

回答1:

I think the problem is on your string compare method. You should use le localeCompare() method instead of logical compare method.

Some informations about localeCompare() on w3schools and on this SO question

Your code should look like:

function deleteSheets(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var transp = new Array();
  var auxSheet = ss.getSheetByName("aux")
  if(auxSheet != null){
    if(auxSheet.getLastRow() > 1){
      transp = auxSheet.getRange(2, 1, ss.getSheetByName("aux").getLastRow()-1).getValues();
    }        
  }

  var i =0;
  var c = 0;

  transp.push(["aux"]);
  transp.push(["nulos"]);
  transp.push(["Sem Transportadora"]);

  for(i in sheets){

    var sheetName = sheets[i].getName(); 

    for(c in transp){

      var name = transp[c][0].toString();

      if(name.localeCompare(sheetName) == 0){

        ss.deleteSheet(sheets[i]);

      }

    }       

  }

}

I put your hard wrote sheet name on the same array as you get on your "aux" sheet, to avoid the switch statement. It's also control the case where your "aux" sheet doesn't exist