Find and Replace

This script finds and replaces text in the text fields of your table. It finds specific text patterns in many records and replaces them with new values. You can preview the matches before the script makes changes.

const config = input.config({
  title: 'Find and replace',
  description: `This script will find and replace all text matches for a text-based field you pick.
    You will be able to see all matches before replacing them.`,
  items: [input.config.table('table', { label: 'Table' }), input.config.field('field', { parentTable: 'table', label: 'Field' })],
})

const { table, field } = config
output.text(`Finding and replacing in the ${field.name} field of ${table.name}.`)

const findText = await input.textAsync('Enter text to find:')
const replaceText = await input.textAsync('Enter to replace matches with:')

// Load all of the records in the table
let result = await table.selectRecordsAsync()

// Fetch all records using a while loop
while (result.hasMoreRecords) {
  await result.loadMoreRecords()
}

// Find every record we need to update
const replacements = []
for (const record of result.records) {
  const originalValue = record.getCellValue(field)

  // Skip non-string records
  if (typeof originalValue !== 'string') {
    continue
  }

  // Skip records which don't have the value set, so the value is null
  if (!originalValue) {
    continue
  }

  const newValue = originalValue.replace(findText, replaceText)

  if (originalValue !== newValue) {
    replacements.push({
      record,
      before: originalValue,
      after: newValue,
    })
  }
}
if (!replacements.length) {
  output.text('No replacements found')
} else {
  output.markdown('## Replacements')
  output.table(replacements)

  const shouldReplace = await input.buttonsAsync('Are you sure you want to save these changes?', [
    { label: 'Save', variant: 'danger' },
    { label: 'Cancel' },
  ])

  if (shouldReplace === 'Save') {
    // Update the records
    let updates = replacements.map((replacement) => ({
      id: replacement.record.id,
      fields: {
        [field.id]: replacement.after,
      },
    }))

    // Only up to 10 updates are allowed at one time, so do it in batches
    while (updates.length > 0) {
      await table.updateRecordsAsync(updates.slice(0, 10))
      updates = updates.slice(10)
    }
  }
}

Use Cases

Data Cleanup: Fix typos, make formatting consistent or correct inconsistent data.
URL Updates: Update domain names or path structures in many records.
Content Migration: Replace old terms with new branding or naming conventions.
Bulk Corrections: Fix repeated errors in large datasets.

How it Works

Select Field: Select a text-based field (for example, Single Line Text, Long Text, Email or URL).
Enter Search Pattern: Enter the text to find.
Enter Replacement: Enter the text that replaces the found text.
Preview Matches: Review all matches before you apply the changes.
Confirm Changes: Apply the replacements to all matching records.

Requirements

A text-based field that contains the data to change
Find pattern: The text string to find
Replace pattern: The text string to use as the replacement

Supported Field Types

Single Line Text
Long Text
Email
URL
Phone Number
Any other text-based field

Example

Scenario: Update the company domain in all email addresses

Before:

john@oldcompany.com
jane@oldcompany.com
admin@oldcompany.com

Find: oldcompany.com Replace: newcompany.com

After:

john@newcompany.com
jane@newcompany.com
admin@newcompany.com

Features

Safe Preview: See all matches before the script makes changes.
Exact Matching: Finds only the exact text pattern that you enter.
Batch Processing: Updates all matching records in one operation.
Case Sensitive: Matches must have the same letter case.
Non-Destructive: Replaces only the pattern that you enter. Other text does not change.

Best Practices

Always use the preview to check the matches before you apply changes.
For large tables, test with a small dataset first.
Remember that the search is case sensitive when you enter your search pattern.
Make sure that your replacement text does not cause formatting problems.