Custom conditional in Google Sheets: How to integrate in Apps Script?

Question

My goal is to have Google Sheets detect when a cell's contents has a strikethrough added, and to change the font color of that cell in response. I learned that this is not possible without an Apps Script integration.

First, I needed a function that identifies whether a cell has strikethrough text. I found this code on Stack Exchange, and it works as a formula when used in a sheet:

function has_strikethrough(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var fParts=formula.split(')').shift()
  var rang=fParts.split('(').pop()
  
  try {
    var range = sheet.getRange(rang);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var lines = range.getFontLines();
 // return lines.length;
  var output = [];
  for (var i = 0; i < lines.length; i++) {
    output.push(lines[i].map(function(a) {return a == 'line-through';}));
  }
  return output;
}

It allows me to derive a boolean result if I use this formula:

=has_strikethrough(A1)=TRUE

This cannot be integrated into a conditional rule within Google Sheets, however, and for that, I learned that I need to create a conditional directly in Apps Script. I found some simple default code that is supposed to do this, but I don't seem to know how to integrate my strikethrough function into it properly. When I add the following code, and include the "whenFormulaSatisfied" boolean, the function breaks and the conditional does not work:

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("C3:D");
var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied("=has_strikethrough(C3)=TRUE")
    .setFontColor("#666")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

Running in the console gives this error:

ReferenceError: args is not defined

Is there a simple step I'm missing? Any help would be appreciated. Thanks!

Answer 1

I managed to get this to work when the text is change (onEdit) or when the cell is selected but if you have the text in the cell and you simply apply the strike through it wont see it until the cell is re-selected.

Maybe this will lead you down a path to success ?

onEdit trigger and isStrikedThrough for reference

const ss = SpreadsheetApp.getActiveSpreadsheet();

/**
 * The event handler triggered when editing the spreadsheet.
 * @param {Event} e The onEdit event.
 * @see https://developers.google.com/apps-script/guides/triggers#onedite
 */
function onEdit(e){
  let isEditStrike = isStrikeThrough(e);
};

/**
 * The event handler triggered when the selection changes in the spreadsheet.
 * @param {Event} e The onSelectionChange event.
 * @see https://developers.google.com/apps-script/guides/triggers#onselectionchangee
 */
function onSelectionChange(e){
  let isSelStrike = isStrikeThrough(e);
};


function isStrikeThrough(e){
  if(e){
    const r = e.range;
    let styles = r.getTextStyles();
    let strike = styles[0][0].isStrikethrough();
    if(strike == true){
      r.setFontColor("red");
      return true
    };
    if(strike == false){
      r.setFontColor("black");
      return false
    };
  };
}