How do I combine two rows with a the same identity?-CodePudding

I'm trying to combine 2 rows with the same identity. I've been looking for a solution, but somehow can't find a working solution. I'm trying to make a tracker for my stocks, but I want it to combine the information if I add the same asset. I made a form with which trades can be added to the portfolio. So if there is a duplicate asset, the new duplicate will always be on the last row.

I'm neither an expert in programming or google sheets, but here's the pseudo code:

check if there is a duplicate in column 2 (id of asset), if so:
copy the value in column 1 of the last row to existing row (it is a column for purchase date);
the id of the asset is in column 2, so that can stay the same;
the amount is in column 3, it should add the amount from the last row to the existing row, column 3.

Here is an example sheet: https://docs.google.com/spreadsheets/d/1AEdljHtXUOnRJ1kxbziqKAjYo5EqGZjjnWOx1mbeTI0/edit#gid=0

I tried several things but I got stuck. I have made a code to go through the data, find the duplicate and add it to a list. But after that I just don't know how to go about it.

You're probably going to laugh at my code, but from a certain point it was just like going round in circles.


function readData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Portfolio");
  var rangeArray = formSS.getRange("B2:B"   formSS.getLastRow()).getValues(); //makes an array of values from column B, but each item  is in array in itself.
  rangeArray = [].concat.apply([],rangeArray);//takes the elements outside their own array;
  var sortedRangeArray = rangeArray.sort();//sort the items to a new array
  duplicates=[];//make a list of duplicates to identify
  for (var i =0; i < 1;sortedRangeArray.length, i  )//iterate through sortedArray
    if(sortedRangeArray[i 1] === sortedRangeArray[i]){
      duplicates.push(sortedRangeArray[i]);//if a duplicate is found, push it to the duplicates list
    }
  var str = duplicates[0];//identify the duplicate, there is only one anyway.
  for (var k = 0; k < sortedRangeArray.length; k  ) {
    var row = sortedRangeArray[k];
    if(row[SEARCH_COL_IDX] == str) {
      var index = rangeArray.findIndex(str);//I thought it might help defining the position
}}}```

CodePudding user response：

Assumming these are your headers [ amount, id, newAmount]

  function run(){
    const ACTIVE = SpreadsheetApp.getActive()
    const allRows = ACTIVE.getDataRange().getValues()
    const lastRow = allRows.pop()

    // Id is in column B
    const recordId = lastRow[1]
    // Amount is in column A
    const amount = lastRow[0]
  
    // Remove row 1, I assume there are only headers
    allRows.shift()

    // Look through every row in Column B for a match
    allRows.forEach( (row, index) => {
      if( row[1] == recordId ){
        // Create a new amount
        const newAmount = amount   parseInt(row[0])
        ACTIVE.getRange(index 2, 3).setValue(newAmount)
      }
    })
  }

CodePudding user response：

It could be something like this:

function main() {
  var table = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

  var ids = table.map(x => x[1]);     // get array of IDs (column B)
  var row = ids.indexOf(ids.pop());   // find ID from last row in the array
  if (row == -1) return;              // if not found do nothing

  var value = table[row][2];          // get value of 3rd cell
  table[row] = table.pop();           // move the last row in current row
  table[row][2]  = value;             // add the value to 3rd cell
  table.push(Array(table[0].length)); // add empty row to the table

  SpreadsheetApp.getActiveSheet().getDataRange().setValues(table); // put the table back on the sheet
}