Google-sheets – Changing the fontColor of cells when editing

google sheetsgoogle-apps-script

I'm trying to write an Apps Script that change the font color of a cell when it value changes.

I found a way to to change de fontColor of a range, and add to trigger 'onEdit', but the all range is changing and not the single cells that I've been modify.

Here is my Script :

function changeFontColor() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetA1 = ss.getSheetByName('Voltaire');
  var lastRow = sheetA1.getLastRow();
  var lastColumn = sheetA1.getLastColumn();
  var dataRange = sheetA1.getRange(3, 3, lastRow -6, lastColumn);

  
  dataRange.setFontColor('Red');

}

Best Answer

Using the edit event object.

function changeFontColor(e) {

   e.range.setFontColor('Red');

}

On the first line e is used to "catch" the edit event object.
On the "last statement" e.range is used instead of dataRange.
All other statements aren't required.

Using getActiveRange (no edit event object)

function changeFontColor() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getActiveRange().setFontColor('Red');

}

On the "last statement" ss.getActiveRange() is used instead of dataRange.
Only the first and the last statements are required.

postEdit functions getting range

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Max & Color', 'getmaxColor').addToUi();
}

function getmaxColor() {
  var ss = SpreadsheetApp.getActive(), aRange = ss.getActiveRange(); 
  var data = aRange.getValues(), colors = aRange.getBackgrounds();
  var dest = aRange.offset(0, data[0].length, 1, 1);
  var index = getmaxIndices(data);
  var max = data[index.i][index.j], color = colors[index.i][index.j];
  dest.setValue(max).setBackground(color);  
}

function onEdit() {
  var ss = SpreadsheetApp.getActive(), col = ss.getActiveCell().getColumn();
  if(col < 7) {
    var aRange = ss.getRange("A1:F7"), data = aRange.getValues();
    var colors = aRange.getBackgrounds();
    var dest = aRange.offset(0, data[0].length, 1, 1)
    var index = getmaxIndices(data);
    var max = data[index.i][index.j], color = colors[index.i][index.j];
    dest.setValue(max).setBackground(color); 
  }
}

function getmaxIndices(data) {
  var max = 0, indexI = 0, indexJ = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    for(var j=0, jLen=data[0].length; j<jLen; j++) {
      if(data[i][j] > max) {
        max = data[i][j], indexI = i, indexJ = j;
      }
    }
  }
  return {"i":indexI, "j":indexJ};
}

Explained

The onOpen() adds a menu item. Select the range you want to evaluate and select the menu option Max & Color. The values and backgrounds are collected as is an offset range. Then the indices (2d-array) of the max value are obtained, through the getmaxIndices(). Afterwards the corresponding max value and color is set.

The onEdit() does almost the same, but it has a fixed range. It will only fire if a cell is edited and if the edited cell belongs to the A1:F7 range.

Note

The reason for getting the error message, can be read in this answer: custom function

Example

I've added the sample code into your example file.

Google-sheets – Add a value to the next line

You can use appendRow() function to append the new values to the first empty row

Your code will look like this:

function RecordToSheet() {
 // make a data array with your row data 
 data = ['=NOW()','=Index(ImportData("URL1"),1,1)','=Index(ImportData("URL2"),1,1)']
 // Append the data array
 SpreadsheetApp.getActiveSheet().appendRow(data)
}

Best Answer

Related Solutions

Google Sheets – Change Cell Color in a Range

Code

Explained

Note

Example

Google-sheets – Add a value to the next line

Related Topic