How to GetSheetByName using partial string in google apps script

user_12344144 0
Viewed 1091

I am trying to use .getSheetByName('') to grab a sheet whose name contain a certain string like, 'V1' or 'V4', not an exact match. Say the name of the sheet is '2020 0304 V1', the first part is always changing but it contains V1, I tried .getSheetByName('*V1') but it is not working. Any hint on how to achieve this?

google-apps-script google-sheets
1 Answers

Issue and workaround:

Unfortunately, in the current stage, the method like the regex cannot be used with getSheetByName(). So in this case, it is required to use the workarounds.

In this answer, I would like to propose 2 patterns.

Pattern 1:

In this pattern, test is used for searching the sheet name. getSheetName() is used for retrieving the sheet name. In this case, it supposes that the pattern is like 2020 0304 V1.

Sample script:

function myFunction() {
  const searchText = "V1";
  
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  const regex = RegExp(`\\d{4} \\d{4} ${searchText}`);
  const sheet = sheets.filter(s => regex.test(s.getSheetName()));
  if (sheet.length > 0) console.log(sheet[0].getSheetName());
}
  • In this case, if there is only one sheet which has the sheet name of the pattern of \d{4} \d{4} V1 in your Spreadsheet, you can retrieve the sheet by sheet[0].

Pattern 2:

In this pattern, includes is used for searching the sheet name. getSheetName() is used for retrieving the sheet name.

Sample script:

function myFunction() {
  const searchText = "V1";

  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  const sheet = sheets.filter(s => s.getSheetName().includes(searchText));
  if (sheet.length > 0) console.log(sheet[0].getSheetName());
}
  • In this case, if there is only one sheet which has the sheet name including V1 in your Spreadsheet, you can retrieve the sheet by sheet[0].

Note:

  • In this case, please enable V8 at the script editor.

References:

user_71086530
Related