Question 416044 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – 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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);
  }
}

  2. 0

    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:

    output1

    The output below is based on sma1 value of 12:

    output2

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search