Make Cell Flash onOpen Instead of onEdit in Google Sheets

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I need a Google sheets script which will make the background color of a cell flash. Currently I have a script which works fine, but onEdit. It would be far better in this case if the script could work onOpen, as I need the users attention to be drawn to a specific cell when they first open the sheet.

The sheet name is LBACC17, and the cell I need to flash (Change background colors) is K3.

I have very little scripting knowledge.

Here's the script I have now which works onEdit, which needs the code changing so will work onOpen…

function onEdit(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getSheetByName("LBACC17");
  var activeCell = ss.getActiveCell().getA1Notation();

  if( activeCell == "K2" )
  {
    for(var i=0;i<50;i++)
    {
      if( i%2 == 0 )
        mysheet.getRange("K3").setBackground("RED");
      else
        mysheet.getRange("K3").setBackground("WHITE");

      SpreadsheetApp.flush();
      Utilities.sleep(500);
    }
  }
}

Best Answer

The necessary changes are: replace onEdit with onOpen, and eliminate the check if( activeCell == "K2" ) as the script is no longer about the active cell.

I also shortened the script by replacing the inner conditional statement with a ternary operator.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mysheet = ss.getSheetByName("LBACC17");
  var cell = mysheet.getRange("K3");
  for (var i = 0; i < 50; i++) {
    cell.setBackground(i % 2 ? "WHITE" : "RED");
    SpreadsheetApp.flush();
    Utilities.sleep(500);
  }
}

Related Solutions

Google Apps Script – Change onEdit Function to onOpen

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);
  }
}

Google-sheets – Change a working function from onOpen() to onEdit()

Yes, if you want a script to be triggered by updates to spreadsheet A, then it should be bound to that spreadsheet. It can still access the other spreadsheets by their URL or Id. For example, this function copies the cells F2:H6 of the present sheet to the cells A1:C5 of another one.

function copyStuff() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('MasterSheet1')
  var ss2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/...');
  var targetSheet = ss2.getSheetByName('Sheet1');
  targetSheet.getRange("A1:C5").setValues(sourceSheet.getRange("F2:H6").getValues());
}

Best Answer

Related Solutions

Google Apps Script – Change onEdit Function to onOpen

Google-sheets – Change a working function from onOpen() to onEdit()

Related Topic