0

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;
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Psychomecano
  • 1
  • 1
  • What You mean with "can not create a custom function"? In google-sheets You will only get the seleted cells values, sadly. But You could change the argument to the function to a tring with sourrounding quotes I assume - or put the range as text into another filed - and use that to find the range. Or possibly use named ranges. – EOhm Dec 04 '19 at 22:13
  • Hello @Psychomecano, I published an answer. Let me know if that's what you were looking for. Cheers! – carlesgg97 Dec 09 '19 at 13:22
  • Does this answer your question? [Custom function throws a "You do not have the permission required to setValue" error](https://stackoverflow.com/questions/15933019/custom-function-throws-a-you-do-not-have-the-permission-required-to-setvalue-e) – Rubén Jun 21 '20 at 03:07

2 Answers2

0

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

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

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

example of the 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.
carlesgg97
  • 4,184
  • 1
  • 8
  • 24