Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have the function below (found online and adapted and it works) that is an on-edit function, but I want it to run/trigger when an import is done OR on open, as the data will not really be edited it will just be frequently overwritten by an import. Is this possible? Basically, col A values need to be formatted red depending on the values in a number of other columns and the only problem is that I’m not editing them.

Here's the code:

  function foramtName() {
    var ss = SpreadsheetApp.getActiveSheet();
    var r = SpreadsheetApp.getActiveRange(); 

    if (r.getRow() != 1 && ss.getName() == "e404") {

        // 4, 5, 6, 7 are cols D, E, F, G
        vm = ss.getRange(r.getRow(), 4).getValue();
        gg = ss.getRange(r.getRow(), 5).getValue();
        inter = ss.getRange(r.getRow(), 6).getValue();
        other = ss.getRange(r.getRow(), 7).getValue();

        rowRange = ss.getRange(r.getRow(),1,1,1);
        // sum of these need to equal zero to colour red
        if (vm == '0' && gg == '0' && inter == '0' && other == '0') {
          rowRange.setBackgroundColor("red");
        } else {
          rowRange.setBackgroundColor("white");
        }
      } //end upper if
    }

Like I said, I’m new, so I’m not entirely sure if the first 2 lines are allowed even though it runs, and I can’t help thinking there is a much simpler way to sum the 4 cols—D, E, F, G—to colour col A red.

Example file: Row Color

share|improve this question
    
Why don't you share a doc with us, so that we can have a look.... –  Jacob Jan Tuinstra Aug 14 '13 at 22:13
    
You say sum, does this mean that this is possible as well: D=1, E=-1, F=0, G=0? –  Jacob Jan Tuinstra Aug 15 '13 at 9:58
1  
No there will never be negative numbers. Zero is the lowest and if all 4 cols are zero name will turn red. I will. Share a link to the sheet when i get home. –  Neil XoX Aug 15 '13 at 10:47
    
Here is a link to the spreadsheet with the function above docs.google.com/spreadsheet/… –  Neil XoX Aug 15 '13 at 14:06
    
I've added the script to your file, plus added the menu option. See how it works on opening and when selecting the menu option !! –  Jacob Jan Tuinstra Aug 15 '13 at 14:24
show 1 more comment

1 Answer

up vote 1 down vote accepted

Rename your script to a normal function, something like setColor. Rewrite the script to contain all the proper references. After that, you can select the proper trigger in the script editor:

enter image description here

Select the onOpen trigger for the function.

The second this you can do, is to create a menu item in the active spreadsheet, like this:

var menu = [{name: "Set Color", functionName: "setColor"}];
ss.addMenu("Extra", menu);

If you add it to the existing script, it will be added on opening of the file. While your at it, use the following script:

function foramtName() {
  var ss = SpreadsheetApp.getActiveSheet();
  var mRows = ss.getMaxRows()-1, mCols = ss.getMaxColumns();
  var cRange = ss.getRange(2, 1, mRows, mCols);

  if (ss.getName() == "e404") {
    var aData = ss.getRange(2, 4, mRows, 4).getValues(); 
    var colors = new Array();    
    for(i in aData) {
      colors[i] = new Array();
      for(var j=0; j<mCols; j++) {
        if(aData[i].reduce(function(a, b) {return a + b}) === 0) {
          colors[i][j] = 'red';
        } else {
          colors[i][j] = 'white';
        }
      }       
    }
    cRange.setBackgrounds(colors);
  }
}
share|improve this answer
    
ok im really new to this. I think i understand the above and did try rewritting a couple of ways but was unsuccessful. Closest i got was the below. still looking at other scripts to try and integrate the code successfully: –  Neil XoX Aug 14 '13 at 22:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.