I need to count the duplicates in an array based on the conditions

values[i][2]==values[i 1][2] && values[i][1]==values[i 1][1])

and add the duplicate value to the column Total Gifts

And sum the totals for ig_Flow and output the results to the column Total Flow

I have trid modifing

values=

[ [ 'Id',
    'From',
    'To',
    'ig_Transaction',
    'ig_Flow',
    'ig_Running Balance',
    'Description',
    'ig_Date',
    'Type',
    'Total Gifts',
    'Total Flow' ],
  [ 'conn-1',
    'Alan',
    'Alícia',
    'Giving',
    1,
    60,
    'Stuff',
    '20/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-2',
    'Alan',
    'Ashish',
    'Giving',
    2,
    62,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-3',
    'Alan',
    'Ashish',
    'Giving',
    3,
    59,
    'Stuff',
    '26/11/2022',
    'Person',
    '',
    '' ],
  [ 'conn-4',
    'Alan',
    'Deborah',
    'Giving',
    15,
    48,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-5',
    'Christine',
    'Deborah',
    'Giving',
    1,
    47,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-6',
    'Christine',
    'Deborah',
    'Giving',
    4,
    61,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-7',
    'Christine',
    'Deborah',
    'Giving',
    4,
    61,
    'Stuff',
    '26/10/2023',
    'Person',
    '',
    '' ],
  [ 'conn-7',
    'Christine',
    'Gitanjali',
    'Giving',
    1,
    54,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-8',
    'Christine',
    'Jacklina',
    'Giving',
    7,
    43,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-9',
    'Jane',
    'Ashish',
    'Giving',
    1,
    55,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-10',
    'Jane',
    'Ashish',
    'Giving',
    1,
    58,
    'Stuff',
    '24/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-11',
    'Jane',
    'Ashish',
    'Giving',
    5,
    22,
    'Stuff',
    '26/10/2022',
    'Person',
    '',
    '' ],
  [ 'conn-12',
    'Jane',
    'Ashish',
    'Giving',
    5,
    10,
    'Stuff',
    '19/10/2022',
    'Person',
    '',
    '' ] ]

Need

values=

[ [ 'Id',
    'From',
    'To',
    'ig_Transaction',
    'ig_Flow',
    'ig_Running Balance',
    'Description',
    'ig_Date',
    'Type',
    'Total Gifts',
    'Total Flow' ],
  [ 'conn-1',
    'Alan',
    'Alícia',
    'Giving',
    1,
    60,
    'Stuff',
    '20/10/2022',
    'Person',
    1,
    1 ],
  [ 'conn-2',
    'Alan',
    'Ashish',
    'Giving',
    2,
    62,
    'Stuff',
    '26/10/2022',
    'Person',
    2,
    5 ],
  [ 'conn-3',
    'Alan',
    'Ashish',
    'Giving',
    3,
    59,
    'Stuff',
    '26/11/2022',
    'Person',
    2,
    5 ],
  [ 'conn-4',
    'Alan',
    'Deborah',
    'Giving',
    15,
    48,
    'Stuff',
    '26/10/2022',
    'Person',
    1,
    15 ],
  [ 'conn-5',
    'Christine',
    'Deborah',
    'Giving',
    1,
    47,
    'Stuff',
    '26/10/2022',
    'Person',
    3,
    9 ],
  [ 'conn-6',
    'Christine',
    'Deborah',
    'Giving',
    4,
    61,
    'Stuff',
    '26/10/2022',
    'Person',
    3,
    9 ],
  [ 'conn-7',
    'Christine',
    'Deborah',
    'Giving',
    4,
    61,
    'Stuff',
    '26/10/2023',
    'Person',
    3,
    9 ],
  [ 'conn-7',
    'Christine',
    'Gitanjali',
    'Giving',
    1,
    54,
    'Stuff',
    '26/10/2022',
    'Person',
    1,
    1 ],
  [ 'conn-8',
    'Christine',
    'Jacklina',
    'Giving',
    7,
    43,
    'Stuff',
    '26/10/2022',
    'Person',
    1,
    7 ],
  [ 'conn-9',
    'Jane',
    'Ashish',
    'Giving',
    1,
    55,
    'Stuff',
    '26/10/2022',
    'Person',
    4,
    11 ],
  [ 'conn-10',
    'Jane',
    'Ashish',
    'Giving',
    1,
    58,
    'Stuff',
    '24/10/2022',
    'Person',
    4,
    11 ],
  [ 'conn-11',
    'Jane',
    'Ashish',
    'Giving',
    5,
    22,
    'Stuff',
    '26/10/2022',
    'Person',
    4,
    11 ],
  [ 'conn-12',
    'Jane',
    'Ashish',
    'Giving',
    5,
    10,
    'Stuff',
    '19/10/2022',
    'Person',
    4,
    11 ] ]

Thanks for assistance

CodePudding user response：

Count Duplicates and Total

function count_duplicate() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  const vs1 = sh1.getRange(2, 1, sh1.getLastRow() - 1, sh1.getLastColumn()).getValues();
  let sObj = { pA: [] };
  vs1.forEach((r, i) => {
    if (vs1[i   1] && r[1] == vs1[i   1][1] && r[2] == vs1[i   1][2]) {
      let p = r[1]   "/"   r[2];
      if (!sObj.hasOwnProperty(p)) {
        sObj[p] = { counts: 1, ttl: r[4] }
        sObj.pA.push(p);
      } else {
        sObj[p].counts  = 1;
        sObj[p].ttl  = r[4];
      }
    } else {
      let p = r[1]   "/"   r[2]
      if (!sObj.hasOwnProperty(p)) {
        sObj[p] = { counts: 0, ttl: r[4] };
        sObj.pA.push(p);
      } else {
        sObj[p].counts  = 1;
        sObj[p].ttl  = r[4]
      }
    }
  })
  Logger.log(JSON.stringify(sObj));
  let arr = [["From","To","Total","Count"]]
  sObj.pA.forEach(p => {
    let t = p.split('/')
    arr.push([t[0],t[1],sObj[p].ttl,sObj[p].counts])
  })
  if(arr.length > 1) {
    osh.getRange(1,1,arr.length,arr[0].length).setValues(arr)
  }
}

Execution log
3:28:06 PM  Notice  Execution started
3:28:06 PM  Info    {"pA":["Alan/Alícia","Alan/Ashish","Alan/Deborah","Christine/Deborah","Christine/Gitanjali","Christine/Jacklina","Jane/Ashish"],"Alan/Alícia":{"counts":0,"ttl":1},"Alan/Ashish":{"counts":2,"ttl":5},"Alan/Deborah":{"counts":0,"ttl":15},"Christine/Deborah":{"counts":3,"ttl":9},"Christine/Gitanjali":{"counts":0,"ttl":1},"Christine/Jacklina":{"counts":0,"ttl":7},"Jane/Ashish":{"counts":4,"ttl":12}}
3:28:09 PM  Notice  Execution completed

Data:

Output: