Field Tweaker

After importing data, it’s not uncommon to want to clean up and normalize data values, especially text fields that may have all types of inconsistencies. Unfortunately, Airtable doesn’t support in-place transformations where formulas can be applied to modify the field contents of all cells.

This is where the Field Tweaker script block can help. It supports in-place text transformations including upper case, lower case, and title case modifications across all records for select fields. The script is published below and this base provides a simple sales table to experiment with and also includes the script block.

Script Code

							/*

   ***********************************************************
   ScriptBloqs - Field Tweaker v1.2
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

//
// display the title
//
output.markdown("# Field Tweaker");
output.markdown("This script block helps you tweak select fields by applying string handling functions for all records.");
output.markdown("Select the table name, the field name that you want to tweak, and the type of tweak.");
output.markdown("Tweak types that you may apply include ... Upper Case (**LIKE THIS**), Lower Case (**like this**), and Title Case (**Like This**)");
//
// prompt for the details
//

// get the table name
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;
var records         = await sourceTable.selectRecordsAsync();

// get the field name
let sourceField = await input.fieldAsync("Pick the field to be tweaked:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get the delimiter
let tweakFormula = await input.textAsync("Enter the tweak formula (i.e., upper case, lower case, or title case):")
tweakFormula = tweakFormula.toString().toLowerCase();

// determine the string operation
if ((tweakFormula.indexOf("lower") > -1) && (tweakFormula.indexOf("case") > -1))
    var formulaAction = "toLowerCase";
if ((tweakFormula.indexOf("upper") > -1) && (tweakFormula.indexOf("case") > -1))
    var formulaAction = "toUpperCase";
if ((tweakFormula.indexOf("title") > -1) && (tweakFormula.indexOf("case") > -1))
    var formulaAction = "toTitleCase";

// test for limit command
let trxLimit = 0;
if ((tweakFormula.indexOf(" limit ") > -1))
    trxLimit = tweakFormula.split(" limit ")[1];

// output.text(formulaAction);

// set up the counters
let max   = (trxLimit > 0) ? (trxLimit - 1) : 0;
let count = 0;

// enumerate the records ... applying the changes
let aChangeSet = [];
let oChanges   = [];
for (let record of records.records)
{

    // read the source field for this record
    var sourceStr = record.getCellValue(sourceFieldName);

    // tweak if target field has data        
    if (sourceStr)
    {

        // apply the formula action to the source values
        switch (formulaAction)
        {
            // to upper case
            case "toUpperCase":
                var targetStr = sourceStr.toUpperCase();
                break;
            // to upper case
            case "toLowerCase":
                var targetStr = sourceStr.toLowerCase();
                break;
            // to title case
            case "toTitleCase":
                var targetStr = toTitleCase(sourceStr);
                break;
            // default
            default:
                var targetStr = sourceStr;
                break;
        }

        // if changed, queue it for a change
        if (targetStr != sourceStr)
        {
            aChangeSet.push([record.id, targetStr]);
            if (count = trxLimit))
      break;

}

// do we have any changes to apply?
if (aChangeSet.length)
{
    //
    // render a list of example changes
    //
    output.clear();
    output.markdown("# Field Tweaker");
    output.markdown("## Change Set");
    output.markdown("* Table Name: **" + sourceTableName + "** \n* Field Name: **" + sourceFieldName + "** \n* Tweak: **" + tweakFormula + "** (" + formulaAction + ")");
    output.markdown("## Example Changes");
    output.table(oChanges);

    //
    // confirm changes
    //
    let shouldReplace = await input.buttonsAsync('Apply Changes?', [
        {label: 'Yes', variant: 'danger'},
        {label: 'Cancel'},
    ]);
    // Update all the records
    var changeCount = 0;
    if (shouldReplace === 'Yes') {

        // display progress indicator
        output.markdown('## Applying Tweaks ...');

        for (var i in aChangeSet)
        {
            await sourceTable.updateRecordAsync(aChangeSet[i][0], {
                [sourceFieldName] : aChangeSet[i][1]
            })
            changeCount += 1;
        }

        //
        // update analytics
        //

        // establish the cgp web service endpoint
        let gcpUrl = "https://script.google.com/macros/s/AKfycbxGO0QVOYEiiBkseEh3AUSnHFMpS0OXidRtAUMTNIjj_EaFdBo/exec";

        let payload = {
            "userid"    : session.currentUser.id,
            "username"  : session.currentUser.name,
            "useremail" : session.currentUser.email
        }

        let postOptions = {
            method: "post",
            headers: {
                'Accept' : 'application/json',
            },
            body: JSON.stringify(payload)
        }
        const postResults = await fetch(gcpUrl, postOptions);
        // const jsonPost    = await postResults.json();

        // that's a wrap
        output.markdown('## Field Tweaker Finished - ' + changeCount.toString() + ' modifications.');
    } else {
        // that's a wrap
        output.markdown('## Field Tweaker Finished - Cancelled, no modifications made.');
    }

} else {
    // that's a wrap
    output.markdown("# Field Tweaker");
    output.markdown('## Field Tweaker Searched - but nothing needs tweaking!');
}

//
// title case
//
function toTitleCase(str)
{
    return str.replace(
        /\w\S*/g,
        function(txt) {
            return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
        }
    );
}