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') {
        } else {
      } //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

  • Why don't you share a doc with us, so that we can have a look....
    – Jacob Jan
    Aug 14, 2013 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
    Aug 15, 2013 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, 2013 at 10:47
  • Here is a link to the spreadsheet with the function above docs.google.com/spreadsheet/…
    – Neil XoX
    Aug 15, 2013 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
    Aug 15, 2013 at 14:24

1 Answer 1


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';
  • 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, 2013 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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