Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Script to calculate mileage between two UK postcodes

1167 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