My script converts a website to text and then selects specific parts of the text to be pasted into Numbers for future use. It pastes 3 AppleScript lists which usually populate columns B, C and D, but sometimes I get unexpected results, in that one (or more) of the pasted lists sometimes occupies more than its designated column (ie. it splits across several columns). In the examples below, I have removed the requirement to get the text from the website by coding some of the text directly into a variable within the script to make for easier testing.
These tests require a blank Numbers spreadsheet to be open
A. Working script:
-- Gets the text from the web report ** Not used in this test script **
-- but left here to show the process used.
(*
tell application "Safari"
set theSource to source of document of front window
end tell
-- Converts the Report (Source) to a text variable
set FullRecord to do shell script "/bin/echo " & quoted form of theSource & " | /usr/bin/textutil -stdin -stdout -format html -inputencoding iso-8859-1 -convert txt -encoding UTF-8"
*)
-- Example text from the website
set FullRecord to "The issues of this assessment are:
1
Observation: 2 lights u/s in flats 8-9 and 11. Recommendation: Light fittings to be replaced as necessary. Location: Flats 8-9 and 11. Floor: First
Response not signed off.
2
Observation: Trip hazards on escape routes. Recommendation: Keep routes clear of personal belongings. Location: Throughout the blocks. Floor: First
Response not signed off.
"
set ObsList to {}
set RecList to {}
set LocList to {}
set myLength to count (FullRecord)
set ObsStart to offset of "The issues of this assessment are:" in FullRecord
--display dialog FullRecord
set FullRecord to items ObsStart thru myLength of FullRecord as string
set myLength to count (FullRecord)
set ObsStart to 1 -- start value
repeat while ObsStart is not 0
-- Obs
set ObsStart to offset of "Observation:" in FullRecord
set ObsEnd to offset of "Recommendation:" in FullRecord
set myObs to text (ObsStart + 13) thru (ObsEnd - 2) of FullRecord
-- Rec
set RecStart to offset of "Recommendation:" in FullRecord
set RecEnd to offset of "Location:" in FullRecord
set MyRec to text (RecStart + 16) thru (RecEnd - 2) of FullRecord
-- Loc
set LocStart to offset of "Location:" in FullRecord
set LocEnd to offset of "Floor:" in FullRecord
set myLoc to text (LocStart + 10) thru (LocEnd - 2) of FullRecord
copy myObs & return to end of ObsList
copy MyRec & return to end of RecList
copy myLoc & return to end of LocList
set FullRecord to items (LocEnd + 1) thru myLength of FullRecord as string
set myLength to count (FullRecord)
end repeat
set ct to count of ObsList
set FinalObsList to items 1 thru ((ct) - 1) of ObsList
set FinalRecList to items 1 thru ((ct) - 1) of RecList
set FinalLocList to items 1 thru ((ct) - 1) of LocList
tell application "Numbers"
activate
-- Obs
set the clipboard to FinalObsList as text
tell document 1 to tell sheet 1 to tell table 1 to set selection range to range "B1"
my selectMenu("Numbers", 4, 6) -- Paste
delay 1
-- Rec
set the clipboard to FinalRecList as text
tell document 1 to tell sheet 1 to tell table 1 to set selection range to range "C1"
my selectMenu("Numbers", 4, 6) -- Paste
delay 1
-- Locations
set the clipboard to FinalLocList as text
--set the clipboard to FinalRecList as text
tell document 1 to tell sheet 1 to tell table 1 to set selection range to range "D1"
my selectMenu("Numbers", 4, 6) -- Paste
end tell -- Numbers
--=====
on activateGUIscripting()
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure that GUI scripting is active *)
end tell
end activateGUIscripting
--=====
-- Handler triggering the menu item mi of the menu mt of the application theApp.
on selectMenu(theApp, mt, mi)
tell application theApp
activate
tell application "System Events" to tell process theApp to tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell -- application theApp
end selectMenu
--=====
B. Failing script: (Same script but with a 3rd Observation added).
-- Gets the text from the web report ** Not used in this test script **
-- but left here to show the process used.
(*
tell application "Safari"
set theSource to source of document of front window
end tell
-- Converts the Report (Source) to a text variable
set FullRecord to do shell script "/bin/echo " & quoted form of theSource & " | /usr/bin/textutil -stdin -stdout -format html -inputencoding iso-8859-1 -convert txt -encoding UTF-8"
*)
-- Example text from the website *** 3rd Observation added ***
set FullRecord to "The issues of this assessment are:
1
Observation: 2 lights u/s in flats 8-9 and 11. Recommendation: Light fittings to be replaced as necessary. Location: Flats 8-9 and 11. Floor: First
Response not signed off.
2
Observation: Trip hazards on escape routes. Recommendation: Keep routes clear of personal belongings. Location: Throughout the blocks. Floor: First
Response not signed off.
3
Observation: No damp protection apparent in communal areas. Recommendation: Consider fitting integral protection to doors provided in common areas. Location: Throughout. Floor: First
Response not signed off.
"
set ObsList to {}
set RecList to {}
set LocList to {}
set myLength to count (FullRecord)
set ObsStart to offset of "The issues of this assessment are:" in FullRecord
--display dialog FullRecord
set FullRecord to items ObsStart thru myLength of FullRecord as string
set myLength to count (FullRecord)
set ObsStart to 1 -- start value
repeat while ObsStart is not 0
-- Obs
set ObsStart to offset of "Observation:" in FullRecord
set ObsEnd to offset of "Recommendation:" in FullRecord
set myObs to text (ObsStart + 13) thru (ObsEnd - 2) of FullRecord
-- Rec
set RecStart to offset of "Recommendation:" in FullRecord
set RecEnd to offset of "Location:" in FullRecord
set MyRec to text (RecStart + 16) thru (RecEnd - 2) of FullRecord
-- Loc
set LocStart to offset of "Location:" in FullRecord
set LocEnd to offset of "Floor:" in FullRecord
set myLoc to text (LocStart + 10) thru (LocEnd - 2) of FullRecord
copy myObs & return to end of ObsList
copy MyRec & return to end of RecList
copy myLoc & return to end of LocList
set FullRecord to items (LocEnd + 1) thru myLength of FullRecord as string
set myLength to count (FullRecord)
end repeat
set ct to count of ObsList
set FinalObsList to items 1 thru ((ct) - 1) of ObsList
set FinalRecList to items 1 thru ((ct) - 1) of RecList
set FinalLocList to items 1 thru ((ct) - 1) of LocList
tell application "Numbers"
activate
-- Obs
set the clipboard to FinalObsList as text
tell document 1 to tell sheet 1 to tell table 1 to set selection range to range "B1"
my selectMenu("Numbers", 4, 6) -- Paste
delay 1
-- Rec
set the clipboard to FinalRecList as text
tell document 1 to tell sheet 1 to tell table 1 to set selection range to range "C1"
my selectMenu("Numbers", 4, 6) -- Paste
delay 1
-- Locations
set the clipboard to FinalLocList as text
--set the clipboard to FinalRecList as text
tell document 1 to tell sheet 1 to tell table 1 to set selection range to range "D1"
my selectMenu("Numbers", 4, 6) -- Paste
end tell -- Numbers
--=====
on activateGUIscripting()
tell application "System Events"
if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure that GUI scripting is active *)
end tell
end activateGUIscripting
--=====
-- Handler triggering the menu item mi of the menu mt of the application theApp.
on selectMenu(theApp, mt, mi)
tell application theApp
activate
tell application "System Events" to tell process theApp to tell menu bar 1 to tell menu bar item mt to tell menu 1 to click menu item mi
end tell -- application theApp
end selectMenu
--=====
With 2 Observations, each comment is in its own cell on the spreadsheet but adding the third throws the whole thing out. I can not work out why this is happening. Can anyone help with this please?
