Sum cells if they are not strikethrough

Question

I'm a little bit stuck with my Google Apps script purposed to calculate a sum of cells which are not ~~strikethrough~~.

Here is its source:

function SumIfNotStrikethrough(rangeA1Notation)
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var mysheet = sheet.getActiveSheet();

  var dataRange = mysheet.getDataRange();
  var mydatarange = mysheet.getRange(rangeA1Notation);
  var numRows = mydatarange.getLastRow();
  var rowindex = mydatarange.getRowIndex();
  var columnindex = mydatarange.getColumnIndex();

  var total =0;

  for(i=rowindex;i<=numRows;i++)
  {
    if(dataRange.offset(i-1, columnindex-1, 1, 1).isBlank() != true && dataRange.offset(i-1, columnindex-1, 1, 1).getFontLine() != "line-through")
    {
      var temp = dataRange.offset(i-1, columnindex-1, 1, 1).getValue();
      total = total + temp;
    }    
  }

  return total;
}

Here is its formula: =SumIfNotStrikethrough("J2").

I have two questions here:

How to add the Google Apps script to use this formula as, for example, SumIfNotStrikethrough(J2) rather than SumIfNotStrikethrough("J2")? Quotes are so annoying in terms of changing its range manually after scaling the formula on other cells :-(. What should be changed in the source code?
When I run this script I face with following error:

Range not found (line 9, file "SumIfNotStrikethrough"

Thus, how can I fix it?

UPD № 1. Here is an example with a string reference, but it only counts the number of cells:

function countStrike(range) {
    var count = 0;
        SpreadsheetApp.getActiveSheet()
            .getRange(range)
            .getFontLines()
        .reduce(function (a, b) {
            return a.concat(b);
        })
        .forEach(function (el) {
            if (el === "line-through") {
                count++
            }
        });
    return count;
}

UPD № 2. Unfortunately, this question is different from my previous question "Sum cells if they are not bold". I respectively tried to change the script, but it didn't work. Yes, it sums cells but it sums all the cells with strike-through and without :-(.

Here is what I changed:

function SumIfNotStrikethrough(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split("!");
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join("!") + " is not a valid range");
  }
  var weights = range.getFontLine();
  var numbers = range.getValues();  
  var x = 0;
  for (var i = 0; i < numbers.length; i++) {
    for (var j = 0; j < numbers[0].length; j++) {
      if (weights[i][j] != "line-through" && typeof numbers[i][j] == "number") {
        x += numbers[i][j];
      }
    }
  }
  return x;
}

Thank you for all your attempts to help me in advance!

I put an example in the post update, but it only counts the number of cells, not a sum of it. — Ruslan Seletskiy, Jun 22 '16 at 20:32
Possible duplicate of [Sum cells if they are not bold](http://stackoverflow.com/questions/37964794/sum-cells-if-they-are-not-bold) — , Jun 23 '16 at 01:49
There is no conceptual difference between these two questions you posted. — , Jun 23 '16 at 01:49
Yes, it is! I tried to change the source of "Sum cells if they are not bold" but it didn't work :-(. Please see the update № 2. — Ruslan Seletskiy, Jun 23 '16 at 09:43
@soup, Maybe should I change something other in the source to make it working properly? — Ruslan Seletskiy, Jun 23 '16 at 11:06

score 0 · Accepted Answer · answered Jun 23 '16 at 15:05

I've modified the suggested answer with getFontLines() rather that getFontLine():

function SumIfNotStrikethrough1(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split("!");
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join("!") + " is not a valid range");
  }
  var lines = range.getFontLines();
  var numbers = range.getValues();  
  var x = 0;
  for (var i = 0; i < numbers.length; i++) {
    for (var j = 0; j < numbers[0].length; j++) {
      if (lines[i][j] != "line-through" && typeof numbers[i][j] == "number") {
        x += numbers[i][j];
      }
    }
  }
  return x;
}

Here is the formula to use it: =SumIfNotStrikethrough1(A2:B3).

Now that seems to be working properly in its single use.

Sum cells if they are not strikethrough

1 Answers1