Apple Card easy import with Google Apps Script

Google Sheets Show & Tell
1

Overview

An automated way to import Apple Card montly csv files with as few clicks as possible.

This is a great spot for a screenshot too! :camera_flash:

image
image2306Ă—1630 438 KB

Installation

On the google sheet that you want to import and format the Apple Card data into (I use my tiller sheet), click Extensions > Apps Script

Delete the code that is there, then paste the code below.

Create a google drive folder to save your Apple Card monthly .csv files into. Navigate to that folder in your browser, when you are there, click on your url to see something like:
https://drive.google.com/drive/folders/1OeUbunchofrandomcharactersQb8RKY

copy the random characters after the /folder/… and paste it into the const FOLDER_ID value below. Make sure to keep the ’ ’ on the ends of the folder id.

function importAppleCardTransactions() {
  const FOLDER_ID = '1OeUbunchofrandomcharactersQb8RKY'; // Replace with your folder ID
  const folder = DriveApp.getFolderById(FOLDER_ID);
  const csvFiles = folder.getFilesByType(MimeType.CSV);

  // Create or get the "read" subfolder
  let readFolder;
  const subFolders = folder.getFoldersByName('read');
  if (subFolders.hasNext()) {
    readFolder = subFolders.next();
  } else {
    readFolder = folder.createFolder('read');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName('Apple Card Transactions');
  
  if (!sheet) {
    sheet = spreadsheet.insertSheet('Apple Card Transactions');
    const tillerHeader = [
      'Date', 'Description', 'Category', 'Amount', 'Account', 'Account #',
      'Institution', 'Month', 'Week', 'Transaction ID', 'Account ID',
      'Check Number', 'Full Description', 'Date Added', 'Categorized Date'
    ];
    sheet.appendRow(tillerHeader);
  }
  
  while (csvFiles.hasNext()) {
    const file = csvFiles.next();
    const csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
    const formattedData = formatToTiller(csvData);
    appendDataToSheet(sheet, formattedData);
    
    // Move the file to the "read" subfolder
    readFolder.addFile(file);
    folder.removeFile(file);
  }
  
  // Format the "Amount" column as currency
  const amountColumnIndex = 4; // "Amount" is the 4th column (1-based index)
  const numRows = sheet.getLastRow();
  if (numRows > 1) { // Ensure there's data to format
    sheet.getRange(2, amountColumnIndex, numRows - 1, 1).setNumberFormat('$#,##0.00');
  }
}

function formatToTiller(csvData) {
  const appleHeaders = [
    'Transaction Date', 'Clearing Date', 'Description', 'Merchant',
    'Category', 'Type', 'Amount (USD)', 'Purchased By'
  ];

  const formattedData = [];

  csvData.slice(1).forEach(row => {
    const formattedRow = [
      row[appleHeaders.indexOf('Transaction Date')], // Date
      row[appleHeaders.indexOf('Merchant')],         // Description
      '',                                            // Category
      -parseFloat(row[appleHeaders.indexOf('Amount (USD)')]), // Amount
      `Apple Card - ${row[appleHeaders.indexOf('Purchased By')]}`, // Account
      '',                                            // Account #
      'Apple Card',                                  // Institution
      '',                                            // Month
      '',                                            // Week
      '',                                            // Transaction ID
      '',                                            // Account ID
      '',                                            // Check Number
      row[appleHeaders.indexOf('Description')],      // Full Description
      '',                                            // Date Added
      ''                                             // Categorized Date
    ];
    formattedData.push(formattedRow);
  });

  return formattedData;
}

function appendDataToSheet(sheet, data) {
  sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
}

Setup

Hit the save button

image
image926Ă—296 32 KB

Usage

If you have Apple Card transaction csv files in this folder, press the run button.

image
image1258Ă—560 81.8 KB

Permissions

You most likely have to give google apps script permission to edit your sheet.

Notes

You should see a run log on the bottom of the screen as the code executes. It will read any csv files in the folder, reformat them into tiller format, then paste the value into a new tab in your sheet titles “Apple Card Transactions”. It will then place any scanned csv files into a “read” sub folder so you don’t have to worry about them again.

You can place as many Apple Card csv files into your scanning folder as you’d like. I just ran an entire years worth without issue. It just appends the data into that new tab.

You can then copy and Paste (values only) into your transactions tab.

It’s very possible to modify this script to just append the values to the Transactions tab, I just wasn’t that brave yet.

You can access the script anytime form the same way above. It’s possible to create a shortcut or daily automation to run the script, I just wanted to keep it simple though.

Hope this is easy and helps someone. After I found out about Apps Scripts I automate all my spreadsheets using AI to do the coding. It’s wonderful!

2

Nice work @sevenboarder Keith! I don’t have an Apple Card, but it looks like a clean solution. I would love to have something like this to retrieve the order description for Amazon Prime purchases. Also, what is the AI functionality you are using? I assume it is from Google.

1 Like