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π
