For Loop Google Apps Script? VBA for Example

Question

I have a VBA UDF in Excel that calculates the Top Level Quantity for a Parent-Child calculation. Asked similar question here Offset in JavaScript UDF? VBA for Example

The code is

Function ParentChildCalc(critCell As Range, valCell As Range) As Double
    Application.Volatile
    Dim numYes As Long
    Dim i As Long
    Dim temp As Double
    Do Until i = 5000
        If critCell.Offset(i, 0) = "Yes" Then numYes = numYes + 1
        If numYes = 2 Then Exit Do
        temp = temp + valCell.Offset(i, 0)
        i = i + 1
    Loop
    ParentChildCalc = temp
End Function

I am trying to translate this UDF into either JavaScript or TypeScript, but I am stuck with the offset and loop part of the formula.

Here is my best shot.

/**
 * ParentChildCalc1
 * @customfunction ParentChildCalc1
 * @param {string} critCell Critical Cell
 * @param {number} valCell Value Cell
 * @returns {number} ParentChildCalc1
 */
function ParentChildCalc1(critCell, valCell) {
    var blank = ""
    var TopYes = "Yes"
    var i = 1
    var numYes = 1
    if (critCell[i,0] == TopYes) {
        numYes = numYes++
            if numYes = 2 {
            }
        return (valCell);
    } else {
        return (blank);
    }
}

Answer 1

Running scripts in Google Sheets doesn't work exactly like Excel, which can be both good and bad. Coming from an Excel VBA background, there is a mindset shift you have to make.

1. When writing a user defined function (UDF) in Excel, you can pass Range parameters like this: =MyUDF(A1,C3). The function signature will give you two Range objects as parameters. In Google Sheets, this isn't the case. ALL range parameters are converted to values (or an array of values). This means that in a Sheets cell if you type =MyUDF(A1,C3 your script code will get the value of A1 and the value of C3. This is not what you want.

Based on this answer, your best option is to parse the original cell formula and extract the range parameters. Then you can create a Range object for your UDF. This is a roundabout set of logic, but it is consistent with how Sheets implements the underlying functions and scripts. The following function will pick up the formula from the active cell and parse it to give you the expected range object.

NOTE: you'll have to modify this function to pick out your UDF call if it's inside a compound formula. For example, it works if your formula is =MyUDF(A1,C3). But is DOES NOT work if your formula is =If(A2='Yes','',MyUDF(A1,C3)). You can modify the function to detect this, but it's not provided here.

function GetParamRange(paramNumber) {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();

  //--- expecting two parameters, split here and check
  const MAXARGS = 2;
  var args = formula.match(/=\w+\((.*)\)/i)[1].split(',');
  if (args.length != MAXARGS) {
    throw new Error('in GetParamRange: ' + args.join(',') + '- invalid number of arguments, expecting ' + MAXARGS);
  }

  if ((paramNumber < 1) || (paramNumber > MAXARGS)) {
    throw new Error('in GetParamRange: invalid parameter requested, must be ' + MAXARGS + ' or less');
  }

  //--- is the worksheet specified?
  var paramRange;
  var parts = args[paramNumber-1].split('!');
  if (parts.length == 2) {
    //--- use worksheet and cell to get the range
    Logger.log('activeSheet = ' + activeSheet.getName());
    ss = SpreadsheetApp.getActive().getSheetByName(parts[0].replace(/\'/g, ''));
    paramRange = ss.getRange(parts[1]);
  }
  else {
    //--- get the range from the active sheet
    paramRange = activeSheet.getRange(parts[0]);
  }
  return(paramRange);
}

2. Before I list the actual function, there is an important aspect to coding UDFs you must understand. In Excel, you can explicitly tag a UDF as Volatile. In Google Sheets this is not possible. Honestly, I haven't worked with Google Sheet enough to learn all the details so you'll have to search around.
3. The user defined function itself accepts two parameters, but those parameters are actually created by calling GetParamRange. From there, it's a simple loop to add your running sum (with an added safety limit).

It will throw an error if the safety limit is exceeded.

 /**
 * ParentChildCalc1
 * @customfunction ParentChildCalc1
 * @param {string} critCell Critical Cell 
 * @param {number} valCell Value Cell 
 * @returns {number} ParentChildCalc1
 */
function ParentChildCalc1(critCell, valCell) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var critCell = GetParamRange(1);
  var valCell = GetParamRange(2);

  const safetyNet = 5000;
  var runningSum = 0;
  var numYes = 0;
  for (var i = 0; !valCell.offset(i,0).isBlank(); i++) {
    if (critCell.offset(i,0).getValue() == 'Yes') {
      numYes++;
      if (numYes == 2) break;
      if (i == safetyNet) {
        throw new Error('exceeded safety net limit in ParentChildCalc1, searching ' + safetyNet + ' rows.');
      }
    }
    runningSum = runningSum + valCell.offset(i,0).getValue();
  }
  return (runningSum);
}

Here's the whole code in a single block:

 /**
 * ParentChildCalc1
 * @customfunction ParentChildCalc1
 * @param {string} critCell Critical Cell 
 * @param {number} valCell Value Cell 
 * @returns {number} ParentChildCalc1
 */
function ParentChildCalc1(critCell, valCell) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var critCell = GetParamRange(1);
  var valCell = GetParamRange(2);

  const safetyNet = 5000;
  var runningSum = 0;
  var numYes = 0;
  for (var i = 0; !valCell.offset(i,0).isBlank(); i++) {
    if (critCell.offset(i,0).getValue() == 'Yes') {
      numYes++;
      if (numYes == 2) break;
      if (i == safetyNet) {
        throw new Error('exceeded safety net limit in ParentChildCalc1, searching ' + safetyNet + ' rows.');
      }
    }
    runningSum = runningSum + valCell.offset(i,0).getValue();
  }
  return (runningSum);
}

function GetParamRange(paramNumber) {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();

  //--- expecting two parameters, split here and check
  const MAXARGS = 2;
  var args = formula.match(/=\w+\((.*)\)/i)[1].split(',');
  if (args.length != MAXARGS) {
    throw new Error('in GetParamRange: ' + args.join(',') + '- invalid number of arguments, expecting ' + MAXARGS);
  }

  if ((paramNumber < 1) || (paramNumber > MAXARGS)) {
    throw new Error('in GetParamRange: invalid parameter requested, must be ' + MAXARGS + ' or less');
  }

  //--- is the worksheet specified?
  var paramRange;
  var parts = args[paramNumber-1].split('!');
  if (parts.length == 2) {
    //--- use worksheet and cell to get the range
    Logger.log('activeSheet = ' + activeSheet.getName());
    ss = SpreadsheetApp.getActive().getSheetByName(parts[0].replace(/\'/g, ''));
    paramRange = ss.getRange(parts[1]);
  }
  else {
    //--- get the range from the active sheet
    paramRange = activeSheet.getRange(parts[0]);
  }
  return(paramRange);
}

So, to use this function just put the formula =ParentChildCalc1(D7,C7) in cell E7.