CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
onit
onit

Reputation: 2372

How to delete sheets based on a name criteria using Google Apps Script?

I've been trying to delete existing sheets whose names match a criteria, but I keep running into the error below and I can't find out why.

The code I'm running:

const SS = SpreadsheetApp.getActiveSpreadsheet();
const allSheets = SS.getSheets();
const category = 'Block-A';
const newSheetName = 'BOQ ' + category;

for (let a = 0; a < allSheets.length; a++) {
  let existingSheetName = allSheets[a].getName();
  if (existingSheetName == newSheetName) {
    SS.deleteSheet(allSheets[a]);
  }
}

The error: Sheet xxxxxxxxxx not found

Appreciate your help.

Antonio

Upvotes: 0

Views: 389

Answers (1)

onit
onit

Reputation: 2372

After reading through the comments on the question, this is what has worked for me:

First, make sure that the sheet names are listed. In my case, I got a column clientCategories whose each value would become a sheet name:

let sheetsToDel = [];
for (let a = 0; a < clientCategories.length; a++) {
  sheetsToDel.push('BOQ ' + clientCategories[a])
}

Then, I'd go through all the sheets and delete them:

for (let a = 0; a < allSheets.length; a++) {
  const sheetName = allSheets[a].getName()
  for (let r = 0; r < sheetsToDel.length; r++) {
    if (sheetName == sheetsToDel[r]) {
      SS.deleteSheet(allSheets[a]);
    }
  }
}

There may be many better ways to it, but this is what I got.

Upvotes: 1

Related Questions