Field Tweaker - Script Block for In-place Transformations

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?

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)

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!

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 -

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.

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.

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:

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π

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

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.

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

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.

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.

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: