Properly parse PayPal activity reports when importing (or use API) about invoiceninja HOT 1 OPEN

Minimal steps needed to clean an activity report (surely needs more - this is from memory). Start with these tweaks from a Google Sheets import (apologies for the GIF; I'm low on time; I bet there's a GIF-to-video out there):

Payments + Invoicing

*When mentioning select all and deleting something, this excludes headers -- always exclude headers for deleting things.

1. Dupe the email field and call it Email Dupe (Name - Unique Id)
2. Dupe the phone number and call it Phone Dupe (Customer - Phone), where the other is Contact - Phone
3. Create 2 fields to the right of Full Name: First Name + Last Name 💡InvoiceNinja's "Name" is mysteriously a unique ID for a
   business, not to be confused with full name. Email is NOT a unique id (additionally mysterious).
4. Ask chatgpt to make you a formula to extract the first + last names. I used this:

// First name
=IF(ISNUMBER(FIND(" ", D2)), TRIM(LEFT(D2, FIND("#", SUBSTITUTE(D2, " ", "#", LEN(D2) - LEN(SUBSTITUTE(D2, " ", "")))) - 1)), D2)

// Last name
=IF(ISNUMBER(FIND(" ", D2)), TRIM(RIGHT(D2, LEN(D2) - FIND("#", SUBSTITUTE(D2, " ", "#", LEN(D2) - LEN(SUBSTITUTE(D2, " ", "")))))), "Unknown")

1. Convert amounts to currency type (default is string)
2. Dupe the amount paid and create a normalized paid field + refund amt field: If >0, it's normalized paid. If <0, it's refund. Cut and special paste as "values" so you can delete the un-normalized column to prevent confusion (since it has both payments and refunds).
3. Dupe the normalized amt paid field ( for product - product), other for payment - amount
4. Date is nice for a custom client 1 field for sort of a "1st pmt date" if tracking seniority
5. Filter names -> select none -> select (empty) -> Delete all columns showing -> clear filter
6. Filter emails -> select none -> select (empty) -> Delete all columns showing -> clear filter
7. Filter status -> select none -> select (empty) -> Delete all columns showing -> clear filter
8. Filter invoice number -> select none -> select (empty) -> Delete all columns showing -> clear filter
9. Select all -> data -> clear dupes -> checkbox row1=headers -> select invoice numbers -> clear dupes -> clean empty rows
10. Filter currency -> select none -> select (empty) -> Delete all columns showing -> clear filter
11. While currency still filtered, show any non-USD only. If !tracking, delete. If tracking, you'll need to make a formula in a new column to convert to USD (or use the currency exchange import mapping which I haven't used before; no idea if it works) -- this part gets complex. I personally just deleted non-USD to simplify it at the cost of history.
12. Filter type (payment method) -> select none -> select (empty) -> Delete all columns showing -> clear filter
13. While type is still filtered, only show anything irrelevant to customer payment gateways -> Delete these rows -> delete empty rows.
14. If you have any unicode notes that are patterned, CTRL+H to replace since importninja won't import unicode (it'll show as garbled text).
15. Find qty -> change it all to static 1 (since your line items will be lost -- paypal mixes them up, making them impossible to parse).
16. Create a new column called Product - Product and fill with "Unknown" since item name is required (the app won't tell you this).
17. Delete Reference Txn ID column - that's not to be confused with transaction id
18. PayPal fees and net can be deleted - these are NOT subcharges since paypal shows inclusive amounts.

...this has been hell 🔥but I did get it to work. I tried recording a macro more than once, but replaying always fails early.

Clients

1. Dupe the sheet above to a new sheet and call it clients.
2. CTRL+A -> data -> clean dupes
3. Checkbox 1st row are headers -> uncheck all -> check only email from field -> delete dupes
4. Clean empty rows

from invoiceninja.