Google Apps Script for parsing email receipts

1099_expense.js

const LABEL_NAME = "1099expense"
const HANDLED_LABEL_NAME = `${LABEL_NAME}_done`
const SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/EXAMPLE-q-EXAMPLE/edit#gid=9999"
const SHEET_NAME = "All Business Expenses"
const DRIVE_RECEIPTS_FOLDER = "1099 Receipts"

// This isn't very efficient and could definitely be refactored 
// It's overly explicit to allow for easier following/logging
function createLabelIfNotExists(labelName) {
  const label = GmailApp.getUserLabelByName(labelName)
  if (label === null) {
    console.log(`Creating new label: ${labelName}`)
    GmailApp.createLabel(labelName)
  }
}

function create1099ExpenseLabels() {
  createLabelIfNotExists(LABEL_NAME)
  createLabelIfNotExists(HANDLED_LABEL_NAME)
}

function extractExpenseFromSubject(subject) {
  // Subject should be the amount and then optionally a category after the "-"
  const subjectParts = subject.split("-")
  const expenseAmount = parseFloat(subjectParts[0].trim())
  const category = subjectParts.length > 1 ? subjectParts[1].trim() : "Other Expenses"
  // If there's a third part, it should be the Merchant so include that if available
  const merchant = subjectParts.length > 2 ? subjectParts[2].trim() : "Unknown"
  return {
    amount: expenseAmount,
    category,
    merchant
  }
}

function saveToGoogleSheet(date, amount, category, merchant, emailId, attachmentLinks) {
  console.log(`Saving ${JSON.stringify(date, amount, category, merchant, emailId, attachmentLinks)}`)
  const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL)
  const sheet = spreadsheet.getSheetByName(SHEET_NAME)
  sheet.appendRow([date, merchant, amount, category, "N/A", emailId, attachmentLinks])
}

function saveAttachmentToDrive(attachments, receiptsFolder, emailId, dateSent) {
  const uploadUrls = []
  // Use a subdirectory in the receipts folder with a unique folder per email (and include a date prefix for easier sorting)
  const subFolder = receiptsFolder.createFolder(`${dateSent.toISOString()}_${emailId}`)
  for (let i = 0; i < attachments.length; i += 1) {
    console.log(`Saving attachment ${i + 1}`)
    const file = subFolder.createFile(attachments[i]);
    uploadUrls.push(file.getUrl())
  }
  return uploadUrls
}

function handleExpenseEmails() {
  // Get all messages addressed to the +expense GMail variant
  console.log("Searching for labelled emails")
  const label = GmailApp.getUserLabelByName(LABEL_NAME)
  const handledLabel = GmailApp.getUserLabelByName(HANDLED_LABEL_NAME)

  // Store all "handled" threads for clean-up
  const completeThreads = []
  const expenseThreads = label.getThreads();

  // Prepare the upload folder
  const folders = DriveApp.getFoldersByName(DRIVE_RECEIPTS_FOLDER);
  const receiptFolder = folders.hasNext() ? folders.next() : DriveApp.createFolder(DRIVE_RECEIPTS_FOLDER);

  // Loop through all the expense threads
  for (let i = 0; i < expenseThreads.length; i += 1) {
    console.info(`Processing email ${i + 1} of ${expenseThreads.length}`)
    const expenseThread = expenseThreads[i]
    const messages = expenseThread.getMessages();
    // Do some basic error checking
    if (messages.length === 0) {
      console.error("0 messages for this thread; skipping")
      continue;
    }
    if (messages.length > 1) {
      console.warn("Script not designed to handle threaded messages: just handling the first message")
    }
    // Select just the first message in the thread
    const [firstMessage] = messages;
    const subject = firstMessage.getSubject()
    const date = firstMessage.getDate();
    const emailId = firstMessage.getId()
    const { amount, category, merchant } = extractExpenseFromSubject(subject)
    console.log(amount, category, merchant, date, emailId)
    // Upload all attachments to Google Drive
    const uploadUrls = saveAttachmentToDrive(firstMessage.getAttachments(), receiptFolder, emailId, date)
    // Store a reference to the uploaded URLs along with the expense
    saveToGoogleSheet(date, amount, category, merchant, emailId, uploadUrls.join('\n'))
    completeThreads.push(expenseThread)
  }
  // Clean-up labels
  label.removeFromThreads(completeThreads)
  handledLabel.addToThreads(completeThreads)
}

function main() {
  create1099ExpenseLabels()
  handleExpenseEmails()
}