我最近開始幫家裡整理家庭收支帳,資料都記在手機上的 Cashew App,但很快就遇到一個限制:Cashew 雖然支援 Google Drive 備份,卻無法讓我直接讀取裡面的資料。
Cashew 的備份功能只會將 .sql
檔案儲存在 Google Drive 的「應用程式資料夾(appDataFolder)」中,那是一個使用者看不到的隱藏空間。
這讓我原本希望能做到「全自動化整理記帳資料 → 寫入 Google 試算表」的想法直接卡關。
目前唯一能取得帳目明細的方法就是手動匯出 CSV。這也是我後續處理流程的起點。
從 Cashew 匯出的 CSV 在 Windows 記事本打開後,發現中文變成亂碼。這是因為 Cashew 使用 UTF-8 編碼但沒有 BOM,而記事本不支援無 BOM 的 UTF-8。
工具 | 解法 |
---|---|
Excel | 用「資料 > 從文字/CSV」載入,選 UTF-8 |
Notepad++/VS Code | 編碼轉成「UTF-8 with BOM」,再另存 |
這段 Google Apps Script 的目標很明確:
✅ 從 Cashew 匯出的 CSV 資料
✅ 根據「月份」、「日期」、「分類」進行加總
✅ 將結果寫入另一份共用的家庭收支試算表
我希望做到的,是將 Cashew 原始資料中的每筆記帳資訊,根據:
加總後寫入「六月」試算表中的對應格子
舉例來說:
來源有 2025/06/25 的「三餐 -60」與「飲料 -40」
我希望在 B 試算表中「6/25 × 餐飲」那格寫入 100
為了避免後續開發誤差,過程中我明確確認了以下幾點:
問題 | 確認結果 |
---|---|
Q:來源試算表的日期欄格式為「2025/6/25 下午 4:36:00」,是否可正確抓取? | A:會使用 JavaScript Date + Utilities.formatDate(..., "Asia/Taipei", "M/d") ,僅取月日,沒問題 |
Q:目標試算表日期欄位為「2023/6/25」等格式,是否能忽略年份比對? | A:是,只會比對 M/d 格式,能正確對應 |
Q:目標表中的分類欄位不在 A 欄而是在 C3~C41,是否能正確對應? | A:已指定分類從 C3 開始取,row 基準正確 |
Q:若 categoryMap 中有多個來源分類對應到同一分類(如「飲料」、「三餐」→「餐飲」)會怎麼處理? | A:會自動合併加總寫入同一格,預期行為正確 |
Q:來源資料包含不同月份,是否會誤寫入? | A:只處理符合 targetMonth = "2025/06" 的資料,其餘略過 |
Q:來源金額為負值(支出),目標表是否寫入正值? | A:是,使用 Math.abs() 處理所有金額 |
Q:是否會重複寫入造成數據錯誤? | A:每月一次性清理累加,不會重複執行同一批資料即可 |
這份規格說明是我在實作前與 GPT 一步步確認的重點項目,幫助我避免許多開發誤解與資料遺漏。如果你也打算做記帳自動化,強烈建議先列出這樣的對照表確認邏輯流程,能讓你寫腳本寫得更放心 🧠🧩
項目 | 說明 |
---|---|
⏱ 日期處理 | 來源轉成「M/d」,目標只比對月日,忽略年份 |
🧾 分類對應 | 用 categoryMap 將細分類合併為大分類(如「三餐」、「飲料」) |
➕ 寫入邏輯 | 累加原本金額,不覆蓋 |
📤 月份過濾 | 僅處理「指定 targetMonth」的資料,例如 2025/06 |
🔁 資料流程 | 先統計 → 一次寫入(效能較佳) |
🧼 清洗處理 | 移除空列、跳過未對應分類、日期不在表格中的資料 |
來源的日期是「2025/6/25 下午 4:36」,而目標表的日期是「2023/6/25」。為了正確比對,我改用:
Utilities.formatDate(new Date(val), "Asia/Taipei", "M/d");
這樣就能無視年份、時區與格式偏差,達到準確比對。
我加入了大量 Logger.log(...)
來幫助除錯,包括:
這讓我能快速找出格式錯誤、比對失敗或欄位抓錯。
整體來說,這段自動化處理腳本幫我省下大量人工填寫與統計時間,也讓月度記帳更有結構感。
雖然踩了不少坑,但也因此更熟悉 Google Apps Script、日期格式處理、以及資料清洗邏輯。
我有一份 Google 試算表「原始資料」,想根據每筆的日期與分類,把金額加總後填入另一張「六月」試算表中對應的日期與分類交叉格,請用 Apps Script 幫我寫一段自動化程式碼。
function fillJuneSheetByCategoryAndDate() {
const sourceSpreadsheetId = 'SheetID';
const sourceSheetName = '原始資料';
// 目標試算表 ID
const targetSpreadsheetId = 'SheetID';
const targetSheetName = '六月';
const targetMonth = "2025/06"; // ✅ 只處理這個年月的資料
const categoryStartRow = 3;
const categoryEndRow = 41;
const categoryMap = {
"三餐": "餐飲",
"飲料": "餐飲",
"生活開支": "家居/家電用品",
"點心": "餐飲",
"醫療": "醫療",
"其它": "雜支",
"例行開銷": "管理費/房貸",
"出遊": "出遊",
"娛樂": "娛樂/治裝費",
// 其他對應分類請自行補上
};
const sourceSheet = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName(sourceSheetName);
const targetSheet = SpreadsheetApp.openById(targetSpreadsheetId).getSheetByName(targetSheetName);
const rawTargetDates = targetSheet.getRange(1, 2, 1, targetSheet.getLastColumn() - 1).getValues()[0];
const targetCategories = targetSheet
.getRange(categoryStartRow, 3, categoryEndRow - categoryStartRow + 1)
.getValues()
.map(row => row[0].toString().trim());
const sourceData = sourceSheet.getDataRange().getValues();
const headers = sourceData[0];
const rows = sourceData.slice(1);
const dateIndex = headers.indexOf("date");
const categoryIndex = headers.indexOf("category name");
const amountIndex = headers.indexOf("amount");
if (dateIndex === -1 || categoryIndex === -1 || amountIndex === -1) {
Logger.log("❌ 缺少必要欄位:date, category name, amount");
return;
}
const accumulator = {};
rows.forEach((row, i) => {
const isEmptyRow = row.every(cell => cell === "" || cell === null || typeof cell === 'undefined');
if (isEmptyRow) {
// Logger.log("⚠️ 第 %s 列為空白列,跳過", i + 2);
return;
}
const rawDate = row[dateIndex];
const rawCategory = row[categoryIndex];
const amount = parseFloat(row[amountIndex]);
if (!rawDate || !rawCategory || isNaN(amount)) {
Logger.log("⚠️ 第 %s 列資料不完整,跳過:%s", i + 2, JSON.stringify(row));
return;
}
const dateObj = new Date(rawDate);
const recordYearMonth = Utilities.formatDate(dateObj, "Asia/Taipei", "yyyy/MM");
if (recordYearMonth !== targetMonth) {
Logger.log("📤 跳過非 %s 的資料:%s", targetMonth, recordYearMonth);
return;
}
const mmdd = Utilities.formatDate(dateObj, "Asia/Taipei", "M/d");
const mappedCategory = categoryMap[rawCategory.toString().trim()];
if (!mappedCategory) {
Logger.log("⚠️ 分類未對應,跳過:%s", rawCategory);
return;
}
const key = `${mmdd}-${mappedCategory}`;
if (!accumulator[key]) accumulator[key] = 0;
accumulator[key] += Math.abs(amount); // ✅ 金額寫入時轉正數
});
Logger.log("📊 累積項目總數:%s", Object.keys(accumulator).length);
// ✅ 加總分類總和
const categoryTotals = {};
for (let key in accumulator) {
const [_dateStr, category] = key.split("-");
if (!categoryTotals[category]) categoryTotals[category] = 0;
categoryTotals[category] += accumulator[key];
}
Logger.log("📈 各分類總金額彙總:");
for (let cat in categoryTotals) {
Logger.log("%s:%s", cat, categoryTotals[cat]);
}
let successCount = 0;
for (let key in accumulator) {
const [dateStr, category] = key.split("-");
// 日期比對
let col = -1;
for (let i = 0; i < rawTargetDates.length; i++) {
const val = rawTargetDates[i];
let dateValue = "";
try {
const parsed = new Date(val);
if (!isNaN(parsed)) {
dateValue = Utilities.formatDate(parsed, "Asia/Taipei", "M/d");
}
} catch (e) { }
// Logger.log("🧪 日期比對:來源=%s vs 目標=%s", dateStr, dateValue);
if (dateValue === dateStr) {
col = i + 2;
break;
}
}
// 分類列比對
const row = targetCategories.findIndex(c => c === category) + categoryStartRow;
// Logger.log("🧪 分類比對:來源=%s vs 目標列=%s", category, row);
if (row >= categoryStartRow && col >= 2) {
const cell = targetSheet.getRange(row, col);
const existing = parseFloat(cell.getValue()) || 0;
const newValue = existing + accumulator[key];
cell.setValue(newValue);
// Logger.log("✅ 寫入成功:%s(row %s, col %s)= %s", key, row, col, newValue);
successCount++;
} else {
Logger.log("❌ 找不到對應欄位:%s(row %s, col %s)", key, row, col);
}
}
Logger.log("✅ 完成寫入,共寫入 %s 筆資料。", successCount);
SpreadsheetApp.flush();
}
B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|
1 | 6/1 | 6/2 | 6/3 | 6/4 | 6/5 | ||
... | ... | ... | ... | ... | ... | ||
14 | 管理費/房貸 | ||||||
15 | 電信費 | ||||||
16 | 樂樂學費 | ||||||
17 | 訂閱費用 | ||||||
18 | 分期付款 | ||||||
19 | 0 |
account | amount | currency | title | note | date | income | type | category name |
---|---|---|---|---|---|---|---|---|
支出 | -250 | TWD | 2025/6/26 23:15 | FALSE | null | 生活開支 | ||
支出 | -90 | TWD | 2025/6/26 23:15 | FALSE | null | 三餐 | ||
支出 | -95 | TWD | 2025/6/26 14:00 | FALSE | null | 三餐 | ||
支出 | -60 | TWD | 2025/6/25 16:36 | FALSE | null | 三餐 | ||
支出 | -90 | TWD | 2025/6/22 17:59 | FALSE | null | 點心 | ||
支出 | -55 | TWD | 2025/6/22 17:59 | FALSE | null | 三餐 | ||
支出 | -80 | TWD | 2025/6/22 17:59 | FALSE | null | 飲料 | ||
支出 | -260 | TWD | 2025/6/22 17:59 | FALSE | null | 醫療 | ||
支出 | 1000 | TWD | 2025/6/22 17:58 | TRUE | null | 其他 | ||
支出 | -60 | TWD | 2025/6/21 13:53 | FALSE | null | 飲料 | ||
支出 | -175 | TWD | 2025/6/21 13:52 | FALSE | null | 三餐 |