I updated this script to support Amazon, Apple Card, Mint, PayPal, Venmo, YNAB, etc., like Import CSV Line Items and the CSV Spreadsheet Importer Multitool do … What do you think?
I differentiated the script from the existing tools by not remapping/transforming files, but then I got inspired by @randy’s Simple CSV Import and CSV Spreadsheet Importer Multitool 
. Data transformation is a broad topic but spreadsheet formulas probably solve a lot of it. And if they don’t or you prefer your own external workflow, the script still enables that, as before.
The mappings/transformations (from Amazon, etc.) are read from spreadsheet formulas, like Simple CSV Import and the Multitool do.
The script detects the format in the file:
It shows a preview:
and this is the feedback you get if you manually select an incompatible format:
Install
The same as before, copy the Code.js and Index.html files into a new Apps Script project, just like Simple CSV Import. But to remap/transform Amazon, etc. third-party files it now depends on HyperFormula, so you need to copy hyperformula.full.min.js to the Apps Script project as well. Find that file on e.g. jsDelivr.
HyperFormula is optional — without it the script works the same as it did before.
Finally, to remap/transform Amazon, etc. files you need a sheet named Crosswalks, mapping those formats to your Transactions sheet. Here’s an example of that sheet and here are the same formulas in a Markdown table:
|
Date |
Description |
Merchant Name |
Category Hint |
Category |
Tags |
Amount |
Account |
Account # |
Institution |
Transaction ID |
Check Number |
Full Description |
Note |
Metadata |
| Amazon Line Items |
'=OrderDate |
'="[Amazon Item] " & Title |
|
'=Category |
|
|
'=-ItemTotal |
'=PaymentInstrumentType |
|
Amazon |
'=“amazon:” & OrderID |
|
'="Amazon Order ID " & OrderID & “: " & Title & " (” & ASINISBN & “)” |
'=CarrierNameTrackingNumber |
'=“{"amazon":{"account":"” & OrderingCustomerEmail & “","category-hint":"” & Category & “","id":"” & OrderID & “","isbn":"” & ASINISBN & “","item-price":"” & PurchasePrice & “","payment-type":"” & PaymentInstrumentType & “","quantity":"” & Quantity & “","seller":"” & Seller & “","shipDate":"” & ShipmentDate & “","total":"” & ItemTotal & “","tracking":"” & CarrierNameTrackingNumber & “","type":"purchase","unspsc":"” & UNSPSCCode & “"}}” |
| Amazon Returns |
'=RefundDate |
'=“[Amazon Refund Item]” & Title |
|
'=Category |
|
|
'=RefundAmount + RefundTaxAmount |
|
|
Amazon |
'=“amazon:” & OrderID |
|
'="Amazon Order ID " & OrderID & “: " & Title & " (” & ASINISBN & “)” |
|
'=“{"amazon-refund":{"category-hint":"” & Category & “","id":"” & OrderID & “","isbn":"” & ASINISBN & “","order date":"” & OrderDate & “","quantity":"” & Quantity & “","refund reason":"” & RefundReason & “","seller":"” & Seller & “","type":"refund"}}” |
| Amazon Business Line Items |
'=OrderDate |
'="[Amazon Item] " & Title |
|
'=ProductCategory |
|
|
'=-ItemNetTotal |
'=PaymentInstrumentType |
|
Amazon |
'=“amazon:” & OrderID |
|
'="Amazon Order ID " & OrderID & “: " & Title & " (” & ASIN & “)” |
'=CarrierName & “(” & CarrierTracking & “)” |
'=“{"amazon-business":{"account user email":"” & AccountUserEmail & “","account-group":"” & AccountGroup & “","account-user":"” & AccountUser & “","asin":"” & ASIN & “","brand":"” & Brand & “","category":"” & ProductCategory & “","discount":"” & PricingDiscountAppliedOff & “","discount-program":"” & DiscountProgram & “","id":"” & OrderID & “","item-price":"” & PurchasePPU & “","order-tax":"” & OrderTax & “","part-number":"” & PartNumber & “","po-number":"” & PONumber & “","quantity":"” & ItemQuantity & “","seller":"” & SellerName & “","shipment-date":"” & ShipmentDate & “","shipping-address":"” & ShippingAddress & “","tracking":"” & CarrierTracking & “","unspsc":"” & UNSPSC & “"}}” |
| Apple Card |
'=TransactionDate |
'=Description |
'=Merchant |
'=Category |
|
|
'=-AmountUSD |
Apple Card |
|
Apple Card |
|
|
|
|
'=“{"appleCard":{"category":"” & Category & “","clearing date":"” & TEXT(ClearingDate,“yyyy-MM-dd”) & “","type":"” & Type & “"}}” |
| Mint |
'=Date |
'=Description |
|
|
'=Category |
|
'=IF(TransactionType = “debit”,-Amount,Amount) |
'=AccountName |
|
|
|
|
'=OriginalDescription |
'=Notes |
|
| PayPal |
'=Date |
'="[PayPal] " & Type & " / " & Name & " / " & FromEmailAddress & " → " & ToEmailAddress & " / " & ItemTitle |
|
|
|
|
'=Net |
PayPal |
|
PayPal |
'=“paypal:” & TransactionID |
|
|
'=Note |
'=“{"paypal":{"balance":"” & Balance & “","currency":"” & Currency & “","fee":"” & Fee & “","from":"” & FromEmailAddress & “","id":"” & TransactionID & “","itemid":"” & ItemID & “","name":"” & Name & “","subject":"” & Subject & “","title":"” & ItemTitle & “","to":"” & ToEmailAddress & “","type":"” & Type & “"}}” |
| Venmo |
'=Datetime |
'="[Venmo] " & Type & " / " & From & " → " & To |
|
|
|
|
'=AmountTotal |
Venmo |
|
Venmo |
'=“venmo:” & ID |
|
|
'=Note |
'=“{"venmo":{"destination":"” & Destination & “","fee":"” & AmountFee & “","from":"” & From & “","id":"” & ID & “","note":"” & Note & “","source":"” & FundingSource & “","status":"” & Status & “","title":"” & To & “","type":"” & Type & “"}}” |
| YNAB |
'=Date |
'=Payee |
|
|
'=Category |
|
'=Inflow - Outflow |
'=Account |
|
|
|
|
|
'=Memo |
'=“{"ynab":{"category group":"” & CategoryGroup & “","cleared":"” & Cleared & “","flag":"” & Flag & “","memo":"” & Memo & “"}}” |
I copied the formulas from the CSV Spreadsheet Importer Multitool master, which replicates Import CSV Line Items.
Why?
- Like Import CSV Line Items the script batch-imports Amazon, etc. CSV files in one shot vs. the Multitool which prepares data for copying and pasting. But unlike this script the Multitool is interactive — you can preview the data and update the formulas “live”.
- Duplicate detection. Some Import CSV Line Items formats offer it and some don’t, whereas this script always does, either via a transaction ID column or a derived content ID. All formats, existing ones or ones you add yourself, have it.
- Customization. Add formats yourself or customize existing ones (like Simple CSV Import and the Multitool allow you to do). Customize (and share!) automatic workflows.
- The script doesn’t create the “offset transactions” that Import CSV Line Items does, however …
The Crosswalks sheet is mapped to your Transactions sheet by (case-sensitive) header names, and the Amazon, etc. files are mapped to the Crosswalks sheet via named expressions.
Expression names are case insensitive and limited to letters and numbers (approximately) so the script rewrites e.g. “Carrier Name & Tracking Number” to “CarrierNameTrackingNumber”. It ignores more specific naming rules for now …
Example
See the Crosswalks example for context but,
|
|
'=AmountTotal |
Map a CSV column (e.g. “Amount Total”) to your Transactions sheet. |
'=-ItemTotal |
Arithmetic, e.g. change the sign of the “Item Total” column. |
'=Inflow - Outflow |
Combine two or more columns. |
'=IF(TransactionType = "debit",-Amount,Amount) |
Functions, e.g. the sign of the “Amount” column depends on its “Transaction Type”. |
I’m grateful for any feedback!
