1

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
}}}```
Osman P.
  • 33
  • 6
  • Can you provide a example Sheet for us to see the actual information? And also can you explain what worked in your code as you hoped for? And where in the process did you encounter an error or give up? – Neven Subotic Sep 25 '21 at 15:58
  • Thanks. I added a link to my little project. Well I hoped to find the duplicates, which I did. But I just gave up because I didn't know what to do with it. I couldn't fine a working solution. I'm open to all ideas :) – Osman P. Sep 25 '21 at 18:41

2 Answers2

2

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
}

Update

Since your table contains formulas it needs a slightly another implementation:

function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var table = sheet.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 last_row = table.pop();

  var date  = last_row[0];
  var id    = last_row[1];
  var value = last_row[2] + table[row][2];

  sheet.getRange(row+1,1,1,3).setValues([[date,id,value]]);

  sheet.getRange(sheet.getLastRow(),1,1,8).clearContent();
}
Yuri Khristich
  • 13,448
  • 2
  • 8
  • 23
  • Thanks, that seems to work (also the comments are really appreciated). But when I put formulas in the following columns, like total value etc. it does not work. Why is that? – Osman P. Sep 26 '21 at 07:38
  • I just sent you a request to access to your sheet. I need to see what do you mean by 'following columns'. – Yuri Khristich Sep 26 '21 at 08:18
  • sorry, accidentally changed the file location, I updated the link in the question. In my original file I made a form in which I can put my trades and it gets automatically copied to the portfolio. Along with the data I put in (purchase date, current price, amount, sl, target) it also copies in formulas for the following columns. Like the current value and risk. – Osman P. Sep 26 '21 at 09:47
  • Still 'Access Denied' with the new link. I sent you another request. – Yuri Khristich Sep 26 '21 at 10:11
  • shit man, I am so sorry.. forgot to turn on the share options.. it should be working now.. again, I apologise. I use RC to copy the correct formulas btw. – Osman P. Sep 26 '21 at 10:26
  • That did it, thanks! also for your patience :-) – Osman P. Sep 26 '21 at 13:04
1

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)
      }
    })
  }
Neven Subotic
  • 1,399
  • 1
  • 6
  • 18