Going from an array to object and creating subclasses-CodePudding

I am a beginner in google app script and would like to ask a question regarding how to create subclasses for an object based on an array of information drawn from my spreadsheet.

Here is an example sheet with some data in the sheet "History". The input data is the transactional history of the investment of a user. My end goal is to create an array inside google app script with adjusted stock-split values for any given stock.

However, the first step in my project would be to gather the data in such a manner that I can perform these calculations. For this, I would need to create an object such as this:

stock symbol: {date:value, {quantity: value, price:value}}, {date:value, {split ratio:value}}

The reason for this is because in this object the dates are linked to quantity price and split ratio. In later calculations I would look if the date of the split value is less or equal to the date of the quantity/price value, if this is true then perform split ratio * quantity and price/split ratio. If this is not true, then leave the price and quantity as is, for any given stock. Finally return these object in the same form as the orginal array.

This is the attempt I have made so far:

function createDate(date, quantity, price) {
  this.date = date;
  this.quantityPrice = new createDateData (quantity, price);
}

function createDateData(quantity, price) {
  this.quantity = quantity;
  this.price = price;
}

function retrieveData () {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheet = ss.getSheetByName('History');
  const data = inputSheet.getRange(2, 1, inputSheet.getLastRow() - 1, 9).getValues();
  const filterd = data.filter(row => row[2] == 'Buy' || row[2] == 'Sell' || row [2] == 'Split');
  const sorted = filterd.sort((a, b) => {
    if (a[0] < b[0]) return -1
    if (a[0] > b[0]) return 1
    else return 0
  })
 for ( let i of sorted) {
var sampleData= new createDate([i][0][0],[i][0][3],[i][0][4]);
console.log(sampleData);
 }
}

This is the output I get

    { date: Tue Jun 30 2020 18:00:00 GMT-0400 (Eastern Daylight Time),
  quantityPrice: { quantity: 1, price: 40000 } }

Which is different than from the desired output?

Question: How do I get the desired output? For example, in the case of AMZN:

AMZN: {9/28/2020, {1, 100}}, {9/28/2020, {0.5, 200}}, {10/19/2020 {0.2, 100}}, {11/27/2020, {10}}

EDIT2: Please see sheet "Desired Output" for desired output.

CodePudding user response：

As far as I can tell you have the array formula in column 'Price', so you need to update the column 'Quantity' only. The column 'Price' will updated automatically.

And are you sure that the line 27 in your desired output should change? I think it should not, since it's 'Cash', not 'AMZN'.

Try this code:

function main() {
  var ss    = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('History');
  var range = sheet.getRange(3,1,sheet.getLastRow(),9);
  var data  = range.getValues().reverse(); // reverse to iterate it from the top to the bottom

  for (var row in data) {
    var action = data[row][2];
    if (action != 'Split') continue;
    data = [...data.slice(0,row), ...update(data.slice(row))]; // update all rows below the 'Split' 
  }

  // get column 'Quantity' from the data and put it on the sheet
  var quantity = data.map(x => [x[3]]).reverse(); 
  sheet.getRange(3,4,data.length,1).setValues(quantity);
}


// the function takes rows, recalculates quantity and returns the updated rows

function update(data) {
  var security = data[0][1];
  var ratio    = data[0][8];

  // if the security is the same as in the first row,
  // and if the action is 'Buy' or 'Sell':
  // the price will be multiply by the ratio
  for (var row in data) {
    if (data[row][1] != security) continue;
    var action = data[row][2];
    if (['Buy','Sell'].includes(action)) data[row][3] *= ratio;
  }

  return data;
}

The script implies that all the rows are sorted by date. Newest dates at the bottom, oldest dates at the top.

There is no any objects. I see no need for them. Probably it could be done another way, with objects.