Please help! I'm not script-savvy, but I've managed to piece together a few scripts to help my school district create individualized google sheet workbooks for each student in my district's elementary schools. We have folders with approximately 150 google sheets workbooks per grade level. All 150 workbooks in each folder have the same tabs (sheets). I want to be able to delete a specific tab/sheet from all 150 workbooks in one of the folders. The container-bound script I was using to delete tabs suddenly stopped working. It now only deletes the tab for the workbook it is attached to, where just a few weeks ago, it deleted the tab from all 150 workbooks. I must have inadvertently changed something, but I can't figure out what. Any help would be much appreciated!

Here's my script:

function onOpen() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
}

function deleteSheets() {
    var sourceFolder = DriveApp.getFolderById("FOLDER ID");
    var folderFiles = sourceFolder.getFiles();
    var source = SpreadsheetApp.getActiveSpreadsheet().getSheets()[2];'
    SpreadsheetApp.getActiveSpreadsheet().deleteSheet(source); 
}

CodePudding user response：

Delete Sheets from Spreadsheets by name

function dsh(n="Second") {
  const ss = SpreadsheetApp.getActive();
  const folder = DriveApp.getFolderById("Folder id");
  const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while(files.hasNext()) {
    let f = files.next();
    let ss = SpreadsheetApp.openById(f.getId());
    let sh = ss.getSheetByName(n);
    ss.deleteSheet(sh)
  }
}

CodePudding user response：

In your script, it seems that the 3rd sheet of the active Spreadsheet is deleted. And, the files in the specific folder are not used. I think that this is the reason for your current issue. In order to delete the specific sheet (3rd sheet) in each Google Spreadsheet in the specific folder of FOLDER ID. How about the following modification?

Modified script:

function deleteSheets() {
  var sourceFolder = DriveApp.getFolderById("FOLDER ID");
  var files = sourceFolder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (files.hasNext()) {
    var file = files.next();
    var ss = SpreadsheetApp.open(file);
    var deleteSheet = ss.getSheets()[2]; // or var deleteSheet = ss.getSheetByName("sheetName");
    if (deleteSheet) {
      ss.deleteSheet(deleteSheet);
    }
  }
}

• When this script is run, Spreadsheet files are retrieved from FOLDER ID folder. And, the 3rd sheet in each Spreadsheet is deleted.
• If you want to delete the sheet by the sheet name, please modify var deleteSheet = ss.getSheets()[2]; to var deleteSheet = ss.getSheetByName("sheetName");

Note:

• In this script, a sheet is deleted from each Spreadsheet. So, as a test of this script, I would like to recommend using a sample Spreadsheet.

References:

• getFilesByType(mimeType)
• Class File
• open(file)