Google Sheets Script while loop assistance

Question

I'm trying to write a script in Google Sheets JavaScript that has a database of invoices. I'm trying to create a payment option where someone can select a vendor and make a bulk payment which then assigns the right amount to each invoice.

I've used a while loop and even though the payments work perfectly, the loop doesn't end and I don't know why.

function makePayment() {

  var dbsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('InvoiceDB');
  var dasheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  var casheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calc');
  var amount = dasheet.getRange('I9').getValue();

  while (amount > 0) {
    var range = dbsheet.getRange("B1:B");
    var values = range.getValues();
    var invoiceNo = casheet.getRange('C1').getValue();

    var i = [];
    for (var y = 0; y < values.length; y++) {
      if (values[y] == invoiceNo) {
        i.push(y);
      }
    }

    var Row = Number(i) + Number(range.getRow());

    var outstanding = dbsheet.getRange(Row, 9).getValue();
    var alreadyPaid = dbsheet.getRange(Row, 8).getValue();

    if (amount > outstanding) {
      dbsheet.getRange(Row, 8).setValue(alreadyPaid + outstanding);
      amount = amount - outstanding;
    } else {
      dbsheet.getRange(Row, 8).setValue(amount);
    }

    dasheet.getRange('C9').clearContent();
    dasheet.getRange('I9').clearContent();
  }
}

obermillerk obermillerk · Accepted Answer · 2018-06-26T17:04:45

If amount is not greater than outstanding, amount is not modified. If amount is greater than outstanding, amount is decremented by outstanding. Since outstanding is always less than amount at this point, amount will never reach zero as a result of this and the loop will run infinitely because you never modify amount anywhere else. You either need to modify your condition, or the more likely solution is to add some modification of amount in your else clause so that amount can reach zero to end the loop.

Google Sheets Script while loop assistance

1 Answers