Ignoring empty cells in Google Sheets custom script

Question

I am trying to create a formula to calculate Net Promoter Score on Google Sheets. I have the formula working but only when I specify the exact range. My issue is that this specific sheet will grow with data over time and I do not want to have to keep reselecting the range. What I want to do is select the entire row and just let it auto-update the NPS score. My issue with this approach is every empty cell is considered a zero which is screwing up my percentages. How can I make my function ignore the empty cells???

Here is my attempt:

/**
    This is a custom formula that calculates the Net Promoter Score.
    @customFunction
    */
function NPS(numArr) {

  var detractors = new Array();
  var passive = new Array();
  var promoters = new Array();

  var i = 0;

  for (i = 0; i < numArr.length; i++) {
    if (isNaN(numArr[i])) {
      console.log(numArr[i]);
    } else {
      if (numArr[i] >= 9) {
        promoters.push(numArr[i]);
      } else if (numArr[i] === 7 || numArr[i] === 8) {
        passive.push(numArr[i]);
      } else if (numArr[i] <= 6) {
        detractors.push(numArr[i]);
      }
    }
  }


  var promoPercentage = promoters.length / numArr.length;
  var detractorsPercentage = detractors.length / numArr.length;

  return (promoPercentage - detractorsPercentage) * 100;
}

@AndrewLohr it comes from the range I highlight in google sheets. For example, A2:A25 — klaurtar1
sorry I'm not familiar with google sheets integration- do you control the numArr parameter at all? From what you said, it sounds like google sheet just passes it in to you. Is the array structured the same whether you highlight a range or a whole row? — Andrew Lohr
Yeah you control the array. When you call the function, you use your cursor to select the range — klaurtar1
can you edit into your question how you call the NPS function? — Andrew Lohr

Andres Duarte Andres Duarte · Accepted Answer · 2020-01-28T11:38:33

You can use JavaScript filter [1] function to filter the empty values from the array you're getting (numArr). Also, notice that you're selecting a range of cells so the argument will be a 2D array [2], where each value is a "row" array filled with the column values for that row, in case you just want the first value of each row (for a one column range like A1:A25) you need to access the first element of each "row" array to get the actual value:

function NPS(numArr) {

  var detractors = new Array();
  var passive = new Array();
  var promoters = new Array();

  var i = 0;

  //Filter empty elements
  numArr = numArr.filter(function(element) {
    return element[0] !== '';
  })

  for (i = 0; i < numArr.length; i++) {
    if (isNaN(numArr[i][0])) {
      console.log(numArr[i][0]);
    } else {
      if (numArr[i][0] >= 9) {
        promoters.push(numArr[i][0]);
      } else if (numArr[i][0] === 7 || numArr[i][0] === 8) {
        passive.push(numArr[i][0]);
      } else if (numArr[i][0] <= 6) {
        detractors.push(numArr[i][0]);
      }
    }
  }

  var promoPercentage = promoters.length / numArr.length;
  var detractorsPercentage = detractors.length / numArr.length;

  return (promoPercentage - detractorsPercentage) * 100;
}

[1] https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter

[2] https://developers.google.com/apps-script/guides/sheets/functions#arguments

Ignoring empty cells in Google Sheets custom script

1 Answers