Question 379707 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Make a function run on all spreadsheets that meet certain criteria

I’m creating an onEdit(e) script to highlight cells in a Google spreadsheet (in a specific column) after a specified user has edited them. Thing is, I need this to apply to all spreadsheets with a specific naming convention, and all tabs within those spreadsheets that, again, have a specific naming convention. I don’t want to edit all of our speadsheets, nor do I want it to apply to all the tabs within those spreadsheets.

Here’s the code I have so far (partially copied 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 == '[email protected]' && 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 works as expected for my test sheet https://docs.google.com/spreadsheets/d/18WVMkkoQViEXXXisXfwTA1ZLY52Zn2ysNa3J8O05MzY/edit?usp=sharing

but I need it to apply to any spreadsheet with the name "Period (Period Number)_YY Theoretical" and to any tab in those spreadsheets named "MMMM-YY". My gut feeling is that I could use regular expressions to check if the getActiveSpreadsheet() name matches the conditions and same with getActiveSheet() names to populate the ‘sheetsToWatch’ array, but I’m not sure how to code that in. Any thoughts?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    My own solution to this meant taking a step back and really understanding the scope of what I needed to use this for. I ended up simplifying the onEdit(e) function I wrote + removing the (e) trigger so it's just onEdit(). I also created two separate functions, spreadsheetName() and sheetNames() which check if the active spreadsheet follows the naming convention and searches for all of the sheets that follow the other naming convention, respectively. If either of these functions returns null, nothing happens, but if the spreadsheet name lines up and there are sheet names that match, onEdit() executes.

    I removed the event trigger after I realized only two people will ever be editing these sheets (myself and someone else), and it doesn't matter who causes the trigger.

    And last thing, with a little digging into APIs, I learned that if you duplicate a spreadsheet, the apps scripts are duplicated along with everything else. Due to the limited scope of my needs + the company, this is more than enough to ensure that all of the spreadsheets I need will have onEdit() script attached.

    Here's my final code:

    function onEdit() {

var sheetsToWatch = sheetNames();

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = sheet.getActiveCell();
var sheetName = sheet.getName();
var matchFound = false;

if(cell.getColumn() == 7 && sheetsToWatch != null)
//if user is 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 no matching sheets found end

//if found execute color change
sheet.getRange(cell.getRow(),cell.getColumn()).setBackground('#ff9900');  
  //set backgorund color in orange
}

  2. 0

    onEdit(e) is a reserved function name for simple triggers. There is no way to make what you want with this trigger. One option is to use an installable trigger instead and a script to search for all the spreadsheets that meet the criteria and create a trigger for each.

    First, you should rename your onEdit(e) function declaration as a sanity measure. This will prevent the risk of having a function run twice, as a simple trigger and installable trigger.

    Second, you should decide how you will handle the Apps Script quotas. One relevant quota is the maximum execution time that might be exceeded if you have many spreadsheets. One way is to use a spreadsheet as your control panel. On this spreadsheet, create a list of the spreadsheets to which you want to add the trigger. Include a column to log the status of adding the trigger. You might create a script to create this list automatically, but again, if you have too many spreadsheets, this script might exceed the maximum execution time.

    Then, make a script that reads the list of spreadsheets, adds the installable trigger to each one that doesn’t have the trigger, (use the status column for this) and updates the status column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search