Reputation: 97
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 )
Upvotes: 0
Views: 182
Reputation: 201673
I believe your goal is as follows.
Transactions
and the destination sheet sumTransacation
.Category
, Month
, and Year
.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?
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);
}
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.
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);
}
Upvotes: 2