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-14 20:42:32Z

3

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
}

edited Jul 14, 2023 at 20:42

answered Jul 14, 2023 at 20:37

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pynoob Over a year ago

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!

Collectives™ on Stack Overflow

GoogleSheets loop with conditional statement function

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related