QLab AppleScript: ExportQLab5

Caution

This Script is being developed now. And I released beta version.
Maybe there are many bugs. If you find our these bugs, would you report me these.

About

Export QLab Workspace to Microsoft Excel File(.xlsx). This script for QLab5

Export parameters:

• Broken flag
• Q type (e.g. Audio, fade, MIDI,..)
• Cue number
• Cue name
• Target
• PreWait
• Action
• PostWait
• Continue Mode
• armed

Use applescript(in part javascript), get these parameter "Cue Data" and to write Excel File.

Index

• Usage
• AppleScript
• Explain Handler
• License
• Enviroment

Read Me!

Apologies

I'm non-native English speaker. These English text might be incorrect.
I would like to apologize in advance for the grammar, spelling, expression,...

Attention

This script is created by non professional.
I have checked the operation, but I do not guarantee all operations.
I am not responsible for any trouble that may occur.

Usage

You copy the script below to Script Editor.app and save as "application".

How to Save as application format

1. Open "Application/Utilities/Script Editor.app".
2. Copy and paset this script to script editor.
3. Save as application format to any folder (i.e Desktop folder or Applications folder): [File]->[Export] and select file format {Application}.
   

Use this script as application

1.Double click that saved application.
2.Select export workspace.

• Current Workspace: Export front workspace of QLab.
• Choose folder: Open Finder and choose target workspace.
• cancel: Quit this application.

3.Confirm Dialog.

• Yes: Execute exporting program.
• No: If you select Current Workspace quit ONLY this script, or if Choose folder quit this script AND close that workspace.

4. Start data export.

• GetData_ProgressBar: Process get cue data, making export data format.

• ToWrite_ProgressBar: Process to wite excel-file(.xlsx).

5.Completed.

AppleScript

This script is released under the MIT License. See License Chapter.

Release note

• 22 Dec 2023
  First version Script for QLab5.
• 12 Mar 2024
  Support all colours. And updata "RGBList"Handler.
• 20 Mar 2024
  Support Applescript version and macOS.
  Applescript version 2.4 -> 2.7
  macOS High Sierra(10.13) or later

ExportQLab5.scpt

(*
Copyright (c) 2024 Kooky Mice

This software is released under the MIT License.
http://opensource.org/licenses/mit-license.php
*)

use AppleScript version "2.7" -- macOS High Sierra(10.13) or later
use scripting additions


on RGBList(colour_map, colour_name)
	set jsScript to " var dict =" & colour_map & ";
	dict['" & colour_name & "'];"

	set DictValue to run script jsScript in "JavaScript"


	return run script ("{" & DictValue & "}")
end RGBList


on ProgressBar(message_)
	tell me to activate
	set progress description to message_
	set progress total steps to -1
	return
end ProgressBar


on GetCueData(CueList_id)
	set aListCueData to {}
	tell application id "com.figure53.QLab.5" to tell front workspace
		set AllCue to every cue of CueList_id
		repeat with aCUE in AllCue
			set aCueData to {}
			if (broken of aCUE) = true then
				set the end of aCueData to "X"
			else
				set the end of aCueData to ""
			end if
			set the end of aCueData to q type of aCUE
			set the end of aCueData to q number of aCUE
			set the end of aCueData to q list name of aCUE
			set id_ to cue target of aCUE
			if id_ /= missing value then
				if q number of id_ = "" then
					set the end of aCueData to q list name of id_
				else
					set the end of aCueData to q number of id_
				end if
			else
				set the end of aCueData to ""
			end if
			set the end of aCueData to (pre wait of aCUE) / 86400
			set the end of aCueData to (duration of aCUE) / 86400
			set the end of aCueData to (post wait of aCUE) / 86400

			set ContinueMode to (continue mode of aCUE)
			if ContinueMode = do_not_continue then
				set the end of aCueData to ""
			else if ContinueMode = auto_continue then
				set the end of aCueData to "auto continue"
			else if ContinueMode = auto_follow then
				set the end of aCueData to "auto follow"
				set item 8 of aCueData to item 7 of aCueData
			end if

			set the end of aCueData to q color of aCUE
			set the end of aListCueData to aCueData

			if (q type of aCUE) = "Group" then
				set GroupMode to mode of aCUE
				if GroupMode = cue_list then
					set theGroupMode to "Cue List"
				else if GroupMode = timeline then
					set theGroupMode to "Timeline"
				else if GroupMode = playlist then
					set theGroupMode to "Playlist"
				else if GroupMode = start_first_and_enter then
					set theGroupMode to "Start first and enter"
				else if GroupMode = start_first then
					set theGroupMode to "Start first"
				else if theGroupMode = start_random then
					set theGroupMode to "Start_random"
				end if

				set item 4 of aCueData to (item 4 of aCueData & " [Mode]: " & theGroupMode)
				set GroupCueData to GetCueData(aCUE) of me
				set the end of aCueData to (count of GroupCueData)
				set aListCueData to aListCueData & GroupCueData
			else
				set the end of aCueData to ""
			end if

			if (armed of aCUE) = true then
				set the end of aCueData to "1"
			else if (armed of aCUE) = false then
				set the end of aCueData to "0"
			end if

		end repeat
	end tell
	return aListCueData
end GetCueData


on ExportWorkspaceData(ocflag)
	set QLAB_RED to "'251,54,59'"
	set QLAB_ORANGE to "'255,149,0'"
	set QLAB_YELLOW to "'246,229,26'"
	set QLAB_GREEN to "'0,204,34'"
	set QLAB_CYAN to "'106,226,227'"
	set QLAB_BLUE to "'65,91,217'"
	set QLAB_PURPLE to "'149,0,179'"
	set QLAB_MAGENTA to "'198,0,128'"
	set QLAB_CRIMSON to "'163,3,34'"
	set QLAB_PEACH to "'254,130,83'"
	set QLAB_OLIVE to "'128,128,0'"
	set QLAB_FOREST to "'18,83,26'"
	set QLAB_SKYBLUE to "'135,194,232'"
	set QLAB_MIDNIGHT to "'26,29,128'"
	set QLAB_INDIGO to "'90,84,153'"
	set QLAB_LAVENDER to "'215,142,228'"
	set QLAB_PLUM to "'59,0,63'"
	set QLAB_BERRY to "'110,24,77'"
	set QLAB_HOTPINK to "'254,0,128'"
	set QLAB_GRAY to "'143,138,153'"
	set QLAB_DEF to "''"
	set QlabColour to "{'red':" & QLAB_RED & ",'orange':" & QLAB_ORANGE & ",'yellow':" & QLAB_YELLOW & ",'green':" & QLAB_GREEN & ",'cyan':" & QLAB_CYAN & ",'blue':" & QLAB_BLUE & ",'purple':" & QLAB_PURPLE & ",'magenta':" & QLAB_MAGENTA & ",'crimson':" & QLAB_CRIMSON & ",'Peach':" & QLAB_PEACH & ",'olive':" & QLAB_OLIVE & ",'forest':" & QLAB_FOREST & ",'Sky Blue':" & QLAB_SKYBLUE & ",'midnight':" & QLAB_MIDNIGHT & ",'indigo':" & QLAB_INDIGO & ",'Lavender':" & QLAB_LAVENDER & ",'plum':" & QLAB_PLUM & ",'berry':" & QLAB_BERRY & ",'Hot Pink':" & QLAB_HOTPINK & ",'gray':" & QLAB_GRAY & ",'none':" & QLAB_DEF & "}"
	tell application id "com.figure53.QLab.5"
		tell front workspace
			activate
			delay 1
			set theFileName to q number
			set Answer to the button returned of (display dialog "Export this WorkSpace?" & return & "  --> " & theFileName with title "Export!" buttons {"No", "Yes"} default button 2 with icon note)

			if Answer is "No" then
				if ocflag then
					close saving ask
				end if
				return
			end if

			ProgressBar("Get Workspace Cue Data...") of me
			set WorkspaceCueList to every cue list

			set CueListName to {}
			repeat with aCueList in WorkspaceCueList
				set the end of CueListName to (q list name of aCueList)
			end repeat
		end tell
		set AllCueData to {}
		set i to 1
		repeat with aCueList in WorkspaceCueList
			set the end of AllCueData to GetCueData(aCueList) of me
		end repeat
	end tell
	return {AllCueData, theFileName, CueListName, QlabColour}
end ExportWorkspaceData


on ToWriteExcelFile(CueDatas, WorkspaceName, CueNames, ColourMap)
	set LABEL_FONT to "SFPro-Regular"
	set LABEL_FONT_SIZE to 13
	set DATA_FONT to "SFPro-Regular"
	set DATA_FONT_SIZE to 14
	tell application id "com.microsoft.Excel"
		make new workbook

		set NumberOfCueList to count of CueNames

		repeat NumberOfCueList - 1 times
			make new worksheet at the end of active workbook
		end repeat

		set AllSheet to every sheet

		set i to 1
		repeat with aSheet in AllSheet
			tell aSheet
				set BROKENFLAG_RANGE to range "A:A"
				set QTYPE_RANGE to range "B:B"
				set QNUMBER_RANGE to range "C:C"
				set QNAME_RANGE to range "D:D"
				set QTARGET_RANGE to range "E:E"
				set PREWAITE_RANGE to range "F:F"
				set DURATION_RANGE to range "G:G"
				set POSTWAIT_RANGE to range "H:H"
				set COUNTINUEMODE_RANGE to range "I:I"

				set BROKENFLAG_COLUMN_WIDTH to 3.5
				set QTYPE_COLUMN_WIDTH to 9.0
				set TIME_COLUMN_WIDTH to 13.0
				set COUNTINUEMODE_COLUMN_WIDTH to 17.0

				set LABEL_ROW_HEIGHT to 19.0
				set DATA_ROW_HEIGHT to 22.0

				set CueDataLeng to (count of item i of CueDatas) as string

				border around cell "A2" line style dot
				copy range cell "A2" destination range ("A2:I2")
				copy range range ("A2:I2") destination range ("A2:" & "A" & CueDataLeng + 1)

				set CUE_LABEL to range ("A1:I1")
				set row height of CUE_LABEL to LABEL_ROW_HEIGHT
				set font size of font object of CUE_LABEL to LABEL_FONT_SIZE
				set name of font object of CUE_LABEL to LABEL_FONT
				set bold of font object of CUE_LABEL to true
				set color of font object of CUE_LABEL to {0, 0, 0}
				set value of CUE_LABEL to {"", "", "Number", "Q", "Target", "PreWait", "Duration", "PostWait", "Continue"}

				set column width of BROKENFLAG_RANGE to BROKENFLAG_COLUMN_WIDTH
				set horizontal alignment of BROKENFLAG_RANGE to horizontal align center

				set column width of QTYPE_RANGE to QTYPE_COLUMN_WIDTH

				set horizontal alignment of QNUMBER_RANGE to horizontal align center
				set horizontal alignment of QTARGET_RANGE to horizontal align center

				set column width of range "F:H" to TIME_COLUMN_WIDTH
				set horizontal alignment of range "F:H" to horizontal align center

				set column width of COUNTINUEMODE_RANGE to COUNTINUEMODE_COLUMN_WIDTH
				set horizontal alignment of COUNTINUEMODE_RANGE to horizontal align center

				set j to 2
				set row height of range ("A2:" & "I" & CueDataLeng + 1) to DATA_ROW_HEIGHT
				set number format of range ("F2:" & "H" & CueDataLeng + 1) to "mm:ss.00"

				repeat with aCueData in item i of CueDatas

					set DATA_RANGE to range ("A" & j & ":" & "I" & j)
					set color of interior object of range ("B" & j & ":" & "I" & j) to RGBList(ColourMap, item 10 of aCueData) of me

					if (item 12 of aCueData) = "0" then
						set pattern of interior object of DATA_RANGE to pattern light up
					end if

					set value of DATA_RANGE to items 1 thru 9 of aCueData

					if item 6 of aCueData = 0 then
						set color of font object of range ("F" & j & ":" & "F" & j) to {150, 150, 150}
					end if

					if item 7 of aCueData = 0 then
						set color of font object of range ("G" & j & ":" & "G" & j) to {150, 150, 150}
					end if

					if item 8 of aCueData = 0 then
						set color of font object of range ("H" & j & ":" & "H" & j) to {150, 150, 150}
					end if

					if item 2 of aCueData = "Group" then
						set GroupArea to range ("C" & j & ":" & "D" & (j + (item 11 of aCueData)))
						border around (GroupArea) weight border weight thin
					end if

					set name of font object of DATA_RANGE to DATA_FONT
					set font size of font object of DATA_RANGE to DATA_FONT_SIZE

					if value of range ("A" & j) = "X" then
						set color of font object of range ("A" & j) to {255, 0, 0}
					end if

					set j to j + 1
				end repeat

				set tgt to get border range ("A2:I2") which border border top
				set line style of tgt to double
				border around range ("A1:" & "I" & j - 1) weight border weight medium

				autofit column "C:E"

				set name to item i of CueNames

				set i to i + 1

			end tell
		end repeat
	end tell
end ToWriteExcelFile


on ExportQLabWorkspace(choosed)
	try
		tell application id "com.figure53.QLab.5"

			set {ExportCueData, ExportFileName, ExportListName, QlabColour} to ExportWorkspaceData(choosed) of me

			ProgressBar("To Write Excel file...") of me
			ToWriteExcelFile(ExportCueData, ExportFileName, ExportListName, QlabColour) of me

			display alert "Success!" as informational
		end tell

		tell application id "com.microsoft.Excel" to tell front workbook
			delay 0.5
			activate
		end tell

	on error number ErrCode
		display alert "An error!" & return & "Quit application." message "Error number: " & ErrCode as warning
		return
	end try
	delay 1
end ExportQLabWorkspace


on ApplicationService()
	tell application id "com.figure53.QLab.5"
		activate
		set ChooseWorkspace to the button returned of (display dialog "Please Choose Exported Workspace." with title "Export QLab" buttons {"cancel", "Choose Folder", "Current Workspace"} default button 3 with icon note)

		if ChooseWorkspace is "Choose Folder" then
			tell application "Finder" to set loc to target of window 1 as alias
			try
				set ExportFile to choose file with prompt "Please choose Qlab File." of type {"qlab4", "qlab5"} default location loc
				open ExportFile
				set opend to true
			on error
				return
			end try

		else if ChooseWorkspace is "current workspace" then
			set opend to false
			if (count of (every workspace)) is 0 then
				set Answer to the button returned of (display dialog "None current workspace! Quit ExportQlab?" with title "File does not exist!" buttons {"Quit", "Retry"} default button 2 with icon 0)
				if Answer is "Retry" then
					ApplicationService() of me
					return
				else if Answer is "quit" then
					return
				end if
			end if

		else if ChooseWorkspace is "cancel" then
			return
		end if
		ExportQLabWorkspace(opend) of me
	end tell
end ApplicationService



ApplicationService()

Explain Handler

This Script contain below Handler.

• RGBList
• ProgressBar
• GetCueData
• ExportWorkspaceData
• ToWriteExcelFile
• ExportWorkspace
• ApplicationService

RGBList

Using "JavaScript", access to record by key in string . Although that return value is "text format", Excel colour code is "list of number" format.
Therefore reshape text format to list of number.

That format: {R, G, B} = {number, number, number}

RGBList Code

on RGBList(colour_map, colour_name)
	set jsScript to " var dict =" & colour_map & ";
	dict['" & colour_name & "'];"

	set DictValue to run script jsScript in "JavaScript"


	return run script ("{" & DictValue & "}")
end RGBList

ProgressBar

Display progress bar dialog with message.
That message is specified by text as arguments.
Progress total steps to -1: That total steps defined "-1", that mean undefined total steps. So get return that prosess is close.

ProgressBar

on ProgressBar(message_)
	tell me to activate
	set progress description to message_
	set progress total steps to -1
	return
end ProgressBar

GetCueData

This handler get property/value of each cue from QLab workspace and return Multiple List form.
That data is broken, cue type, cue number, name, target, pre wait, duration, post wait, continue mode, colour name, Disarmed.
So aListCueData is 2-dimensional form data.
I explain below part how to get those parameters.

Arg:"CueList_id"
In Qlab application, all data that each cue, each cue list, even workspec are managed by uniqueID.
When we want to access any data, use this id.

Data format:
{{broken flag-#1, Q type-#1, Q number-#1, Name-#1, Target-#1, Pre Wait-#1, Duration-#1, Post Wait-#1, Continue Mode-#1, colour-#1, Count of GroupMember-#1, Armed Flag-#1}, ....,
 {broken flag-#n, Q type-#n, Q number-#n, Name-#n, Target-#n, Pre Wait-#n, Duration-#n, Post Wait-#n, Continue Mode-#n, colour-#n, Count of GroupMember-#n, Armed Flag-#n}}

GetCueData

on GetCueData(CueList_id)
	set aListCueData to {}
	tell application id "com.figure53.QLab.5" to tell front workspace
		set AllCue to every cue of CueList_id
		repeat with aCUE in AllCue
			set aCueData to {}
			if (broken of aCUE) = true then
				set the end of aCueData to "X"
			else
				set the end of aCueData to ""
			end if
			set the end of aCueData to q type of aCUE
			set the end of aCueData to q number of aCUE
			set the end of aCueData to q list name of aCUE
			set id_ to cue target of aCUE
			if id_ /= missing value then
				if q number of id_ = "" then
					set the end of aCueData to q list name of id_
				else
					set the end of aCueData to q number of id_
				end if
			else
				set the end of aCueData to ""
			end if
			set the end of aCueData to (pre wait of aCUE) / 86400
			set the end of aCueData to (duration of aCUE) / 86400
			set the end of aCueData to (post wait of aCUE) / 86400

			set ContinueMode to (continue mode of aCUE)
			if ContinueMode = do_not_continue then
				set the end of aCueData to ""
			else if ContinueMode = auto_continue then
				set the end of aCueData to "auto continue"
			else if ContinueMode = auto_follow then
				set the end of aCueData to "auto follow"
				set item 8 of aCueData to item 7 of aCueData
			end if

			set the end of aCueData to q color of aCUE
			set the end of aListCueData to aCueData

			if (q type of aCUE) = "Group" then
				set GroupMode to mode of aCUE
				if GroupMode = cue_list then
					set theGroupMode to "Cue List"
				else if GroupMode = timeline then
					set theGroupMode to "Timeline"
				else if GroupMode = playlist then
					set theGroupMode to "Playlist"
				else if GroupMode = start_first_and_enter then
					set theGroupMode to "Start first and enter"
				else if GroupMode = start_first then
					set theGroupMode to "Start first"
				else if theGroupMode = start_random then
					set theGroupMode to "Start_random"
				end if

				set item 4 of aCueData to (item 4 of aCueData & " [Mode]: " & theGroupMode)
				set GroupCueData to GetCueData(aCUE) of me
				set the end of aCueData to (count of GroupCueData)
				set aListCueData to aListCueData & GroupCueData
			else
				set the end of aCueData to ""
			end if

			if (armed of aCUE) = true then
				set the end of aCueData to "1"
			else if (armed of aCUE) = false then
				set the end of aCueData to "0"
			end if

		end repeat
	end tell
	return aListCueData
end GetCueData

broken

This parameter is boolean. When that cue has warning return true.
When that is true set "X" to broken flag, is "false" set ""(blank).

q type

That name is kind of each cue, e.g) Audio, Fade, MIDI,...etc

q number

Each cue number set any number/text or none.

q name

The name of the cue. It's not unique

Cue Target

If a cue doesn't have target then return "missing value", has target then return cue number or cue name.

Pre Wait, Action, Post wait

Get each time value by seconds and convert to serial value.
In "Microsoft Excel", the serial value of time is 24 hours as "1", and that time is expressed as percentage of "1".
That serial value of time is value[sec] divided by 86400[sec].

86400 = 60[sec] * 60[min] * 24[h]

Group Cue

Group cue contain some cues (it's "Children") and, has been set a group mode.
Get each cue data and group mode. That Group mode append to "cue name" cell in Excel.
In QLab5, there are five modes. Added these modes "cue list mode" and "playlist mode" from QLab4.

Group mode

• cue_list: The group is a cue list.
• timeline: Timeline - Start all children simultaneously.
• playlist: Playlist - One cue at a time.
• start_first_and_enter: Start first child and enter into group.
• start_first : Start first child and go to next cue.
• start_random: Start a random child cue and then go to the next cue.

continue mode

Get each cue continue mode, "Do not continue", "Auto-continue", "Auto-follow".
If "Do not continue" mode set ""(Blank), otherwise set these mode as text this data.
When "Auto-follow" mode sat any cue, that cue's "Post Wait" time　has been set to "Duration" time.

Continue mode

• Do not continue: Do not automatically continue to the next cue.
• Auto-continue: Automatically continue to the next cue after completing the post wait.
• Auto-follow: Automatically continue to the next cue after completing the action of the cue.

Colour name

Get each cue colour, return colour name. These colours set by colour palette on inspector tab. QLab 5 has more colours we can set to, and this script support those all colours.

Armed

Any cue's "Armed" checkbox uncheking so disarmed(Do not run) state these cues, then these cues row are added hatching. That data in case "Armed" set '1', "Disarmed" set '0'.
Disarmed state cue row is added hatching pattern in exported Excel-File, too.

ExportWorkspaceData

This handler creat export data to hand on Excel.That data return by GetCueData(CueList_id) and the format is as noted that part.
GetCueData() export all cue data of a cue List and ExportQlabWorkSpace() is all cue data of whole workspace (all Cue List).
So AllCueData is 3-dimensional form data.

Arg:"ocflag"
This argument is flag that target workspace is open or not, given by "ApplicationService"Handler. This data formn is boolean.

ExportWorkspaceData

on ExportWorkspaceData(ocflag)
	set QLAB_RED to "'251,54,59'"
	set QLAB_ORANGE to "'255,149,0'"
	set QLAB_YELLOW to "'246,229,26'"
	set QLAB_GREEN to "'0,204,34'"
	set QLAB_CYAN to "'106,226,227'"
	set QLAB_BLUE to "'65,91,217'"
	set QLAB_PURPLE to "'149,0,179'"
	set QLAB_MAGENTA to "'198,0,128'"
	set QLAB_CRIMSON to "'163,3,34'"
	set QLAB_PEACH to "'254,130,83'"
	set QLAB_OLIVE to "'128,128,0'"
	set QLAB_FOREST to "'18,83,26'"
	set QLAB_SKYBLUE to "'135,194,232'"
	set QLAB_MIDNIGHT to "'26,29,128'"
	set QLAB_INDIGO to "'90,84,153'"
	set QLAB_LAVENDER to "'215,142,228'"
	set QLAB_PLUM to "'59,0,63'"
	set QLAB_BERRY to "'110,24,77'"
	set QLAB_HOTPINK to "'254,0,128'"
	set QLAB_GRAY to "'143,138,153'"
	set QLAB_DEF to "''"
	set QlabColour to "{'red':" & QLAB_RED & ",'orange':" & QLAB_ORANGE & ",'yellow':" & QLAB_YELLOW & ",'green':" & QLAB_GREEN & ",'cyan':" & QLAB_CYAN & ",'blue':" & QLAB_BLUE & ",'purple':" & QLAB_PURPLE & ",'magenta':" & QLAB_MAGENTA & ",'crimson':" & QLAB_CRIMSON & ",'Peach':" & QLAB_PEACH & ",'olive':" & QLAB_OLIVE & ",'forest':" & QLAB_FOREST & ",'Sky Blue':" & QLAB_SKYBLUE & ",'midnight':" & QLAB_MIDNIGHT & ",'indigo':" & QLAB_INDIGO & ",'Lavender':" & QLAB_LAVENDER & ",'plum':" & QLAB_PLUM & ",'berry':" & QLAB_BERRY & ",'Hot Pink':" & QLAB_HOTPINK & ",'gray':" & QLAB_GRAY & ",'none':" & QLAB_DEF & "}"
	tell application id "com.figure53.QLab.5"
		tell front workspace
			activate
			delay 1
			set theFileName to q number
			set Answer to the button returned of (display dialog "Export this WorkSpace?" & return & "  --> " & theFileName with title "Export!" buttons {"No", "Yes"} default button 2 with icon note)

			if Answer is "No" then
				if ocflag then
					close saving ask
				end if
				return
			end if

			ProgressBar("Get Workspace Cue Data...") of me
			set WorkspaceCueList to every cue list

			set CueListName to {}
			repeat with aCueList in WorkspaceCueList
				set the end of CueListName to (q list name of aCueList)
			end repeat
		end tell
		set AllCueData to {}
		set i to 1
		repeat with aCueList in WorkspaceCueList
			set the end of AllCueData to GetCueData(aCueList) of me
		end repeat
	end tell
	return {AllCueData, theFileName, CueListName, QlabColour}
end ExportWorkspaceData

Define Colour

Defined colour use in QLab. Pick up colour from:

The colour code is sRGB. Pick up from there, with Digital colour Meter.app

Verify Dialog -choice target file-

This dialog verify target file to export.
Yes: Start to export that workspace.
No: Quit this script.

ToWriteExcelFile

This handler write qlab data into Excel's new workbook.
And styling sheet: cell size, colour, font.

Creat new workbook and sheets.

This process creat a new workbook and create number of Cuelist new sheets.

Styling each sheet.

• Label

(Break flag)	(Q type)	Cue number	Cue name	Target	PreWait	Duration	PostWait	Continue
X /		Number	Q	Target	PreWait	Action	PostWait	Continue

• Font

  Font	name	Size	Style	Colour
  Label	SFPro-Regular	13	Bold	Black
  Data	SFPro-Regular	14	Regular	Black

When the time value(PreWait, Duration, PostWait) is "00:00:00", that font colour set to Gray({R, G, B} = {160, 160, 160}).

• Cell Size
  Height is set by Pixel, but Width is set by character of number of default font.
  The default font is sat by Excel Preferences. Excel/Preferences/General/Setting.

  • Height
    • Label = 19.0
    • Data = 22.0
  • Width [SF Pro, Size=12]

    	Break flag	Q type	Cue number	Cue name	Target	PreWait	Duration	PostWait	Continue
    Width	3.5	9.0	autofit	autofit	autofit	13.0	13.0	13.0	13.0

ToWriteExcelFile

on ToWriteExcelFile(CueDatas, WorkspaceName, CueNames, ColourMap)
	set LABEL_FONT to "SFPro-Regular"
	set LABEL_FONT_SIZE to 13
	set DATA_FONT to "SFPro-Regular"
	set DATA_FONT_SIZE to 14
	tell application id "com.microsoft.Excel"
		make new workbook

		set NumberOfCueList to count of CueNames

		repeat NumberOfCueList - 1 times
			make new worksheet at the end of active workbook
		end repeat

		set AllSheet to every sheet

		set i to 1
		repeat with aSheet in AllSheet
			tell aSheet
				set BROKENFLAG_RANGE to range "A:A"
				set QTYPE_RANGE to range "B:B"
				set QNUMBER_RANGE to range "C:C"
				set QNAME_RANGE to range "D:D"
				set QTARGET_RANGE to range "E:E"
				set PREWAITE_RANGE to range "F:F"
				set DURATION_RANGE to range "G:G"
				set POSTWAIT_RANGE to range "H:H"
				set COUNTINUEMODE_RANGE to range "I:I"

				set BROKENFLAG_COLUMN_WIDTH to 3.5
				set QTYPE_COLUMN_WIDTH to 9.0
				set TIME_COLUMN_WIDTH to 13.0
				set COUNTINUEMODE_COLUMN_WIDTH to 17.0

				set LABEL_ROW_HEIGHT to 19.0
				set DATA_ROW_HEIGHT to 22.0

				set CueDataLeng to (count of item i of CueDatas) as string

				border around cell "A2" line style dot
				copy range cell "A2" destination range ("A2:I2")
				copy range range ("A2:I2") destination range ("A2:" & "A" & CueDataLeng + 1)

				set CUE_LABEL to range ("A1:I1")
				set row height of CUE_LABEL to LABEL_ROW_HEIGHT
				set font size of font object of CUE_LABEL to LABEL_FONT_SIZE
				set name of font object of CUE_LABEL to LABEL_FONT
				set bold of font object of CUE_LABEL to true
				set color of font object of CUE_LABEL to {0, 0, 0}
				set value of CUE_LABEL to {"", "", "Number", "Q", "Target", "PreWait", "Duration", "PostWait", "Continue"}

				set column width of BROKENFLAG_RANGE to BROKENFLAG_COLUMN_WIDTH
				set horizontal alignment of BROKENFLAG_RANGE to horizontal align center

				set column width of QTYPE_RANGE to QTYPE_COLUMN_WIDTH

				set horizontal alignment of QNUMBER_RANGE to horizontal align center
				set horizontal alignment of QTARGET_RANGE to horizontal align center

				set column width of range "F:H" to TIME_COLUMN_WIDTH
				set horizontal alignment of range "F:H" to horizontal align center

				set column width of COUNTINUEMODE_RANGE to COUNTINUEMODE_COLUMN_WIDTH
				set horizontal alignment of COUNTINUEMODE_RANGE to horizontal align center

				set j to 2
				set row height of range ("A2:" & "I" & CueDataLeng + 1) to DATA_ROW_HEIGHT
				set number format of range ("F2:" & "H" & CueDataLeng + 1) to "mm:ss.00"

				repeat with aCueData in item i of CueDatas

					set DATA_RANGE to range ("A" & j & ":" & "I" & j)
					set color of interior object of range ("B" & j & ":" & "I" & j) to RGBList(ColourMap, item 10 of aCueData) of me

					if (item 12 of aCueData) = "0" then
						set pattern of interior object of DATA_RANGE to pattern light up
					end if

					set value of DATA_RANGE to items 1 thru 9 of aCueData

					if item 6 of aCueData = 0 then
						set color of font object of range ("F" & j & ":" & "F" & j) to {150, 150, 150}
					end if

					if item 7 of aCueData = 0 then
						set color of font object of range ("G" & j & ":" & "G" & j) to {150, 150, 150}
					end if

					if item 8 of aCueData = 0 then
						set color of font object of range ("H" & j & ":" & "H" & j) to {150, 150, 150}
					end if

					if item 2 of aCueData = "Group" then
						set GroupArea to range ("C" & j & ":" & "D" & (j + (item 11 of aCueData)))
						border around (GroupArea) weight border weight thin
					end if

					set name of font object of DATA_RANGE to DATA_FONT
					set font size of font object of DATA_RANGE to DATA_FONT_SIZE

					if value of range ("A" & j) = "X" then
						set color of font object of range ("A" & j) to {255, 0, 0}
					end if

					set j to j + 1
				end repeat

				set tgt to get border range ("A2:I2") which border border top
				set line style of tgt to double
				border around range ("A1:" & "I" & j - 1) weight border weight medium

				autofit column "C:E"

				set name to item i of CueNames

				set i to i + 1

			end tell
		end repeat
	end tell
end ToWriteExcelFile

ExportQLabWorkspace

This is main module.
That call ExportWorkspaceData()handler and ToWriteExcelFile()handler to run exporting operation.
These using try...on error..end try statement. If "try" block raises an error, the "on error" block execute quit to quit application. And get error number, disply error message and that number.
Error numbers and message refer to below site:

https://developer.apple.com/library/archive/documentation/AppleScript/Conceptual/AppleScriptLangGuide/reference/ASLR_error_codes.html

ExportWorkspace

on ExportQLabWorkspace(choosed)
	try
		tell application id "com.figure53.QLab.5"

			set {ExportCueData, ExportFileName, ExportListName, QlabColour} to ExportWorkspaceData(choosed) of me

			ProgressBar("To Write Excel file...") of me
			ToWriteExcelFile(ExportCueData, ExportFileName, ExportListName, QlabColour) of me

			display alert "Success!" as informational
		end tell

		tell application id "com.microsoft.Excel" to tell front workbook
			delay 0.5
			activate
		end tell

	on error number ErrCode
		display alert "An error!" & return & "Quit application." message "Error number: " & ErrCode as warning
		return
	end try
	delay 1
end ExportQLabWorkspace

Error Dialog

When try...on error..end try statement get error and error number, display this dialog.

ApplicationService

This handler is application controller.
That provide dialogs and control application flow.

Dialog

ChooseWorkspace
This dialog select export target workspace.

• Current Workspace: Export front workspace of QLab.
• Choose frolder: Open Finder and choose target workspace.
• cancel: Quit this application.

If selected "Current Workspace" but not found current workspace (i.e None Workspace opend) then open "Quit" or "Retry" dialog.

• Retry: Display "ChooseWorkspece" dialig again.
• Quit: Quit this application.

ApplicationService

  on ApplicationService()
  	tell application id "com.figure53.QLab.5"
  		activate
  		set ChooseWorkspace to the button returned of (display dialog "Please Choose Exported Workspace." with title "Export QLab" buttons {"cancel", "Choose Folder", "Current Workspace"} default button 3 with icon note)

  		if ChooseWorkspace is "Choose Folder" then
  			tell application "Finder" to set loc to target of window 1 as alias
  			try
  				set ExportFile to choose file with prompt "Please choose Qlab File." of type {"qlab4", "qlab5"} default location loc
  				open ExportFile
  				set opend to true
  			on error
  				return
  			end try

  		else if ChooseWorkspace is "current workspace" then
  			set opend to false
  			if (count of (every workspace)) is 0 then
  				set Answer to the button returned of (display dialog "None current workspace! Quit ExportQlab?" with title "File does not exist!" buttons {"Quit", "Retry"} default button 2 with icon 0)
  				if Answer is "Retry" then
  					ApplicationService() of me
  					return
  				else if Answer is "quit" then
  					return
  				end if
  			end if

  		else if ChooseWorkspace is "cancel" then
  			return
  		end if
  		ExportQLabWorkspace(opend) of me
  	end tell
  end ApplicationService

Export Sample

Coming Soon!

We writing now...

License

This script is released under the MIT License.

Copyright 2024 Kooky Mice

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Enviroment

Support

• macOS 10.13(High Sierra) or Later
• QLab5

HardWare

MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)
Processor: 2.3GHz Quad-Core Intel Core i7
Memory: 16GB 3733MHz LPDDR4X

Software

macOS
macOS Monterey
version 12.7.2

QLab5
Version: 5.3.6

Script Editor
Version: 2.11
AppleScript: 2.7 Support 10.13 or later