Count the number of cells that have the text strikethrough

Question

I would like to create a custom function to count the number of strikethrough text. Would it be possible to create a script to perform this operation?

For the moment, I managed to create a script that counts the cell number with the strikethrough text, but I have to modify my script each time for a different range, and I can not create a custom function that count the number of cells that have the text strikethrough.

Here is my script:

The result is in the L20 and the control in the script

// Count the number of cells that are barred
function fontLine() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var plage = "E4:E11"
  var range = sheet.getRange(plage);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if (cell.getFontLine() === 'line-through')
        x++;
    }
  }
  sheet.getRange("L20").setValue(x);
  return x;
}

Answer 1

It's not possible to use setValue in a custom function but, as you already figured out, it's possible to get the result you are looking for by using a "regular script" (running it from a menu, by clicking an image, from the script editor, etc.)

Reference

• https://developers.google.com/apps-script/guides/sheets/functions

Answer 2

Modifications of your code

1. It now uses getActiveSheet() to obtain the sheet from where the function is called, and getDataRange() to obtain the whole range in the sheet where there is data.
2. It uses getFontLines() on the whole range instead of iterating cell-per-cell and calling getFontLine() to every single one of them. This results in a way more efficient code (see: Best Practices - Minimize calls to other services).
3. Removed setValue() call. If you want the result of this custom function to be placed in the L20 cell, simply modify its contents for this formula: =fontLine(). The value returned by the custom function will be set as the cell's value.

// Count the number of cells in the sheet that are barred
function fontLine() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var fontLines = range.getFontLines();
  var x = 0;
  for (var i = 0; i < fontLines.length; i++) {
    for (var j = 0; j < fontLines[i].length; j++) {
      if (fontLines[i][j] === 'line-through')
        x++;
    }
  }
  return x;
}

Example execution

Disclaimers

• Custom functions are executed upon writing them into a cell. Afterwards, their result is cached so that less resources are used. That means that if you add or remove strike-through's from your sheet, you will have to remove and re-insert the custom function in your cell.