Google spreadsheets, Delete sheets from multiple spreadsheets

Question

I'm trying to delete one specific sheet from multiple google spreadsheets.

I have a master spreadsheet that collects data from all the other spreadsheets. From the master spreadsheet I'm able to do different kind of actions in the other spreadsheets, like adding sheets, renaming sheets, hide and lock sheets.

But not been able to delete sheets in the other spreadsheets. Looked in other threads but can´t find anything to solve it.

This is what I´ve got so far. It stops at this row:

"fname.deleteSheet(thisweek);}"

    function DeleteSheet() {

  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sss.getActiveSheet();

  var range = sheet.getRange("Z1:Z").getValues();
  var filtered_r = range.filter(String).length;    
  var range = SpreadsheetApp.getActiveSheet().getRange('Z2:Z'+filtered_r);
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {

      //Get current file ID in column Z
      var currentValue = range.getCell(i,j).getValue();

  var currentweekneedstobedefined = SpreadsheetApp.getActiveSheet().getRange(1, 3);
  var thisweek = currentweekneedstobedefined.getValue();
      ss = SpreadsheetApp.openById(currentValue); 
  var dsheet = ss.getSheetByName(thisweek);
     file = ss 
  var fname = file.getName();
  var thisweeksSheet = file.getSheetByName(thisweek);    

    if (dsheet != null) {
    fname.deleteSheet(thisweek);}

 else {

    }
   }
  }
}

I appreciate any help on this since I'm a newbie in coding.

ziganotschka ziganotschka · Accepted Answer · 2020-04-08T11:45:48

var fname = file.getName(); does not return you an instance of the spreadsheet, but only its name
To delete a sheet, you need the spreadsheet instance itself: ss
Retrieving the spreadsheet name is redundant

Solution:

Modify

      ss = SpreadsheetApp.openById(currentValue); 
  var dsheet = ss.getSheetByName(thisweek);
     file = ss 
  var fname = file.getName();
  var thisweeksSheet = file.getSheetByName(thisweek);    

    if (dsheet != null) {
    fname.deleteSheet(thisweek);}

to

  ss = SpreadsheetApp.openById(currentValue); 
  var dsheet = ss.getSheetByName(thisweek);
  if (dsheet != null) {
    ss.deleteSheet(dsheet);
    }

Google spreadsheets, Delete sheets from multiple spreadsheets

1 Answers

Solution: