Help

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

3486 1
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
Alexander-Jerry
6 - Interface Innovator
6 - Interface Innovator

Hi Bill! I’m really excited about being able to use this.
I’m getting an error on line 25, could you help me out?

image

Yep - this is why - the folks at Airtable decided to make a dozen breaking changes at the same instant we published the scripts.

I have updated the script in the example base and in the original post.

I think it’s all fixed and should work with the latest release. (fingers crossed)

Alexander-Jerry
6 - Interface Innovator
6 - Interface Innovator

This is amazing Bill! It works great, I can’t wait to use this in our workflow. This will be very helpful for cleaning up all of those miscapitalized contacts and accounts!

Federico_Gh
4 - Data Explorer
4 - Data Explorer

Hi Bill! The script is great and simple to use. I´m running it on a +1500 record base.

I just want to input a series of word exceptions – meaning: I do not want to run the script transformation for some given words (ie: “for”, “of”, “from”). Which is the most practical way to code that? A NOT formula? Just doing my first steps in scripting!!! :alien:

Hi Frederico, and welcome to the community!

Okay - so these are “stop-words”; cases where you want the process to skip over them and not take any action, right? If so, there is a section of the code that performs the transformation -

image

Each case applies a different transformation method to the data value in the table. You would have to read each data value, parse that into separate tokens using split() and that apply the transformation method at the word-level instead of to the entire data value.

Not trivial to change, but also not difficult.

Indresh_Kumar
6 - Interface Innovator
6 - Interface Innovator

Umm, the script sends user details to an external service. That doesn’t look good at all. It seems to be happening without a consent from user.

Edit: I just noticed that airtable itself promoted this in one of the posts here. So, it is kind of “allowed” but may be take consent from the user before collecting personally identifiable information?

I was waiting to see when anyone might take notice of this tracking metric. :winking_face: I fully disclosed this in My Script Store and the code was completely reviewed by Airtable since it was submitted into a script contest.

Indeed, this script was developed and openly shared to demonstrate that which Airtable suggested in a few other intimations. However, note that no table data or actions on the table data is being tracked; only who used it and when. You are free of course to integrate this code into your business use and modify it as you like - perhaps substituting your own endpoint for tracking metrics.

Finally, someone noticed and this is the perfect context for people to get wiser about introducing script into their processes.

Seriously? Does Airtable ask your consent to know how long you looked at a specific record this morning? Indeed, privacy and data matter greatly, but every keystroke, every query, every new record formulates a comprehensive collection of metrics that are used to help companies like Airtable and Global Technologies Corporation create and improve services and software.

This is a simple little script block with full source code provided and the added guidance that you should examine all source code introduced into your systems. Imagine what we might find if Airtable open-sourced all code? :winking_face:

So, it is kind of “allowed” ...

You need to ponder the idea that things in software are “allowed” or “disallowed”. There are no rules per-se; there are only laws and laws are very different across countries and localities. It is not illegal to track usage metrics of any application. It might be considered poor practice, but it is common to seek out who may be using just about everything. Tesla (for example), knows how many times you touched the volume control on your audio system in the past hour. Every website knows clearly who you are and what your interests are.

Bill, I don’t have a strong opinion here.

I see your prescriptive. I used the script you made, and learnt a few tricks from it too. Keep up the good work, and thank you.

PS: you have my email, say hi sometime, send a clever script may be.

Joshua_Dawson
4 - Data Explorer
4 - Data Explorer

This is great! Has anyone seen anything like this that can format a number into currency format in a text field eg 10000 into $10,000

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