Q: Applescript Find & Replace in Text Document from Excel Sheet
I'm new to Applescript and am trying to create a script that can run through a text document (.html) and find & replace various phrases with a corresponding cell in an Excel document.
I will put placeholders in the text to be replaced as B_1 (first value to replace), B_2 (second), B_3 etc...
They should be replaced in order using the cells B1-B9 in the Excel document. The phrases in the Excel document will be changed each time, hence why it has to be dynamic to capture them rather than a standard find & replace script with static values.
I've been following this thread which all makes sense:https://discussions.apple.com/thread/7008048?start=0&tstart=0
However, I keep running into compile errors. Here is my current code:
tell application "Microsoft Excel" set colB_data to value of every cell of range "B1:B9" of worksheet 1 of document 1 end tell tell application "Sublime Text" tell text of "Users/maxquinn/Desktop/index.html" repeat with index from 1 to count colB_data replace ("B_" & index as text) using (item index of colB_data) options {starting at top:true, match words:true} end repeat end tell end tell The first half works fine, but the second half gives me the error "Expected end of line but found identifier." and highlights the 'using' in the 'replace' command (line 8).
Does anyone know why this might be, and are there any other glaring errors in the script?
Thanks!
Max
iMac
Posted on Jul 22, 2016 6:25 AM
Given the following HTML input file, the following AppleScript will replace the B_n values with the contents of the list, and writes a new file out with the changes.
Before After
use scripting additions
set orig_html to ((path to desktop) as text) & "test.html"
set replaced_html to ((path to desktop) as text) & "replaced.html"
-- replace this with the tell block to get the Excel spreadsheet cells into a list
set colB_data to {"A", "B", "C", "D", "E", "F", "G", "H", "I"}
tell application "TextEdit"
set myHTML to read file orig_html
repeat with index from 1 to count of colB_data
set findStr to ("B_" & index) as text
set replaceStr to (item index of colB_data) as text
-- distinctly quoted command-line arguments for Ruby
set args to findStr's quoted form & space & replaceStr's quoted form & space & myHTML's quoted form
set myHTML to my ruby_replace(args) as text
end repeat
-- write changes out
set replaced to open for access replaced_html with write permission
set eof replaced to 0
write (myHTML as text) to replaced
close access replaced
end tell
return
on ruby_replace(sargs)
return do shell script "ruby <<'EOF' - " & sargs & "
#!/usr/bin/ruby
# coding: utf-8
sfind, sreplace, txt = ARGV
print txt.gsub(/#{sfind}/, \"#{sreplace}\")
EOF"
end ruby_replace
Posted on Jul 22, 2016 9:01 AM

