Script Block: Find & Replace Text

In response to a long-running post about needing a find and replace function here:

Here’s a script that does just that:

// This is free and unencumbered software released into the public domain.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
// IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
// OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
// ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
// OTHER DEALINGS IN THE SOFTWARE.
//
// AUTHOR: Jonathan Bowen (www.dragondrop.uk)
// SCRIPT: Script for Airtable script block to find and replace text
// DISCLAIMER:
// - Test the script first on a copy of your base
// - Try a few different scenarios to make sure it works as you expect
// - Take a snapshot of your production base before using the script
// - Duplicate the field you're working on (with field contents) in case you want to go back to the original content
// - Use at your own risk!


// user chooses the table...
let table = await input.tableAsync('Pick a table');
// ...and a field
let field = await input.fieldAsync("Pick a field", table);
// Shows the field name and field type
output.text(`Field "${field.name}" has type "${field.type}".`);

// The script only works against text-type fields
if(field.type == 'singleLineText' || field.type == 'multilineText' || field.type == 'richText') {
    // enter the string you want to find...
    let stringToFind = await input.textAsync('Enter the string to find');
    // ...and the string you want to replace it with
    let stringToReplace = await input.textAsync('Enter the string to replace it with');
    // define a new regex based on the sting to find
    // this configuration replaces all instances of the string to find and is case sensitive
    let myRegex = new RegExp(stringToFind,"g");
    // get the records from the table
    let query = await table.selectRecordsAsync();
    let countOfRecords = 0;
    output.text("The following records will be changed:")
    // get the records which contain the string to find and output the before and after version
    // note that records are not changed at this point, we're just showing what will be changed if you go ahead
    for (let record of query.records) {
        if (record.getCellValue(field.name).includes(stringToFind)) {
            let newText = record.getCellValue(field.name).replace(myRegex, stringToReplace);
            output.text(`(Record: ${record.name}) ${record.getCellValue(field.name)} => ${newText}`);
            output.text("============================");
            countOfRecords = countOfRecords += 1;
        }
    }
    // if nothing matches the string to find, then finish
    if (countOfRecords == 0) {
        output.text("Sorry - can't find any records to update - please try again!")
    } else if (countOfRecords > 0) {
        // otherwise check with the user if they want to proceed with the find and replace
        let proceedOrCancel = await input.buttonsAsync('Would you like to proceed or cancel?', ['Proceed', 'Cancel']);
        if (proceedOrCancel === 'Proceed') {
            output.text('Proceeding...');
            for (let record of query.records) {
                if (record.getCellValue(field.name).includes(stringToFind)) {
                    let newText = record.getCellValue(field.name).replace(myRegex, stringToReplace);
                    // update the records
                    let update = table.updateRecordAsync(record, {
                        [field.name]: newText
                    })
                    output.text(`Record: ${record.name} updated`);
                }
            }
            output.text("The find and replace action has finished")
        } else {
            output.text("The find and replace action was cancelled");
        }
    }
} else {
    // if field type is not a text type field then let the user know
    output.text(`Sorry - can't do find & replace on a ${field.type} field`);
}

And a quick video to show it in action:

A couple of comments:

  • It works on single line text, multiline text and rich text field. I’ve coded it so that it stops if the field you select is of another type. It is possible that it will work on other field types, e.g. Email, URL (I’m pretty sure it will), but haven’t tested it out on those.
  • I can’t figure out a way to replace something with nothing, i.e. remove a string from the field. This is because the “replace with” field won’t accept an empty string or at least you can’t click on “next” until you enter something. Maybe someone has a trick to get around this.
  • The regex is case sensitive, i.e. “Dog” will not find “dog”

JB

7 Likes

Great script!

Have a button input that asks if you want to delete or replace the selected text. If the user wants to replace the selected text, ask for the text string. If the user wants to delete the text, you don’t need to ask for a text string.

If you are interested in other ways to improve / expand the script here are some suggestions. (I do not at all expect you to implement these suggestions, as I think you are already being generous to share this script. But if anyone has time on their hands …)

  • Tell the end user that the script uses regular expressions and have a link to a regular expression reference. This will help users when they get unexpected results when they use special characters that are not escaped.

  • Limit field selection to only the allowable fields so that the user can’t choose invalid fields. Get the list of fields from the table. Filter the list of fields based on the field type. Display the allowable fields as buttons for the user to pick.

  • For case insensitive matching, pass the i modifier as well as the g modifier. You can also have a button asking whether or not the find should be case insensitive.

There are so many possibilities of where to go with this. It almost makes me wish the code were in a GitHub repository where other people could make pull requests.

1 Like

@kuovonne - public github repo here:

:slight_smile:

2 Likes

I work around this by allowing users to enter an “escaped” space (i.e., <space>) and then test the entry for escaped character entries.

This is also helpful if you want to look for certain characters at the end of a line and other complex processes that tiptoe into the regEx realm.

1 Like

Hi @JonathanBowen,

I just tested your CODE against one of my current TEXT tables.
Getting ERRORs while iterating NULL values from some of my RECORD’s TEXT Fields, I’m proposing you the following FORK and COMMITS:

// FORKED by Olpy_Acaflo on 20-06-20 at 00h30 Paris, France Local Time

// https://community.airtable.com/u/Olpy_Acaflo

// from: https://community.airtable.com/t/script-block-find-replace-text/28658

// to:

//

// Commit 1: if (record.getCellValue(field.name) !== null) at Line 66

// Commit 2: if (record.getCellValue(field.name) !== null) at Line 84

// to avoid if (record.getCellValue(field.name).includes(stringToFind)) test on NULL Cell Value that breaks to ERROR.

//

FORKED & EDITED CODE:

// This is free and unencumbered software released into the public domain.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
// IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
// OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
// ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
// OTHER DEALINGS IN THE SOFTWARE.
//
// AUTHOR: Jonathan Bowen (www.dragondrop.uk)
//         https://community.airtable.com/u/JonathanBowen
//         Dragon Drop (www.dragondrop.uk)
//         www.airscript.dev
// 
// 
// Script Block: Find & Replace Text
// Airtable Community URL: https://community.airtable.com/t/script-block-find-replace-text/28658
// 
// Repository: 
// jonathanbow/airtable-find-and-replace
// https://github.com/jonathanbow/airtable-find-and-replace
// 
// 
// SCRIPT: Script for Airtable script block to find and replace text
// DISCLAIMER:
// - Test the script first on a copy of your base
// - Try a few different scenarios to make sure it works as you expect
// - Take a snapshot of your production base before using the script
// - Duplicate the field you're working on (with field contents) in case you want to go back to the original content
// - Use at your own risk!
//
// FORKED by Olpy_Acaflo on 20-06-20 at 00h30 Paris, France Local Time
// https://community.airtable.com/u/Olpy_Acaflo 
// from: https://community.airtable.com/t/script-block-find-replace-text/28658
// to: 
//
// Commit 1: if (record.getCellValue(field.name) !== null) at Line 66
// Commit 2: if (record.getCellValue(field.name) !== null) at Line 84
// to avoid if (record.getCellValue(field.name).includes(stringToFind)) test on NULL Cell Value that breaks to ERROR.
//

// user chooses the table...
let table = await input.tableAsync('Pick a table');
// ...and a field
let field = await input.fieldAsync("Pick a field", table);
// Shows the field name and field type
output.text(`Field "${field.name}" has type "${field.type}".`);

// The script only works against text-type fields
if(field.type == 'singleLineText' || field.type == 'multilineText' || field.type == 'richText') {
    // enter the string you want to find...
    let stringToFind = await input.textAsync('Enter the string to find');
    // ...and the string you want to replace it with
    let stringToReplace = await input.textAsync('Enter the string to replace it with');
    // define a new regex based on the sting to find
    // this configuration replaces all instances of the string to find and is case sensitive
    let myRegex = new RegExp(stringToFind,"g");
    // get the records from the table
    let query = await table.selectRecordsAsync();
    let countOfRecords = 0;
    output.text("The following records will be changed:")
    // get the records which contain the string to find and output the before and after version
    // note that records are not changed at this point, we're just showing what will be changed if you go ahead
    for (let record of query.records) {
        if (record.getCellValue(field.name) !== null)
        { if (record.getCellValue(field.name).includes(stringToFind)) {
            let newText = record.getCellValue(field.name).replace(myRegex, stringToReplace);
            output.text(`(Record: ${record.name}) ${record.getCellValue(field.name)} => ${newText}`);
            output.text("============================");
            countOfRecords = countOfRecords += 1;
        }
        }
    }
    // if nothing matches the string to find, then finish
    if (countOfRecords == 0) {
        output.text("Sorry - can't find any records to update - please try again!")
    } else if (countOfRecords > 0) {
        // otherwise check with the user if they want to proceed with the find and replace
        let proceedOrCancel = await input.buttonsAsync('Would you like to proceed or cancel?', ['Proceed', 'Cancel']);
        if (proceedOrCancel === 'Proceed') {
            output.text('Proceeding...');
            for (let record of query.records) {
                if (record.getCellValue(field.name) !== null) {
                if (record.getCellValue(field.name).includes(stringToFind)) {
                    let newText = record.getCellValue(field.name).replace(myRegex, stringToReplace);
                    // update the records
                    let update = table.updateRecordAsync(record, {
                        [field.name]: newText
                    })
                    output.text(`Record: ${record.name} updated`);
                }
                }
            }
            output.text("The find and replace action has finished")
        } else {
            output.text("The find and replace action was cancelled");
        }
    }
} else {
    // if field type is not a text type field then let the user know
    output.text(`Sorry - can't do find & replace on a ${field.type} field`);
}

Thank you very much for your original script,
I hope my FORK & COMMITs will be useful,

oLπ

1 Like