Simple Data Scrubber

[Bill_French]

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

[Nashville_Publi]

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?