Airtable script to automate cleaning and categorizing of incoming transactions

airtable-zapier-finance-automation.js

let config = input.config();
let importId = config.id;

let transactions = base.getTable('Transactions');
let transactionsFiltered = await transactions.getView('Auto Import').selectRecordsAsync();
let thisTransaction = transactionsFiltered.getRecord(importId);


// Match Merchant
let importMerchantRaw = config.merchant_raw.toLowerCase();
let merchants = base.getTable('Merchants');
let merchantsFiltered = await merchants.getView('Has Keywords').selectRecordsAsync();

for (let merchantRecord of merchantsFiltered.records) {
    let field = merchantRecord.getCellValue('Keywords').toLowerCase();

    if (importMerchantRaw.includes(field)) {
        // console.log(merchantRecord.id)
        
        let update = await transactions.updateRecordAsync(thisTransaction, {
            'Merchant': [{ id: merchantRecord.id }],
            'Tags': [ ...thisTransaction.getCellValue('Tags'), { name: 'Merchant Matched' }]
        });
    }
}


// Convert Amount to negative value if coming from Square Cash
let importAmount = config.amount;
let importTagNames = config.tag_names;
let invertedSuccessTag = 'Amount Inverted';

if (!importTagNames.includes(invertedSuccessTag) && importTagNames.includes('Zap (Square)')) {
    let update = await transactions.updateRecordAsync(thisTransaction, {
        'Amount': -importAmount,
        'Tags': [ ...thisTransaction.getCellValue('Tags'), { name: invertedSuccessTag }]
    });
}


// Match Account (not needed if account name coming from Zapier is an exact match to the linked record's name)
// let importAccountRaw = config.account_raw.toLowerCase();
// let accounts = base.getTable('Accounts');
// let accountsFiltered = await accounts.getView('Has Keywords').selectRecordsAsync();

// for (let accountRecord of accountsFiltered.records) {
//     let field = accountRecord.getCellValue('Keywords').toLowerCase();

//     if (importAccountRaw.includes(field)) {        
//         let update = await transactions.updateRecordAsync(thisTransaction, {
//             'Account': [{ id: accountRecord.id }],
//             'Tags': [ ...thisTransaction.getCellValue('Tags'), { name: 'Account Matched' }]
//         });
//     }
// }


// Filter and array?
// let filteredRecords = merchantsFiltered.records.filter(match => {
//     return match.getCellValue('Keywords')
// })