QLab AppleScript: ExportQLab4

# About

**Export QLab Workspace to Microsoft Excel File(.xlsx).**
Export Sample ==> [Export Sample](#export-sample)

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](#usage)
+ [AppleScript](#applescript)
+ [Explain Handler](#explain-handler)
+ [License](#license)


:::note alert
This Script is for Qlab4.
I don't check operation in QLab5.
:::

**QLab5**
https://zenn.dev/kooky_mice/articles/4883fe99699c47


**Japanese**

https://qiita.com/kooky_mice/private/f5363fa7a84be6a36bec


# Read Me!

### <font color="Red"> Apologies  </font>
:::note warn
I'm non-native English speaker. These English text might be incorrect.
I would like to apologize in advance for the grammar, spelling, expression,...
:::

### <font color="Red"> Attention  </font>
:::note warn
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}.
![SaveAs_App.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/bb313c8c-8ac7-8eb3-1097-8dc37b416bd1.png)


### Use this script as application
<ol>
<li>Double click that saved application.</li>

<li>Select export workspace.
    <ul>
    <li><b>Current Workspace</b>: Export front workspace of QLab.</li>
    <li><b>Choose folder</b>: Open Finder and choose target workspace.</li>
    <li><b>cancel</b>: Quit this application.
    </ul>
</li>
<img width=500 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/3fb24577-9256-753b-f646-63e1a1c00b6d.png" alt="Choose_Target_File.png"></img>

<li>Confirm Dialog.
    <ul>
    <li><b>Yes</b>: Execute exporting program.</li>
    <li><b>No</b>:If you select <b><i>Current Workspace</i></b> quit ONLY this script, 
or if <b><i>Choose folder</i></b> quit this script AND close that workspace.
    </ul>
</li>
<img width=400 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/12508b0a-8688-fd66-a0de-1eb4192a5703.png" alt="Confirm_Dialog.png">

<li>Start data export.
    <ul>
    <li><b>GetData_ProgressBar:</b> Process get cue data, making export data format.</li><img width=400 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/a67817e5-8475-c306-6565-b61bea853dfb.png" alt="GetData_ProgressBar.png">
    <li><b>ToWrite_ProgressBar:</b> Process to wite excel-file(.xlsx).</li><img width=400 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/e6aae36c-eebc-3534-254a-7a24cffac88b.png" alt="ToWrite_ProgressBar.png">
    </ul>
</li>


<li>Completed. </li>
<img width=400 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/c6fa2562-db89-9c56-3c87-188647523a43.png" alt="Succes Diagog.png">
</ol>

# AppleScript

**This script is released under the MIT License. See [License Chapter](#license).**
<details>
  <summary><b>Release Note</b></summary>
    <ul>
    <li><b>2023/12/10:</b>[Change]To specify application by Application ID.</li>
    <li><b>2023/5/30:</b>[add]Disarmed exporte.</li>
    <li><b>2023/2/19:</b>First version.</li>
    </ul>
</details>

```applescript:ExportQLab.scpt
(*
Copyright (c) 2022 Kooky Mice

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

use AppleScript version "2.4" -- Yosemite (10.10) 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.4" 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 = fire_first_enter_group then
					set theGroupMode to "Start first child and enter into group."
				else if GroupMode = fire_first_go_to_next_cue then
					set theGroupMode to "Start first child and go to next cue."
				else if GroupMode = fire_random then
					set theGroupMode to "Start a random child cue and then go to the next cue."
				else if GroupMode = timeline then
					set theGroupMode to "Timeline - Start all children simultaneously."
				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 "'237, 72, 61'"
	set QLAB_ORANGE to "'245, 165, 64'"
	set QLAB_GREEN to "'98, 210, 54'"
	set QLAB_BLUE to "'82, 115, 224'"
	set QLAB_PURPLE to "'169, 64, 192'"
	set QLAB_DEF to "''"
	set QlabColour to "{red:" & QLAB_RED & ", orange:" & QLAB_ORANGE & ", green:" & QLAB_GREEN & ", blue:" & QLAB_BLUE & ", purple:" & QLAB_PURPLE & ", none:" & QLAB_DEF & "}"
	
	tell application id "com.figure53.QLab.4"
		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.4"
			
			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.4"
		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()
```

<br>

# Explain Handler
This Script contain below Handler.
 + [RGBList](#rgblist)
 + [ProgressBar](#progressbar)
 + [GetCueData](#getcuedata)
 + [ExportWorkspaceData](#exportworkspacedata)
 + [ToWriteExcelFile](#towriteexcelfile)
 + [ExportWorkspace](#exportworkspace)
 + [ApplicationService](#applicationservice)


## RGBList
Use "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.

:::note warn
That format: *{R, G, B} = {number, number, number}*
:::

<details>
  <summary><b>RGBList</b></summary>
  <div>
  
```applescript
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
```

  </div>
</details>


## 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.

<details>
  <summary><b>ProgressBar</b></summary>
  <div>
  
```applescript
on ProgressBar(message_)
	tell me to activate
	set progress description to message_
	set progress total steps to -1
	return
end ProgressBar
```

  </div>
</details>



## 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.

:::note info
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.
:::

:::note info
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}, ....,
&nbsp;{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*}}

<!--
|Number|1|2|3|4|5|6|7|8|9|10|11|12|
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|**Data**|Broken Flag|Q type|Q number|Name|Target|Pre Wait|Duration|Post Wait|Continue Mode|Colour Label|Count of Group Member|Armed Flag|
-->
:::
<br>

<details>
  <summary><b>GetCueData</b></summary>
    <div>

```applescript
on GetCueData(CueList_id)
	set aListCueData to {}
	tell application id "com.figure53.QLab.4" 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 = fire_first_enter_group then
					set theGroupMode to "Start first child and enter into group."
				else if GroupMode = fire_first_go_to_next_cue then
					set theGroupMode to "Start first child and go to next cue."
				else if GroupMode = fire_random then
					set theGroupMode to "Start a random child cue and then go to the next cue."
				else if GroupMode = timeline then
					set theGroupMode to "Timeline - Start all children simultaneously."
				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
```

  </div>
</details>

### 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***.
<br>

### 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].

:::note warn
*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.

:::note info
***Group mode***
+ **Timeline**: Start all children simultaneously.
+ **fire_first_enter_group** : Start first child and enter into group.
+ **fire_first_go_to_next_cue** : Start first child and go to next cue.
+ **fire_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.

:::note info
+ **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 "Red", "Orange", "Green", "Blue" "Purple" or "none" if colour not set. These colours set by colour palette on inspector tab. 

### 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.

:::note info
Arg:**"ocflag"**
This argument is flag that target workspace is open or not, given by "ApplicationService"Handler. This data formn is boolean.
<!--If you select "current workspace", that flag sat to "False" and DO NOT CLOSE,  however you select "choose workwspace", that sat to "True" and CLOSE that workspace.-->
:::

<details>
  <summary><b>ExportWorkspaceData</b></summary>
    <div>
    
```applescript
on ExportWorkspaceData(ocflag)
	set QLAB_RED to "'237, 72, 61'"
	set QLAB_ORANGE to "'245, 165, 64'"
	set QLAB_GREEN to "'98, 210, 54'"
	set QLAB_BLUE to "'82, 115, 224'"
	set QLAB_PURPLE to "'169, 64, 192'"
	set QLAB_DEF to "''"
	set QlabColour to "{red:" & QLAB_RED & ", orange:" & QLAB_ORANGE & ", green:" & QLAB_GREEN & ", blue:" & QLAB_BLUE & ", purple:" & QLAB_PURPLE & ", none:" & QLAB_DEF & "}"
	
	tell application id "com.figure53.QLab.4"
		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
```

  </div>
</details>

### Define Colour
Defined colour use in QLab. Pick up colour from:

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/94e0bf98-494e-3dfb-c5e1-ead16d395e25.png" alt="ColourLabel_text.png"></img>


### Verify Dialog -choice target file-
This dialog verify target file to export.
**Yes**: Start to export that workspace.
**No**: Quit this script.

<img width=400 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/12508b0a-8688-fd66-a0de-1eb4192a5703.png" alt="Confirm_Dialog.png">



<br>

## 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|

:::note warn
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*.
    + Height
        + Label = 19.0
        + Data = 22.0
    + Width
        ||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|


<details>
  <summary><b>ToWriteExcelFile</b></summary>
  <div>

```applescript
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
```

  </div>
</details>

## 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

<details>
  <summary><b>ExportWorkspace</b></summary>
  <div>
  
  ```applescript
on ExportQLabWorkspace(choosed)
	try
		tell application id "com.figure53.QLab.4"
			
			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
```
  
  </div>
</details>

### Error Dialog
When `try...on error..end try` statement get error and error number, display this dialog.

<img width=350 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/a4745c1a-8ad2-f767-2d94-162646b4023c.png" alt="ErrorDialog.png"></img>


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

### Dialog
**ChooseWorkspace**
This dialog select export target workspace.
<img width=500 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/3fb24577-9256-753b-f646-63e1a1c00b6d.png" alt="Choose_Target_File.png"></img>

  + **Current Workspace**: Export front workspace of QLab.
  + **Choose frolde**r: Open Finder and choose target workspace.
  + **cancel**: Quit this application.
<br>

If selected "Current Workspace" but not found current workspace (i.e None Workspace opend) then open "Quit" or "Retry" dialog.
<img width=500 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/34f87186-e9ce-3e0f-a6ef-e51163a06865.png" alt="Stop_Dialog.png"></img>
  + **Retry**: Display "ChooseWorkspece" dialig again.
  + **Quit**: Quit this application.


<details>
  <summary><b>ApplicationService</b></summary>
  <div>
  
  ```applescript
on ApplicationService()
	tell application id "com.figure53.QLab.4"
		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
  ```
  
  </div>
</details>


# Export Sample

### Basic Workspace
Each Cue List are exported as Excel each sheet.
The "cue's colour" and "disarmed" are this image.

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/42da807b-6ad2-a77d-cb9d-6f60f06fc49c.png" alt="Colour_CueList_Disarmed_sample_img.png"></img>

---

### ContinuMode Workspace

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/4ca4ae2c-48aa-2969-5380-479e8e681c88.png" alt="ContinuMode_img.png"></img>

---

### Include Group Cue
Group mode append to "cue name" field.

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/e71b1edf-2a60-1859-e276-c2ebb1778cc8.png" alt="GroupCue_img.png"></img>

---

### All Cue Type

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/411510/9ecd3d3c-8d94-87b8-5d9c-2dcfb3900ce3.png" alt="AllCueType_img.png"></img>



# License
**This script is released under the MIT License.**


>Copyright 2022 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
### HardWare
**MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)**
Processor: 2.3GHz Quad-Core Intel Core i7
Memory: 16GB 3733MHz LPDDR4X

### Software
**QLab4**
Version: 4.7
License: Pro Audio

**Script Editor**
Version: 2.11
AppleScript: 2.7