I've just worked on my first script for Airtable and got it how I want it for now, before taking it any further.
This script runs on a Table named "Travel" and takes two UK postcodes, in fields labelled P1 and P2, passes them to the Bing Maps API, and returns the distance between them, in miles, to a field labelled Distance. Check the API documentation re: call limits and pricing plans to gauge suitability/affordability for your use-case.
In its current form, the script is triggered by an Airtable button field on each record/row; I'm looking to develop this further to run from a "when record enters a view" automation to trigger a slightly modified script.
let apiKey = 'INSERT_BING_MAPS_API_KEY_HERE'; // Replace with your actual Bing Maps API key
let baseId = 'BASE_ID'; // Replace with your actual base ID
let tableName = 'Travel'; // Replace with your actual table name
let p1Field = 'P1'; // Field name for the P1 postcode
let p2Field = 'P2'; // Field name for the P2 postcode
let distanceField = 'Distance'; // Field name for the Distance field
let p1Postcode = '';
let p2Postcode = '';
// Prompt the user to select a record
let table = base.getTable(tableName); // Get the table object
let record = await input.recordAsync('Select a record from the table', table);
if (record) {
// Get the P1 and P2 postcodes from the selected record
p1Postcode = record.getCellValueAsString(p1Field);
p2Postcode = record.getCellValueAsString(p2Field);
// Make sure both postcodes are not empty
if (p1Postcode && p2Postcode) {
// Make API call to Bing Maps API
let url = `https://dev.virtualearth.net/REST/v1/Routes/Driving?wayPoint.1=${p1Postcode}&wayPoint.2=${p2Postcode}&distanceUnit=mi&key=${apiKey}`;
let response = await fetch(url);
let data = await response.json();
// Extract travel distance from API response
let travelDistance = data.resourceSets[0].resources[0].travelDistance;
// Update the Distance field in the selected record
let updates = {
[distanceField]: travelDistance
};
await table.updateRecordAsync(record, updates);
output.text(`Distance updated successfully with value: ${travelDistance}`);
} else {
output.text('Error: Postcode fields are empty in the selected record');
}
} else {
output.text('No record selected');
}
