Help

Re: Field Tweaker - Script Block for In-place Transformations

3813 0
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

When you run Field Tweaker, you define a changeset which includes the table name and the field name you want to tweak, and lastly - the tweak you want to apply which in this example script block includes:

  • Upper Case (LIKE THIS)
  • Lower Case (like this)
  • Title Case (Like This)

In the following example, we have a field named PRODUCTLINE where values have been imported in lower case.

image

Ideally, we’d like this data to more readable in reports and such using title case. Rather than create a complex formula and a new field, Field Tweaker can be used to normalize this data straightaway.

image

The script will then confirm your changeset by displaying the first five records that it will modify with the current value compared with the new tweaked value.

image

Selecting Yes to apply these changes will transform every record in the PRODUCTLINE column into new title case values.

image

Now we have some human-chummy data. :winking_face:

image

Using Field Tweaker to manicure the STATUS column is equally effortless.

image

The results…

image

Extending Field Tweaker

While in-place transformations involving upper, lower, and title case are useful tools, this is just the tip of the vast challenges involving data consistency. The script is designed to be extended, so feel free to experiment - the javascript environment provides all the data manipulation functions you might ever need.

Field Tweaker Script

The entirety of the script is included here. Simply create a Script Block and paste it in and run it - that’s all you need to do to install this block.

/*

   ***********************************************************
   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 < 5)
            {
                oChanges[count.toString()] = {};
                oChanges[count.toString()][sourceFieldName + " (current value)"] = sourceStr;
                oChanges[count.toString()][sourceFieldName + " (new value)"] = targetStr;
            }
            count ++;
        }

    }

    // if limited request
    if ((trxLimit != 0) && (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();
        }
    );
}
18 Replies 18

Hello @Bill.French & Community Scripters,

Inspired by

//
        // 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();

from this Bill FRENCH’s Script,
I just tried to script & test my own “Update Analytics” using SHEETS and Google App Script as Bill did or something closed to Bill’s way, I hope although I’m not sure !

You can find my Screen-Shots Log sorted by Date/Time here.

My Google App Script code inspiration is here
mostly from Using Google Sheets as a Web App
and Author’s GAS Code Repo is here !

Here is my own adapted GAS Code attempt:
( ! this script will never run as it is from Airtable Script Block but runs from Google App Script !)

//this is a function that fires when the webapp receives a GET request
function doGet(e) {
  return HtmlService.createHtmlOutput("get request received");
}

//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  var params = JSON.stringify(e.postData.contents);
  params = JSON.parse(params);
  var myData = JSON.parse(e.postData.contents);
  var testUserid = myData.userid;
  var testUsername = myData.username;
  var testUseremail = myData.useremail;
  //
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = Math.max(sheet.getLastRow(),1);
  sheet.insertRowAfter(lastRow);
  var timestamp = new Date();
  sheet.getRange(lastRow + 1, 1).setValue(timestamp);
  sheet.getRange(lastRow + 1, 2).setValue(testUserid);
  sheet.getRange(lastRow + 1, 3).setValue(testUsername);
  sheet.getRange(lastRow + 1, 4).setValue(testUseremail);
  sheet.getRange(lastRow + 1, 5).setValue(params);
  SpreadsheetApp.flush();
  return HtmlService.createHtmlOutput("post request received");
}

Anyone who would try it only have to PUBLISH it as a WEB App as explained here , mostly from Using Google Sheets as a Web App, to get WebHook URL.

My Airtable Script-Block Script lightly adapted from Bill’s one is here:

//
    //
    //
    // update analytics
    // ACTION_02 : MyActionsRepoURL
    // inspired by : Field Tweaker - Script Block for In-place Transformations
    //               https://community.airtable.com/t/field-tweaker-script-block-for-in-place-transformations/27532/8
    // author : https://community.airtable.com/u/Bill.French
    //
    //
    
    // establish the cgp web service endpoint
    //
    // ! Anyone who would try it only have to PUBLISH it as a WEB App as explained here:
    // https://blog.runscope.com/posts/tutorial-capturing-webhooks-with-google-sheets
    // mostly from *Using Google Sheets as a Web App*, to get WebHook URL.
    //
    let gcpUrl = "https://script.google.com/macros/s/MyGoogleWEBappURL/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();
    //
    //
//

I got it running and POSTING to my Google SHEET
but I got 2 ISSUES:

ISSUE01:

Just Another CORS Case:
My-own-Update-Analytics-Inspired-by-Field-Tweaker-and-GAS-2020-08-15-at-10_50_32

I tried to get rid of CORS by use of recent Airtable Automation Beta
but it threw an error on
session.currentUser.id , session.currentUser.name , session.currentUser.email because session. is not invocable from Automation Script TEST.

ISSUE02:

All the expected payload didn’t appear in SHEET:

My-own-Update-Analytics-Inspired-by-Field-Tweaker-and-GAS-2020-08-15-at-12_12_27

“useremail” : session.currentUser.email from

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

is missing in SHEET after each POST.

I first thought that was result of CORS ISSUE01 but it isn’t:
When I replaced “useremail” : session.currentUser.email
by “useremail” : ‘MyOwnMailAdress’ in an AUTOMATION Script Attempt , SHEET’s POST remained the same as screenshot.

Thank you to pay attention to my little report and I hope Community could help.

oLπ

Peter_Borg
6 - Interface Innovator
6 - Interface Innovator

Thanks Bill, this works beautifully.

We noticed something with special characters though (e.g á). If such a char is first in a word, then the Title Case option will not make it upper case, but makes the second character upper case. I couldn’t work out in your code exactly where this is happening, probably because I don’t understand all of it. In particular I don’t know what /\w\S*/g is doing in the below. Suspect it has somethign to do with that. Can you point me in the right direction?

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

My example used a regex approach which is very fast and can probably be enhanced to support special characters. This regex string is very simple and basically says that for any word, process the first string character. Apparently, special characters are eliminated by the “S*”. You can dig deeper into enhancing this approach here.

There are three ways to transform strings to title case (described here). For your situation, I think you need to move toward the for…loop approach. It’s a little slower but should work for the higher-order bit values of an international character set.

Peter_Borg
6 - Interface Innovator
6 - Interface Innovator

thanks @Bill.French, the for…loop approach worked a treat.

Marty_McCoy
6 - Interface Innovator
6 - Interface Innovator

I want to include a script for cleaning up a date field in an automation. How could I modify this script for use in an automation?

@Marty_McCoy This script is designed to change the case of text in a text field. It is not designed for cleaning up date information. The logic for cleaning date information is very different from the logic to for changing the case of text. This script also was not designed to be run from an automation. You are better off starting off from scratch with your automation script.

Scott_Weir
4 - Data Explorer
4 - Data Explorer

Hey @Bill.French ,

I’ve managed to replicate the Field Tweaker natively in Coda but in addition it also de-capitalizes chosen smaller words (such as of, and, the, or)

Currently, I am syncing Airtable data to a Coda doc, normalizing the text in Coda with native Coda formulas, then sending my normalized text back to Airtable and updating that there via a webhook invoked automation.

But I’d love to do it natively via a Script but I don’t think your Field Tweaker allows me to keep small words de-capitalized. Does it? If not, do you know how I’d do it?

By the way - Heres the doc showing the text normalizer. Its pretty awesome

Nope, but it could. You just need an index of stop words (like this) and gate the process to skip over those tokens.

Kiki
4 - Data Explorer
4 - Data Explorer

FYI, this is kinda bruteforce and not at all sophisticated, but here's a simple formula that just de-capitalizes everything after the first letter, for transforming UPPERCASE to TitleCase:

REGEX_REPLACE(
LastName,
MID(LastName, 2, LEN(LastName)),
LOWER( MID(LastName, 2, LEN(LastName)))
)