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();
}
);
}
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:
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:
“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π
