CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Rob Campbell
Rob Campbell

Reputation: 63

how to get deleteSheet to work for complex sheet names

I have a Google Add-on that manages a few sheets in a Google spreadsheet. One of the things it does is remove sheets based on dates, which are the names of the sheets it deletes. In my current development phase I'm also adding the capability to remove sheets that include the date and another term, specifically "script 6/5/2019", for example.

I'm using the same code that worked for the sheets named with the date and made some adjustments to it, but it returns an error when it comes time to delete the sheet: "Cannot find method deleteSheet(string)"

 if(sRemove==true) {
  SpreadsheetApp.getActiveSpreadsheet().toast('Finding Old Scripts', 'Status',3);
    for (var i = ss.getNumSheets()-1; i >= 0; i--) {
    
        var thisTab = ss.getSheets()[i].getName();
        if(thisTab.substr(0,6) =="script") {
        Logger.log(i+" "+thisTab+" is a script");
        var tabDate = new Date(thisTab.substr(8)); 
        //8 is the first digit of the date
        Logger.log(tabDate);
        var thisDate = new Date(todayDate);
  
        var patt = new RegExp("s");
        var res = patt.test(thisTab);
        Logger.log(i+" "+res);

        if(tabDate<thisDate && res==true) { 
           var ss = SpreadsheetApp.getActiveSpreadsheet();
           //ss.setActiveSheet(ss.getSheetByName(thisTab));
           //ss.deleteActiveSheet(ss.getSheetByName(thisTab));   
           Logger.log(thisTab);
           ss.deleteSheet(thisTab);
           tabsGone++;
        }
     }
  }
  ui.alert(tabsGone+" Sheets Removed");
  }

Logger.log returns the correct name of the sheet to be removed, but deleting the sheet returns the error "Cannot find method deleteSheet(string)"

Upvotes: 0

Views: 710

Answers (1)

ross
ross

Reputation: 2774

Requirement:

Delete sheet based on name.

Solution:

Use getSheetByName() to get the sheet object then pass this to your deleteSheet():

ss.deleteSheet(ss.getSheetByName(thisTab));

Explanation:

Currently you're passing string thisTab to deleteSheet(), this won't work because it is expecting a sheet object, not a string. As long as your code above is working properly and thisTab matches your sheet name exactly, all you need to do is call getSheetByName(thisTab) to get the sheet object then pass this to deleteSheet().

References:

  1. getSheetByName(string)
  2. deleteSheet(sheet)

Upvotes: 5

Related Questions