iT邦幫忙

1

🧾 用 Google Apps Script 撰寫家庭記帳自動化腳本的全記錄

  • 分享至 

  • xImage
  •  

https://ithelp.ithome.com.tw/upload/images/20250628/20155103hn1ijQTUh4.png

📊 從 Cashew 匯出資料到月度表格寫入,實作細節與踩坑紀錄

我最近開始幫家裡整理家庭收支帳,資料都記在手機上的 Cashew App,但很快就遇到一個限制:Cashew 雖然支援 Google Drive 備份,卻無法讓我直接讀取裡面的資料


☁️ Cashew 備份方式的限制

Cashew 的備份功能只會將 .sql 檔案儲存在 Google Drive 的「應用程式資料夾(appDataFolder)」中,那是一個使用者看不到的隱藏空間。

這讓我原本希望能做到「全自動化整理記帳資料 → 寫入 Google 試算表」的想法直接卡關。

目前唯一能取得帳目明細的方法就是手動匯出 CSV。這也是我後續處理流程的起點。


🧨 遇到的第一個問題: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


✅ 開發前與 GPT 的重點規格確認

為了避免後續開發誤差,過程中我明確確認了以下幾點:

問題 確認結果
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");

這樣就能無視年份、時區與格式偏差,達到準確比對。


🔍 debug 與日誌技巧

我加入了大量 Logger.log(...) 來幫助除錯,包括:

  • 顯示比對來源與目標日期:來源=6/26 vs 目標=6/26
  • 找不到分類或欄位的報錯訊息
  • 顯示每類別總和金額:如「餐飲:1,430」

這讓我能快速找出格式錯誤、比對失敗或欄位抓錯。


🧠 總結心得與反思

整體來說,這段自動化處理腳本幫我省下大量人工填寫與統計時間,也讓月度記帳更有結構感。

雖然踩了不少坑,但也因此更熟悉 Google Apps Script、日期格式處理、以及資料清洗邏輯。


💬 適合詢問 GPT 的 prompt 範例

我有一份 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();
}


🧾 目標試算表(Target)格式範例

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

📋 來源試算表(Source)CSV 格式範例

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 三餐

圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言