Help

Script to calculate mileage between two UK postcodes

972 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Hairtable
6 - Interface Innovator
6 - Interface Innovator

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');
}

 

0 Replies 0