Re: Field Tweaker - Script Block for In-place Transformations
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.
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.
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.
Selecting Yes to apply these changes will transform every record in the PRODUCTLINE column into new title case values.
Now we have some human-chummy data. :winking_face:
Using Field Tweaker to manicure the STATUS column is equally effortless.
The results…
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();
}
);
}
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?
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 -
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.
