# Simple Data Scrubber

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.

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

return degrees * Math.PI/180;
}
``````

Data Scrubber Script Block

``````/*

***********************************************************
ScriptBloqs - DataScrubber
Copyright (c) 2020 by Global Technologies Corporation
***********************************************************

*/

// 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)
{

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);
}``````
10 Likes

Your Data Scrubber Script Block was exactly the Script I was looking for so I could run automations from since you don’t get the same automations from dates (like trigger when date is today) from a formula field.

Some of our data won’t always have a date attached and I get a null error from the script at
var targetStr = toIsoDate(sourceStr.split(sourcePattern)[sourcePatternI]);
// output.markdown(targetStr);

As a work around I just wrote in the formula field:
`IF({Premiere} = "", "1/1/2000"`

So it would populate something that I would know wasn’t a “real” date (from the Prince song “Party like it’s 1999.”).

Is there a better way to accomplish this that you wouldn’t mind helping me with?