Use wildcard when searching for sheet name in google sheets combined with clear range. GApp Script

user_11041959 0
Viewed 48

I need help because I'm really newbie in this coding stuff.

I have a spreadsheet with lots of sheets, and it takes a lot of work to erase each tab.

I have seen a post here and tried to adapt to my needs but it won't work.

I need: Clear a range of text in each sheet that has in the name "Cicle".

I have 15 sheets from Cicle 1 to Cicle 15.

I need the ranges from B5:D15 and B20:D35 and F5:F35 to be clear, from each cicle. I have an image icon of a trash and I assign the script to this icon.

Here's what I got:

function clearTextCicle() {
  var sourceSS = SpreadsheetApp.getActive().find(sheet =>
    sheet.getName().includes("'Cicle'"))
      sheet.getRange('B5:B15').clearContent();
      sheet.getRange('F5:F15').clearContent();
}

Thank you for your attention and any help!

google-apps-script wildcard
1 Answers

In your situation, how about the following modification?

Modified script:

function clearTextCicle() {
  var search = "Cicle";
  var len = search.length;
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets().filter(s => s.getName().slice( 0, len ) == search);
  if (sheets.length == 0) return;
  sheets.forEach(s => s.getRangeList(['B5:B15', 'F5:F15']).clearContent());
}

  • In this modification, the sheets of the specific sheet names like Cicle## using filter. And, 2 ranges are cleared using the range list.

  • In this case, I think that you might be able to also use var sheets = ss.getSheets().filter(s => /Cicle\d+/.test(s.getName())); instead of var sheets = ss.getSheets().filter(s => s.getName().slice( 0, len ) == search);.

References:

user_71086530
Related