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.

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.

• javascript
• google-sheets
• google-apps-script

• Can you provide a clear and concise expected result to help the community easily understand the problem statement. Please provide a minimal reproducible example. – Patsytalk Commented Nov 20, 2024 at 12:49
• Can you provide a clear and concise expected result Would you also provide sample data, and details of the "sheet" and "sma1" arguments. – Tedinoz Commented Nov 20, 2024 at 13:12
• 1 This looks wrong j = i perhaps you meant j == i – James Commented Nov 20, 2024 at 14:09
• "A simple moving average (SMA) is a technical indicator that calculates the average price of a security over a given period of time. Add the closing prices for all periods in the timeframe, then divide the total by the number of periods." – maxhugen Commented Nov 20, 2024 at 16:35
• Details of the "sheet" and "sma1" arguments are as specified in the OP, 'sheet' is a sheet object, passed to the function from the calling function, and "sma" is the length of rows to do the calc on - 12 in this example. – maxhugen Commented Nov 20, 2024 at 16:38

2 Answers 2

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

The accepted answer provided by Patrick is concise and professional, and should be used.

Just in case any other beginners are interested I'm adding my corrected formula. As a JS novice, I did want to figure out why my 'simplistic' function wasn't working as expected.

Using the Debugger, and the Logger, I found a couple of issues:

1. The values I was adding to the new array 'values' needed to be in square brackets.
2. The value of dPrices[j] was being treated as text, and concatenated to 'sum' instead of being added. Quite unexpected, but fixed it by applying the Number() function.

function calcSMA(sheet, sma1) {
  if (sheet === null) return false;
  var dPrices = sheet.getRange('B6:B8642').getValues();
  var len = dPrices.length;

  if (sma1 !== 'N') {  // 'N' = this calc is not being used
    var values = [];
    for (var i = 0; i < len; i++) {
      var sum = 0;
      if (i < (sma1 - 1)) {
        values[i] = [''];
      } else {
        var start = i - (sma1 - 1);
        var end = i + 1;
        for (var j = start; j < end; j++) {
          sum += Number(dPrices[j]);
        }
        values[i] = [round(sum/sma1, 2)];
      }
    }
    sheet.getRange('C6:C8642').setValues(values);
  }
}