我有以下代碼，它從兩張作業表中獲取資料，但無法弄清楚如何在類別、月份和年份相等的作業表“sumTransaction”中更新或添加新行。
例如，在此示例中，sumTransaction 表中存在 2019 年 1 月的費用 1，因此應將金額值更新為 -3。如果 sumTransaction 中不存在 Source A, January, 2019 和 Other 1, March, 2019，則應將它們添加到新行中。
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
}
交易組資料
[ { 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'
} ]
預算值資料
[ { 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': '' } ]
sumTransactions 表的示例影像（即腳本之前）
sumTransactions 表的示例影像（即腳本之后）
uj5u.com熱心網友回復：
我相信你的目標如下。
- 有 2 張作業表，分別是源作業表
Transactions和目標作業表sumTransacation。 - 您想要檢查源作業表和目標作業表之間的重復值。那時，您想檢查目標作業表上的“A”到“C”列。所以從你的樣本值，要檢查的值
Category，Month和Year。 - 從你的解釋來看
- 當我看到你的價值觀
transactionGroup Data和budgetValues Data，Category: 'Expense 1'的transactionGroup Data是Month: 'February',。和Category: 'Expense 1'的budgetValues Data是Month: 'January',。當我看到的影像i.e. before script和i.e. after script，Category: 'Expense 1'中transactionGroup Data被洗掉。在這種情況下，我認為你可能想的增值Category: 'Expense 1'的transactionGroup Data目的地表。
- 當我看到你的價值觀
當我的理解正確時，下面的示例腳本怎么樣？
示例腳本：
我在腳本中添加了腳本的流程作為注釋。
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);
}
參考：
- 降低（）
- 地圖（）
編輯：
當我看到您的示例電子表格時，我注意到您的電子表格與您的示例影像不同。我認為這就是你的問題的原因。因此，對于您的示例電子表格，我又添加了一個示例腳本，如下所示。
示例腳本：
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.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/338178.html