Load to Time from spreadsheet

[Paul]

(this is for Dick from Facebook group)

Here is a script which reads data from spreadsheet (open in Numbers), presents the user with a list to choose and then loads the selected cue to time. You probably want to put this on a hot key. The spreadsheet must be open as the front sheet. The format of the spreadsheet is given in the comments at the top of the script; it takes 3 columns: a label, a cue number and a time (in seconds). This is a fairly basic script with minimal error checking - it doesn't handle point cues and times are in seconds, doesn't open the file; so could be enhanced in lots of ways.  Has been minimally tested but doesn't do anything destructive to your workspace.  I'm not sure if MS Excel supports AppleScript, if it does you could probably modify it to read from Excel, but I don't have that to test.  

Hope this is helpful to somebody.

--- presents the user with a list of places to load, data read from spreadsheet open in Numbers
-- set up your spreadsheet with following structure
-- cell A1 contains QLab Workspace name (normally your show name) as sanity check
--  row 2 contains column headings Label, Cue Num, Load Time (thse are not used by this script)
--  row 3 to (max rows) with three columns containing the data
-- column A - a meaningful label which relates to the place in the show (eg song 1 second chorus)
-- column B - the cue number [only whole number; point cues and strings are not supported in this script]
-- coluumn C - the time (in seconds) to load

set sep to tab
tell application id "com.figure53.QLab.5" to tell front workspace
set thiscue to last item of (every cue whose (running is true) and (q type is "Script"))
set AppleScript's text item delimiters to "."
set workspaceName to first text item of ((q number) as string)
-- use the notes of this cue to store the last user choice
set defaultChoice to (notes of thiscue)
end tell

tell application "Numbers" to tell active sheet of front document

--- do a basic sanity against importing garbage by checking Workspace name in cell A1
set checkName to value of cell 1 of row 1 of table 1
if workspaceName is not equal to checkName then
display dialog "CAUTION: The spreadsheet cell A1 does not match the QLab Workspace name. Do you have the right spreadsheet open in Numbers?" buttons {"Cancel", "Continue"} default button {"Cancel"} with title workspaceName & " != " & checkName with icon caution
end if
----

set menuChoices to {}
-- read from spreadsheet getting choices to be pressented to the user
repeat with r from 3 to row count of first table
-- row count always seems to be atleast 22, so check for missing value
set label to value of cell 1 of row r of table 1
if label is not missing value then

-- Spreadsheet / Numbers returns a float; assume whole number cues only, so convert to int
-- Qlab thinks cue "1" and cue "1.0"  are different cues, because cue numbers are actually strings in QLab
-- TODO test cueNum for decimal or string and convert appropiately
try
set cueNum to value of cell 2 of row r of table 1 as integer
on error ErrorMessage
display dialog ErrorMessage with title "Invalid format for Cue Number"
return
end try
set loadTime to value of cell 3 of row r of table 1
--set myString to label & sep & cueNum & sep & loadTime

set end of menuChoices to label & sep & cueNum & sep & loadTime
end if
end repeat
end tell
tell application id "com.figure53.QLab.5" to tell front workspace

--set defaultItem to (first item of menuChoices)
set response to choose from list menuChoices with prompt "choose place to load" with title "Load to Time" default items defaultChoice
if response is false then -- cancel pressed
return
end if
-- set the notes of the this script cue to the most recent choice
set notes of thiscue to response as string
set myArray to every text item of menuChoices
set AppleScript's text item delimiters to sep
set myChoice to every text item of (response as string)

set label to first item of myChoice
-- note cue 3 and cue "3" are (probably) not the same thing!
set cueNum to (item 2 of myChoice) as string
set loadTime to item 3 of myChoice

-- load the cue to time indicated and make it selected
try
load (cue cueNum) time loadTime
display notification "Load cue " & cueNum & " to time  " & loadTime with title label
set selected to (cue cueNum)
on error ErrorMessage
display dialog ErrorMessage & linefeed & "check the format of your cue number in spreadsheet" with title "Error"
end try
end tell