Google Sheets App Script Merge or Add New Row-CodePudding

I have the following code which gets data from two sheets but can't figure out how update or add a new row in sheet "sumTransaction" where Category, Month & Year are equal.

For example in this example Expense 1, January, 2019 exists in the sumTransaction sheet so it should update the amount value by -3. Where Source A, January, 2019 and Other 1, March, 2019 do not exist in sumTransaction so they should be added to a new row.

function tableToObject() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const transactionSheet = ss.getSheetByName('Transactions')
  const lastRow = transactionSheet.getLastRow()
  const lastColumn = transactionSheet.getLastColumn()
  const values = transactionSheet.getRange(1, 1, lastRow, lastColumn).getValues()
  const [headers, ...originalData] = values.map(([,b,,d,e,,,,,,,,,,,p,q,r,s]) => [b,d,e,p,q,r,s])
  const res = originalData.map(r => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}))
  console.log(res)
 // GroupBy and Sum
 const transactionGroup = [...res.reduce((r, o) => {
 const key = o.Category   '_'   o.Month   '_'   o.Year
 const item = r.get(key) || Object.assign({}, o, {
   Amount: 0,
 })

  item.Amount  = o.Amount
  item.Key = key
  return r.set(key, item)
}, new Map).values()]

 console.log(transactionGroup)

 const budgetValues = getBudget()
 console.log(budgetValues)

 // merge or add row

}

function getBudget(){
 const ss = SpreadsheetApp.getActiveSpreadsheet()
 const sumSheet = ss.getSheetByName('sumTransacation')
 const lastRow = sumSheet.getLastRow()
 const lastColumn = sumSheet.getLastColumn()
 const values = sumSheet.getRange(1, 1, lastRow, lastColumn).getValues()
 const [headers, ...originalData] = values.map(([a,b,c,d,e,f]) => [a,b,c,d,e,f])
 const res = originalData.map(r => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}))
 return res
 }

transactionGroup Data

[ { Date: Fri Jan 04 2019 00:00:00 GMT-0700 (Mountain Standard Time),
  Category: 'Source A',
  Amount: 85,
  Month: 'January',
  Year: 2019,
  Group: 'COGS',
  Debit: 'Credit',
  Key: 'Source A_January_2019' },
{ Date: Mon Feb 25 2019 00:00:00 GMT-0700 (Mountain Standard Time),
  Category: 'Expense 1',
  Amount: -3,
  Month: 'February',
  Year: 2019,
  Group: 'Expense',
  Debit: 'Debit',
  Key: 'Expense 1_February_2019' },
{ Date: Tue Mar 26 2019 00:00:00 GMT-0600 (Mountain Daylight Time),
  Category: 'Other 1',
  Amount: -4,
  Month: 'March',
  Year: 2019,
  Group: 'Other',
  Debit: 'Debit',
  Key: 'Other 1_March_2019' 
 } ]

budgetValues Data

 [ { Category: 'Expense 1',
   Month: 'January',
   Year: 2019,
   Group: 'COGS',
   Amount: 10,
   'Debit/Credit': '' },
{ Category: 'Expense 2',
   Month: 'January',
   Year: 2019,
   Group: 'COGS',
   Amount: 10,
   'Debit/Credit': '' } ]

Sample Image of sumTransactions Sheet ( i.e. before script )

Sample Image of sumTransactions Sheet ( i.e. after script )

CodePudding user response：

I believe your goal is as follows.

There are 2 sheets which are the source sheet Transactions and the destination sheet sumTransacation.
You want to check the duplicated values between the source and destination sheets. At that time, you want to check the columns "A" to "C" on the destination sheet. So from your sample values, you want to check the values of Category, Month, and Year.
From your explanation,
- When I saw your values of transactionGroup Data and budgetValues Data, Category: 'Expense 1' of transactionGroup Data is Month: 'February',. And Category: 'Expense 1' of budgetValues Data is Month: 'January',. When I saw the images of i.e. before script and i.e. after script, Category: 'Expense 1' of transactionGroup Data is removed. In this case, I thought that you might want to add the value of Category: 'Expense 1' of transactionGroup Data to the destination sheet.

When my understanding is correct, how about the following sample script?

Sample script:

I added the script's flow in the script as the comment.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()

  // 1. Retrieve values from destination sheet.
  const dst = ss.getSheetByName('sumTransacation');
  const [headers, ...dstVal] = dst.getDataRange().getValues();

  // 2. Retrieve values from source sheet.
  const src = ss.getSheetByName('Transactions');
  const [srcHead, ...srcVal] = src.getDataRange().getValues();
  const srcIdx = headers.reduce((ar, h) => {
    const temp = srcHead.indexOf(h);
    if (temp > -1) ar.push(temp);
    return ar
  }, []);
  const srcValues = srcVal.map(r => srcIdx.map(i => r[i]));

  // 3. Update values of destination sheet.
  const obj1 = srcValues.reduce((o, r) => Object.assign(o, {[`${r[0]   r[1]   r[2]}`]: r}), {});
  const values1 = dstVal.map(r => {
    const temp = obj1[r[0]   r[1]   r[2]];
    if (temp) {
      return r.slice(0, 4).concat([r[4]   temp[4], r[5]]);
    }
    return r;
  });

  // 4. Added new values of source sheet.
  const obj2 = dstVal.reduce((o, r) => Object.assign(o, {[`${r[0]   r[1]   r[2]}`]: r}), {});
  const values2 = srcValues.reduce((ar, r) => {
    if (!obj2[r[0]   r[1]   r[2]]) ar.push(r);
    return ar;
  }, []);
  const values = [headers, ...values1, ...values2];

  // 5. Update the destination sheet using new values.
  dst.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

References:

Edit:

When I saw your sample Spreadsheet, I noticed that your spreadsheet is different from your sample images. I think that this is the reason of your issue. So for your sample Spreadsheet, I added one more sample script as follows.

Sample script:

function sample2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()

  // 1. Retrieve values from destination sheet.
  const dst = ss.getSheetByName('sumTransacation');
  const [headers, ...dstVal] = dst.getDataRange().getValues();

  // 2. Retrieve values from source sheet.
  const src = ss.getSheetByName('Transactions');
  const [srcHead, ...srcVal] = src.getDataRange().getValues().map(([,b,,d,e,,,,,,,,,,,p,q,r,s]) => [b,d,e,p,q,r,s])
  const srcIdx = headers.reduce((ar, h) => {
    const temp = srcHead.indexOf(h);
    if (temp > -1) {
      ar.push(temp);
    } else {
      ar.push("");
    }
    return ar
  }, []);
  const srcValues = srcVal.map(r => srcIdx.map(i => r[i]));

  // 3. Update values of destination sheet.
  const obj1 = srcValues.reduce((o, r) => Object.assign(o, {[`${r[0]   r[1]   r[2]}`]: r}), {});
  const values1 = dstVal.map(r => {
    const temp = obj1[r[0]   r[1]   r[2]];
    if (temp) {
      return r.slice(0, 4).concat([r[4]   temp[4], r[5]]);
    }
    return r;
  });

  // 4. Added new values of source sheet.
  const obj2 = dstVal.reduce((o, r) => Object.assign(o, {[`${r[0]   r[1]   r[2]}`]: r}), {});
  const values2 = srcValues.reduce((ar, r) => {
    if (!obj2[r[0]   r[1]   r[2]]) ar.push(r);
    return ar;
  }, []);
  const values = [headers, ...values1, ...values2];

  dst.clearContents().getRange(1, 1, values.length, values[0].length).setValues(values);
}

In your sample Spreadsheet, the values of "Month" of "Transactions" is different from that of "sumTransacation" sheet. But, unfortunately, I cannot know your actual format. So, when you want to compare the values, how about changing the format as the same format? Please be careful this.