Airtable script for geocoding addresses using the swisstopo REST API

geoadmincoding.js

/* Geocoding utility script by @loleg
 * Latest version: https://gist.github.com/loleg/2e38752732e29472993dd74da988d7e6
 * 
 * - fills location coordinates based on an "Address" field
 * - uses the open data API service of swisstopo (Switzerland only)
 * - currently auto-throttled by Airtable, with fetch limits
 * - retries continuously to let you fix addresses in your table
 * - add this to a "create record" automation and also limit by time
 *
 * Required fields:
 * 
 * "address" - street address and post code e.g. "Richlibachstrasse 20, 3095"
 * "lat" / "lon" - will be updated with the coordinates (# 0.0000000)
 * "weight" - will be updated with result quality (rating as integer # 1-10)
 *
 * It may help you to use a formula like this to create the address string:
 * IF({Street}, TRIM(REGEX_REPLACE({Street}, "(" & {Postal Code} & ")|(" & {City} & ")|,","")) & ", " & {Postal Code}, '')
 */

const addressField = 'address';
let table = base.getTable("Locations");
let query = await table.selectRecordsAsync({ fields: [addressField, 'lat', 'lon', 'weight'] });

// Skip resolved values and blank addresses
let recordsToUpdate = query.records.filter(
    (row) => (
        row.getCellValueAsString('weight') == '' &&
        row.getCellValue(addressField)
    )
);

let countCalls = 0, MAX_CALLS = 40

console.log(`Geocoding ${recordsToUpdate.length} in ${base.name}`);
console.log(`Rate is limited to ${MAX_CALLS} in this batch.`);

for (let record of recordsToUpdate) {

    if (++countCalls > MAX_CALLS) continue

    let address = record.getCellValueAsString(addressField)
    if (address.trim() == '') continue
    console.info(`🔍 Searching: ${address}`)

    const jsondata = await runGeocoder(address)

    if (jsondata == null) {
        console.warn('❌ No data')
        continue
    } else if (!('results' in jsondata)) {
        console.warn('❌ No results')
        continue
    }
    const res = jsondata['results']
    if (res.length == 0) {
        console.warn('❓ Not found')
        await table.updateRecordAsync(record, { 
            "weight": 10
        })
        continue
    }
    let detail = res[0]
    let maxweight = Math.min(detail.weight, 10)
    await table.updateRecordAsync(record, { 
        "weight": maxweight,
        "lat": detail.attrs.lat,
        "lon": detail.attrs.lon,
    })
    console.info(`☑️ Record updated (${record.id})`)
}

async function runGeocoder(q) {
    let url = 'https://api3.geo.admin.ch/rest/services/api/SearchServer?' +
              'type=locations&origins=address&limit=1&' +
              'searchText=' + q;
    return await fetch(url, {
        method: 'GET'
    }).then(async response => {
        const contentType = response.headers.get('content-type')
        if (!contentType || !contentType.includes('application/json')) {
            return null
        }
        return response.json()
    }).catch(error => console.error(error));
}