GoogleSheets loop with conditional statement function

Question

I am attempting to do a very simple function but I am having issues getting it to work as intended.

My goal is to read the value out of column 2 and if it is "Yes", add it to a variable named total. I want that total to be returned to the location I specify the function in.

So far, I can only get a number 2 to return back to the sheet. What am I doing wrong?

Below is my code so far:

function confirmationTotal() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var i = 2
  var total = 0
  while (true) {
    var column1Box = ss.getRange("B" + i);
    var column2Box = ss.getRange("C" + i);
    var value = column2Box.getValue();
    if(value == "Yes") {
      total = total + column1Box.getValue()
      i = i + 1
    }
    if(value == "Pending") {
      i = i + 1
    }
    else
      break;
  }
  return total
}

Tom Sharpe · Accepted Answer · 2023-07-14T20:42:32.833

The Else clause is part of the second if statement, not the first one. So if your input is "Yes", it will increment total and i, but then immediately exit.

See this for discussion of 'elseif'

Try nesting the if..else clauses like this:

function confirmationTotal() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var i = 2
  var total = 0
  while (true) {
    var column1Box = ss.getRange("B" + i);
    var column2Box = ss.getRange("C" + i);
    var value = column2Box.getValue();

    if(value == "Yes") {
      total = total + column1Box.getValue()
      i = i + 1
    }
      else if(value == "Pending") {
        i = i + 1
      }
           else
             break;

  }
  return total
}

Hi Tom, thank you so much for the assist! That is real insightful. After adding two else if statements one for "Pending" and one for "No", everything is functional. Thanks again! — pynoob, Jul 15 '23 at 01:56

GoogleSheets loop with conditional statement function

1 Answers1