Validate an Address in Airtable, with SmartyStreets

README.md

Validate an Address in Airtable

Validate an address with SmartyStreets from a script in Airtable. You can even use your "free 250 lookups per month"!

SmartyStreet Configuration

Fill in SS_KEY and SS_LICENSE with your SmartyStreets info.

For the key, you'll need to use an Embedded Key. The SmartyStreets system will see you making this API call from a browser and expect you to conform to their client-side code authentication scheme. This means you also need to fill in the Hosts setting. I've set mine up for airtable.com and *.airtableblocks.com. Here's what my SmartyStreets configuration looks like at (https://www.smartystreets.com/account/keys):

Falling into their client-side code scheme means you'll also be rate-limited, so no batch/bulk processing. The SmartyStreets documentation says you can get around that by specifying an IP address, but I've had trouble determining the IP address of the Airtable host that's running the script.

Configuring your table

The script reads the input address from a field named Input address and writes the SmartyStreet results to these fields:

• Delivery Line 1
• Delivery Line 2
• Last Line
• Lat/Lon
• DPV Match Code
• DPV Footnotes
• DPV Vacant

Those fields correspond to the HTTP Response: Output Field Definitions listed in the SmartyStreets US Street API doc.

main.js

// Validate an Address in Airtable, with SmartyStreets

// GNU General Public License v3.0
// https://www.gnu.org/licenses/gpl-3.0.en.html

// By: Zach Young
// https://github.com/zacharysyoung

const CONTACT_NAME = 'ADMIN\'s NAME';
const CONTACT_EMAIL = 'ADMIN\'s EMAIL';

const SS_KEY = 'YOUR KEY';
const SS_LICENSE = 'YOUR LICENSE';

const TABLE = base.getTable('Table 1');

// Input address
const inputField = TABLE.getField('Input address');

// Validation info from SmartyStreets
const fldStatus         = TABLE.getField( 'Status' );

const fldDeliveryLine1  = TABLE.getField( 'Delivery Line 1' );
const fldDeliveryLine2  = TABLE.getField( 'Delivery Line 2' );
const fldLastLine       = TABLE.getField( 'Last Line'       );

const fldCoordinates    = TABLE.getField( 'Lat/Lon' );

const fldDpvMatchCode   = TABLE.getField( 'DPV Match Code' );
const fldDpvFootnotes   = TABLE.getField( 'DPV Footnotes'  );
const fldDpvVacant      = TABLE.getField( 'DPV Vacant'     );

const clearFields = {
    [fldStatus.id]         : null,

    [fldDeliveryLine1.id]  : null,
    [fldDeliveryLine2.id]  : null,
    [fldLastLine.id]       : null,

    [fldCoordinates.id]    : null,

    [fldDpvMatchCode.id]   : null,
    [fldDpvFootnotes.id]   : null,
    [fldDpvVacant.id]      : null,
};

//-------------------------------------------------------------------------

async function main() {
    // `record` is set with the record the "Validate" button was clicked on
    const record = await input.recordAsync('Pick a record to validate', TABLE);

    if (!record) {
        throw Error('A record was not selected!');
    }

    // Wipe out any previous validation, no artifacts
    // @ts-ignore: null-setting is allowed for `clearFields` but TS complains
    await TABLE.updateRecordAsync(record.id, clearFields);

    // Validate
    const inputAddress = record.getCellValueAsString(inputField).trim();

    let fields, match;
    match = await getMatchFor(inputAddress);
    match = processMatch(match);
    fields = setFields(match);

    // Finally, update
    await TABLE.updateRecordAsync(record.id, fields);

    // Print to script window
    output.markdown(match.statusStr);
}

await main();

//-------------------------------------------------------------------------

/**
* @param {string} inputAddress
*/
async function getMatchFor(inputAddress) {
    const endpoint = new URL('https://us-street.api.smartystreets.com/street-address');
    const params = {
        key: SS_KEY,
        match: 'invalid',
        candidates: '1',
        license: SS_LICENSE,
        street: inputAddress
    };
    endpoint.search = new URLSearchParams(params).toString();
    const request = new Request(endpoint.toString());
    let matchedAddress = await fetch(request)
        .then(response => {
            if (!response.ok) {
                throw Error(
                    `Screen-shot this red "ERROR" block and send to ${CONTACT_NAME}, ${CONTACT_EMAIL}\n\n` +
                    `Failed to complete validation request: ${response.status} - ${response.statusText}\n` +
                    `url: ${request.url}\n` +
                    `method: ${request.method}`
                );
            }
            return response.json();
        })
        .then(jsonData => {
            // Using Free-form address input, we get back one match or nothing
            if (jsonData.length === 0) {
                return null;
            }
            return jsonData[0];
        })
        .then(candidate => {
            let match = { };

            if (candidate === null) {
                match.status = false;
                return match;
            }

            try {
                match.status = true;

                match.deliveryLine1  = candidate.delivery_line_1;
                match.deliveryLine2  = candidate.delivery_line_2;
                match.lastLine       = candidate.last_line;

                var o;

                o = candidate.metadata;
                match.coordinates    = [ o.latitude, o.longitude ].join(',').trim();

                o = candidate.analysis;
                match.dpvFootnotes   = o.dpv_footnotes;
                match.dpvMatchCode   = o.dpv_match_code;
                match.dpvVacant      = o.dpv_vacant;

            } catch (error) {
                console.log(candidate);
                throw error;
            }

            return match;
        });

    return matchedAddress;
}

function processMatch(match) {
    if (!match.status) {
        match.statusStr = `❌ Error: could not match address`;
        return match;
    }

    if (match.dpvVacant === 'Y') {
        match.statusStr = `🟡 Warning: USPS has marked this address as **Vacant** and will not deliver to it`;
        return match;
    }

    if (match.dpvMatchCode === 'N' || match.dpvMatchCode === '') {
        match.statusStr = `❌ Error: could not match address`;
        return match;
    }

    if (match.dpvMatchCode === 'S') {
        match.statusStr = `🟡 Warning: bad apt/unit`;
        return match;
    }

    if (match.dpvMatchCode === 'D') {
        match.statusStr = `🟡 Warning: missing apt/unit`;
        return match;
    }

    match.statusStr = '✅ Valid';
    return match;
}

function setFields(match) {
    let fields = { [fldStatus.id]: match.statusStr };

    if (match.status === false) {
        return fields;
    }

    fields[fldDeliveryLine1.id]  = match.deliveryLine1;
    fields[fldDeliveryLine2.id]  = match.deliveryLine2;
    fields[fldLastLine.id]       = match.lastLine;

    fields[fldCoordinates.id]    = match.coordinates;

    fields[fldDpvFootnotes.id]   = match.dpvFootnotes;
    fields[fldDpvMatchCode.id]   = match.dpvMatchCode;
    fields[fldDpvVacant.id]      = match.dpvVacant;

    return fields;
}