Sum a number of range values; process and add to array

I'm attempting to create a Simple Moving Average (SMA) for an existing list of downloaded ETH prices. The main function calls a separate function to do the SMA calculation.

It has 2 params:

sheet: A 'sheet' object
sma1: The length of rows to do the calc on - 12 in this example.

The function:

function calcParams(sheet, sma1) {
  if (sheet === null) return false;

  var dPrices = sheet.getRange('B6:B8642').getValues();
  var len = dPrices.length;

  var sum = 0;
  var values = [];
  for (let i = 0; i < len; i++) {
    if (i < (sma1 - 1) ) {
      values[i] = "";
    } else {
      for (var j = (i - (sma1 - 1)); j = i; j++) {
        sum += dPrices[j];
      }
      values[i] = round(sum/sma1, 2);
    }
  }
  var dSMA1 = sheet.getRange('C6:C8642').setValues(values);
}

While the 'For' goes through the first 11 iterations, there is not enough data to sum 12 values to get the average, so the values array saves a blank.

On the 12th iteration, the code is trying to get 11 previous values from dPrices plus the current one, to sum. This sum is divided by 12 for the SMA, and added to the values array.

From debugging, it appears that the var j is "stuck" at 11, whereas it should iterate from 0 to 11. As a JavaScript novice I can't seem to ID where my code is wrong, so any suggestions would be appreciated.

Use Array Methods

I modified your script by adding array methods (such as splice, map, and reduce) to simplify the script to get the Simple Moving Average based on the sma1 value.

• The splice method was used to extract a specific number of elements based on the value of sma1.
• The map method is just an ES5 version of the basic for loop.
• The reduce method was used to get the sum of the extracted number of values based on sma1.

Script

function calcParams(sheet, sma1) {
  // sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // sample sheet object for testing
  // sma1 = 3; // sample sma1 value for testing
  var lr = sheet.getLastRow(); //get last row of the data (not unless you have specific range)
  if (sheet === null) return false;
  var dPrices = sheet.getRange(6, 2, lr - 5, 1).getValues(); //get data
  var out = dPrices.map((x, i, arr) => {
    if (i >= sma1 - 1) { //checks if row is greater than sma1
      var dPricesSub = new Array(...arr);
      var numerator = dPricesSub.splice(i - sma1 + 1, sma1); //extracts the number of elements needed based on sma1
      var sum = numerator.flat().reduce((total, current) => total += current, 0); //gets the sum of the extracted elements
      return [sum / sma1]; //returns the average value
    }
    else
      return [""];
  });
  sheet.getRange(6, 3, out.length, 1).setValues(out); //adds the output to the spreadsheet
}

Sample Output:

The output below is based on sma1 value of 3:

The output below is based on sma1 value of 12:

References:

• splice
• map
• reduce