2

How can I automated find and replace in a Numbers file based on a predefined master list?

For example, I want to find and replace all:

  • "ABC" to "XYZ"
  • "123" with "789"

A few thoughts / workflow expectations:

  • My find and replace routine has become burdensome as I manually run through 20+ find / replace actions
  • Find and replace will be for the whole document, not limited to a certain column, row, cell
  • I want to have running master list that I can add to
  • I would like to open a file, run this script and have the find/replace done
  • It would be great if I can incorporate a wildcard in the find
1
  • I am so happy you asked this. Can we assume your automation will run on macOS?
    – bmike
    Apr 23 at 19:16
2

I'm not exactly sure how you have the Numbers document formatted or how exactly you want to store the Master List of Find & Replace items or where, however, the following example AppleScript code is set up to show the use of the Master List of Find & Replace items as a single table in a sheet in the Numbers document and is comprised of two columns and however many rows you need.

The images below show the sheets, the Master List of Find & Replace items and the before and after of sheet 1. The data is simplistic but the bottom line is it works to quickly find and replace the pairs of Find & Replace items in the Master List.

This will be so much faster than any manual process to accomplish the same goal.

Note that as coded, it assumes there are no formulas in the cells and they are formatted as text.


Example AppleScript code:

  • As coded this works with just the active sheet in the front document.
tell application "Numbers"
    tell front document
        
        --  # Get the Find & Replace list.
        
        set theFindReplaceList to ¬
            the formatted value of ¬
                the cells of the rows 2 thru -1 of ¬
                    table "Find & Replace" of ¬
                    sheet "Master List" whose ¬
            value is not missing value
        
        --  # Clean up the List removing empty cells and rows 
        --  # so there are only pairs of find/replace items. 
        
        set theFindReplaceListItems to {}
        repeat with aListItem in theFindReplaceList
            if the length of aListItem is 2 then ¬
                copy aListItem to the end of theFindReplaceListItems
        end repeat
        copy theFindReplaceListItems to theFindReplaceList
        
        --  ### Find & Replace ###
        
        if the name of active sheet is not "Master List" then
            
            --  # For each table of the active sheet.
            
            repeat with i from 1 to the count of the tables of the active sheet
                
                --  # For each Find & Replace pair.
                
                repeat with thisListItem in theFindReplaceList
                    
                    --  # For every cell containing the FIND text. 
                    
                    set theReplaceCellsList to the (cells of table i of sheet 1 ¬
                        whose formatted value contains item 1 of thisListItem)
                    
                    --  # Replace it with the REPLACE text.
                    
                    --  # Uncomment 'considering case' and 'end considering'
                    --  # for case senstive find/replace operations.
                    
                    --considering case
                    repeat with aCell in theReplaceCellsList
                        set the value of aCell to my findAndReplaceInCellValue(formatted value of aCell, ¬
                            item 1 of thisListItem, item 2 of thisListItem)
                    end repeat
                    --end considering
                    
                end repeat
            end repeat
            tell table 1 of active sheet to set selection range to range "A1"
        end if
    end tell
end tell


--  ## Handler ##

on findAndReplaceInCellValue(theFormatedCellValue, theFindValue, theReplaceValue)
    set AppleScript's text item delimiters to theFindValue
    set theTextItems to every text item of theFormatedCellValue
    set AppleScript's text item delimiters to theReplaceValue
    set theFormatedCellValue to theTextItems as string
    set AppleScript's text item delimiters to ""
    return theFormatedCellValue
end findAndReplaceInCellValue

Notes:

While the example AppleScript code as coded is using a sheet within the target document for its Find & Replace items, nonetheless, it can be coded to use and external source, e.g., another Numbers document.

In either code examples, the Master List sheet is not touched by the find/replace operations.

The find/replace operations are case insensitive. If you need case sensitive find/replace then uncomment the considering case and end considering statements in the example AppleScript code.

As coded the find/replace operations act on cells containing the value of FIND text and anything else in the cell. If you want to limit to cells that have only the FIND text and nothing else, then:

Change:

set theReplaceCellsList to the (cells of table i of aSheet ¬
    whose formatted value contains item 1 of thisListItem)

To:

set theReplaceCellsList to the (cells of table i of aSheet ¬
    whose formatted value is item 1 of thisListItem)

The example AppleScript code can be used in an Automator Service/Quick Action using a Run AppleScript action and assigned a keyboard shortcut in System Preferences > Keyboard > Shortcuts > Services or used with any third-party application that can run AppleScript scripts, etc



Example AppleScript code:

  • As coded this works with all sheets in the front document.
tell application "Numbers"
    tell front document
        
        --  # Get the Find & Replace list.
        
        set theFindReplaceList to ¬
            the formatted value of ¬
                the cells of the rows 2 thru -1 of ¬
                    table "Find & Replace" of ¬
                    sheet "Master List" whose ¬
            value is not missing value
        
        --  # Clean up the List removing empty cells and rows 
        --  # so there are only pairs of find/replace items. 
        
        set theFindReplaceListItems to {}
        repeat with aListItem in theFindReplaceList
            if the length of aListItem is 2 then ¬
                copy aListItem to the end of theFindReplaceListItems
        end repeat
        copy theFindReplaceListItems to theFindReplaceList
        
        --  ### Find & Replace ###
        
        --  # For each sheet in the document.
        
        repeat with aSheet in (sheets whose name is not "Master List")
            
            --  # For each table of the sheet.
            
            repeat with i from 1 to the count of the tables of aSheet
                
                --  # For each Find & Replace pair.
                
                repeat with thisListItem in theFindReplaceList
                    
                    --  # For every cell containing the FIND text. 
                    
                    set theReplaceCellsList to the (cells of table i of aSheet ¬
                        whose formatted value contains item 1 of thisListItem)
                    
                    --  # Replace it with the REPLACE text.
                    
                    --  # Uncomment 'considering case' and 'end considering'
                    --  # for case senstive find/replace operations.
                    
                    --considering case
                    repeat with aCell in theReplaceCellsList
                        set the value of aCell to my findAndReplaceInCellValue(formatted value of aCell, ¬
                            item 1 of thisListItem, item 2 of thisListItem)
                    end repeat
                    --end considering
                end repeat
            end repeat
            tell table 1 of active sheet to set selection range to range "A1"
        end repeat
    end tell
end tell


--  ## Handler ##

on findAndReplaceInCellValue(theFormatedCellValue, theFindValue, theReplaceValue)
    set AppleScript's text item delimiters to theFindValue
    set theTextItems to every text item of theFormatedCellValue
    set AppleScript's text item delimiters to theReplaceValue
    set theFormatedCellValue to theTextItems as string
    set AppleScript's text item delimiters to ""
    return theFormatedCellValue
end findAndReplaceInCellValue

Master List

enter image description here

The Find & Replace table must always be two columns, however, the script is coded to only process rows containing both a find and replace item.


Before:

enter image description here


After:

enter image description here


Note: The example AppleScript code is just that and sans any included error handling does not contain any additional error handling as may be appropriate. The onus is upon the user to add any error handling as may be appropriate, needed or wanted. Have a look at the try statement and error statement in the AppleScript Language Guide. See also, Working with Errors. Additionally, the use of the delay command may be necessary between events where appropriate, e.g. delay 0.5, with the value of the delay set appropriately.

2
  • It works! I'm applying it to my file now to see if there are any nuances that make the script get hung. Thank you for the help.
    – say
    Apr 29 at 15:14
  • @sevens, You are welcome. Let me know if your have any issues, I'm sure I can adjust the code if need be. Apr 29 at 15:17
0

So far this is what I have. I know the answer is incomplete, but you can see if this is the right path for you. I will edit when I get it complete.

  1. "Sheet 1" with data:

enter image description here

  1. Master sheet with find/replace values:

enter image description here

  1. Script so far:

enter image description here

Code:

on run {input, parameters}
tell application "Numbers"
    activate
    tell front document
        tell sheet "Master" -- use info from master sheet
            tell table 1
                set FIND to value of cell "B2" as text
                set REPLACE to value of cell "C2" as text
            end tell
        end tell
    end tell
    tell document 1 of application "Numbers"
        set active sheet to sheet 1 -- work data from Sheet 1
    end tell
end tell
tell application "System Events"
    key code 3 using {command down} -- Command+F to search/replace
    delay 0.15
    set the clipboard to FIND
    delay 3 -- enough time to perform search (should be tweaked for large documents)
    key code 9 using {command down} -- paste data to find
    delay 0.8
    key code 48 -- tab
    delay 0.8
    set the clipboard to REPLACE
    delay 0.8
    key code 9 using {command down} -- paste data to replace
    delay 0.8
    key code 48 -- tab
    delay 0.8
    key code 49 -- space (press "replace all" button)
end tell
end run
  1. State before "replace all":

enter image description here

  1. Final state:

enter image description here

Next steps:

  • Loop
  • Detect empty cells on Master sheet
  • Finish script
4
  • Since AppleScript provides an avenue to directly manipulate the value of cells in a Numbers document, there should be no need to resort to the use of UI Scripting with System Events to perform find/replace in the cells. This approach is needlessly kludgy and prone to failure. Apr 23 at 17:10
  • @user3439894 funny, I knew you would appear to criticize. Apr 23 at 21:51
  • Thank you for taking the time to respond Mateus. It was helpful.
    – say
    Apr 29 at 15:42
  • Mateus Ribeiro, RE: "funny, I knew you would appear to criticize." -- I'm not intentionally trying to criticize 'you' and if you choose to take it that way, sorry but that's on you as I cannot control your feelings. What I said in my first comment is a critique of the info in the answer, not meant to be personal or taken that way, as it's just plain fact and a spot on analysis of the approach taken. If Apple did not provide a direct way to manipulate cell data with AppleScript then this answer, albeit kludgy as UI Scripting often is, would have been a good approach. Apr 29 at 15:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .