I am currently developing an onEdit(e) script that will highlight cells in a Google spreadsheet (specifically in a certain column) after a specified user has made edits. The challenge I face is ensuring this functionality applies to all spreadsheets with a particular naming convention, as well as all tabs within those spreadsheets that have another specific naming convention. I want to avoid affecting all spreadsheets and tabs indiscriminately.
Here is the code I have so far (partially sourced from Is there a way to automatically highlight changes made in google sheets):
function onEdit(e) {
var sheetsToWatch = ['Template Week'];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = sheet.getActiveCell();
var column = cell.getColumn();
var sheetName = sheet.getName();
var matchFound = false;
var user = e.user;
var email = user.getEmail();
if(email == '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2c49544d415c4049024f4341">[email protected]</a>' && column == 7)
//if user is Example and editing in Column G - Small Listed Price
{
for (var i = 0; i < sheetsToWatch.length; i++) {
if (sheetName.match(sheetsToWatch[i]))
matchFound = true;
}
}
if (!matchFound) return;
// if not found end
//if found execute color change
var rowColLabel =
sheet.getRange(cell.getRow(),cell.getColumn()).setBackground('#ff9900');
//set backgorund color in orange
}
This implementation functions correctly in my test sheet available at https://docs.google.com/spreadsheets/d/18WVMkkoQViEXXXisXfwTA1ZLY52Zn2ysNa3J8O05MzY/edit?usp=sharing
However, I need it to extend its functionality to any spreadsheet named "Period (Period Number)_YY Theoretical" and to any tab within those spreadsheets named "MMMM-YY". My instinct tells me that using regular expressions to check if the names returned by getActiveSpreadsheet() and getActiveSheet() meet the criteria could populate the 'sheetsToWatch' array, but I am unsure of how to implement this. Any suggestions?