Here’s a simple script - so small it should be labelled a scriptlet. It takes a crappy date value stored as a string from one field and scrubs it for use in a real date field. In practice, it might be easier to clean up this date column easier with a formula field, but then you’d have another field with dependencies. While formulas are fine for simple tasks, this little block can implement very complex string handling and math computations.
As such, this example is simply an educational step to get some experience with the new Script Block. It could be broadly applicable to almost any data cleaning or data-building process. (complete source provided at the bottom)
The data import and resulting cleaned column look like this:
Now, imagine a case where you had the lat/lng of your business and the lat/lng of every sale.
How could you calculate the distance from your business to each buyer’s location?
In a very similar script block process, you’d simply pass the two locations into this function and it would return the distance (in miles) which you could then easily populated as a new data field.
Considering this example, you are now in a wholly new class of capability that Airtable formulas cannot achieve because they lack the support for complex mathematical equations.
Distance Computations Function
function getDistance(c1, c2, opt)
{
var lat1 = rad(c1.lat), lat2 = rad(c2.lat);
var lng1 = rad(c1.lng), lng2 = rad(c2.lng);
var dLng = (lng2-lng1), dLat = (lat2-lat1);
var R = 6371/1.6;
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.sin(dLng/2) * Math.sin(dLng/2) *
Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
return parseInt(R * c);
}
function rad(degrees) {
return degrees * Math.PI/180;
}
Data Scrubber Script Block
/*
***********************************************************
ScriptBloqs - DataScrubber
Copyright (c) 2020 by Global Technologies Corporation
ALL RIGHTS RESERVED
***********************************************************
*/
// configure data scrubber
let targetTableName = "Sales Data";
let sourceFieldName = "ORDERDATE";
let targetFieldName = "ORDER_DATE";
let sourcePattern = " 0:00";
let sourcePatternI = 0;
// display app title
output.markdown('# DataScrubber');
output.markdown('DataScrubber working...');
let targetTable = base.getTable(targetTableName);
let result = await targetTable.selectRecordsAsync();
// set a counter for testing
var count = 0;
// iterate across all the records
for (let record of result.records)
{
// read the target field
var targetFieldValue = record.getCellValue(targetFieldName);
// scrub if target field is empty
if (!targetFieldValue)
{
// get the value of the source field
var sourceStr = record.getCellValue(sourceFieldName);
var targetStr = toIsoDate(sourceStr.split(sourcePattern)[sourcePatternI]);
// output.markdown(targetStr);
// increment the count
count += 1;
await targetTable.updateRecordAsync(record.id, {
[targetFieldName] : targetStr
})
// test only the first 10 hits
if (count > 10)
break;
}
}
output.markdown('DataScrubber Finished - ' + count + ' updates.');
//
// to iso date str
//
function toIsoDate(dateStr)
{
var date = new Date(dateStr);
var year = date.getFullYear();
var month = date.getMonth() + 1;
var dt = date.getDate();
dt = (dt < 10) ? '0' + dt : dt;
month = (month < 10) ? month = '0' + month : month;
return(year + '-' + month + '-' + dt);
}
