Google-sheets – Conditional Formatting Script to Change Font Size in Google Sheet

google sheets

In Google Sheets I'm trying to find a way to conditionally format the font size of the cell for Column A in a row, based on text found within the cell of Column B in the same row.

For example, if Column B in Row 2 contains "Most Wanted", then Column A in Row 2 should be font size 12, bold and underlined. (Normally it is plain text and size 10.)

It's pretty easy to change bold and underline formatting with the built-in conditional formatting but I can't find a way to change the font size as well.

Right now I'm using an adapted script I found and edited it to suit my needs. But it changes the font for the entire row. I'm not sure if there's something simple I need to edit or if I need an entirely new script. I'm not great with scripts. I was only able to edit the easy stuff. Below is the adapted script I'm using now and here is an editable test sheet that it's currently attached to:
https://docs.google.com/spreadsheets/d/1N4BT50HifwBS_qP4Tt6kxqwEOp2N-wVQxv7Jo2t0vQA/edit?usp=sharing

Any help would be greatly appreciated.

//Sets the row format depending on the value in the "Wanted Level" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var wantedlevelColumnOffset = getWantedlevelColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    wantedlevel = rowRange.offset(0, wantedlevelColumnOffset).getValue();
    if (wantedlevel == 'Most Wanted') {
      rowRange.setFontSize('12').setFontWeight('bold').setFontLine('underline').clear;
    } else if (wantedlevel == 'Want') {
      rowRange.setFontSize('10').setFontWeight('bold').setFontLine('none');
    } else if (wantedlevel != 'Most Wanted' || 'Want') {
      rowRange.setFontSize('10').setFontWeight('normal').setFontLine('none');
    }
  }
}

//Returns the offset value of the column titled "Wanted Level"
//(eg, if the 7th column is labeled "Wanted Level", this function returns 6)
function getWantedlevelColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Wanted Level") {
      return i;
    }
  }
}

Best Answer

Instead of range.offset(...) use something like SpreadsheetApp.getActiveSheet().getRange(i,1)