现代化账房01：日常消费记账自动化

function extractEmailsToSheet() {
  var ss = SpreadsheetApp.openById("更新你的GooglesheetID");
  var sheet = ss.getSheetByName("Transactions");
  var categorySheet = ss.getSheetByName("StoreCategory");  // Store-to-category mapping
  var categoryMap = getCategoryMapping(categorySheet); 

  var threads = GmailApp.search('subject:"Purchase amount alert" newer_than:1d'); // Modify search query
  var messages = threads.map(thread => thread.getMessages()).flat();
  
  messages.forEach(message => {
    var body = message.getPlainBody();
    // Extract Date, Amount, Store (Modify regex based on email format)
    var amountMatch = body.match(/of \$(\d+\.\d{2}) was/);  // Example: 12/31/2024
    var dateMatch = body.match(/was made on (\w+ \d{1,2}, \d{4})/);         // Example: $12.34
    var storeMatch = body.match(/at (.*?) in/);               // Example: "at Walmart."

    var date = dateMatch ? dateMatch[1] : "N/A";
    var amount = amountMatch ? amountMatch[1] : "N/A";
    var store = storeMatch ? storeMatch[1] : "Unknown";
    var formattedStore = toTitleCase(store);

    var category = categoryMap[formattedStore] || "";

    Logger.log(date)
    Logger.log(amount)
    Logger.log(formattedStore)
    Logger.log(category)
    
    // Append to Google Sheet
    sheet.appendRow([date, amount, formattedStore, category]);
  });
}

function toTitleCase(text) {
  return text.toLowerCase().replace(/\b\w/g, function(char) {
    return char.toUpperCase();
  });
}

function getCategoryMapping(sheet) {
  var data = sheet.getDataRange().getValues();
  var map = {};
  for (var i = 1; i < data.length; i++) { // Skip header row
    var store = toTitleCase(data[i][0]); // Normalize store name
    var category = data[i][1];

    if (store) {
      map[store] = category;
    }
  }
return map;
}

function updateStoreCategoryMapping() {
  var ss = SpreadsheetApp.openById("更新你的GooglesheetID");
  var transactionsSheet = ss.getSheetByName("Transactions");
  var mappingSheet = ss.getSheetByName("StoreCategory");

  if (!transactionsSheet || !mappingSheet) {
    Logger.log("Sheets not found.");
    return;
  }

  // Get existing store-category mappings
  var existingMappings = mappingSheet.getDataRange().getValues(); // Read all existing mappings
  var mappingDict = {}; // Store mappings as { store: category }

  Logger.log("Existing Store-Category Mappings:");
  existingMappings.forEach(row => {
    if (row[0]) {
      mappingDict[row[0].toLowerCase()] = row[1] || "";
      Logger.log("Store: " + row[0] + " | Category: " + (row[1] || "No Category"));
    }
  });

  // Get transactions data
  var transactionsData = transactionsSheet.getDataRange().getValues();
  var newMappings = [];
  var storesNotInMapping = [];

  // Iterate over transactions (Assuming store names are in column C, category in D)
  for (var i = 1; i < transactionsData.length; i++) {  // Skip header row
    var store = transactionsData[i][2];  // Column C: Store Name
    var category = transactionsData[i][3];  // Column D: Category

    if (store) {
      var lowerStore = store.toLowerCase();
      if (!(lowerStore in mappingDict)) {  // If store is not already mapped
        storesNotInMapping.push(store);
        if (category) { 
          newMappings.push([store, category]);
          mappingDict[lowerStore] = category; // Add to dictionary to avoid duplicate insertions
        }
      }
    }
  }

  // Log stores that are not in the mapping table
  if (storesNotInMapping.length > 0) {
    Logger.log("Stores not in mapping table:");
    storesNotInMapping.forEach(store => Logger.log(store));
  } else {
    Logger.log("All stores are already in the mapping table.");
  }

  // Append new store-category pairs to the StoreCategory sheet
  if (newMappings.length > 0) {
    mappingSheet.getRange(mappingSheet.getLastRow() + 1, 1, newMappings.length, 2).setValues(newMappings);
    Logger.log("New mappings added: " + JSON.stringify(newMappings));
  } else {
    Logger.log("No new mappings to add.");
  }
}