QLabOff Grid
Spreadsheets can be enormously helpful for preparing data for use in QLab workspaces, and also for presenting data from QLab workspaces in a clear, at-a-glance form which does not require spelunking through inspector tabs.
This is the second of three chapters on this subject and looks at how a spreadsheet can provide a tabular view of show data and also be used ‘live’ as a cue list in QLab.
Note: The examples in these tutorials are primarily demonstrations of how QLab can read from and write to spreadsheets. The downloadable examples should be consider proofs of concept, and should not be regarded as show-ready solutions!
The demo in this chapter shows the mutes of a 16-channel mixing console being automated from a spreadsheet cue list that shows all the mute data for all cues in a single view. As all the data manipulation features of a spreadsheet are available, the microphone plot can be edited rapidly.
As a bonus, it demonstrates a play text being ingested and processed to automatically generate a very accurate first draft of a plot of the required microphone activations, speech by speech.
QLab controls the spreadsheet through a cue cart, triggering the activation of the mixer mutes and also displaying the text of the play on a second operator display.
Generating a mute table from a play text
A lightly edited text is opened in TextEdit. The text contains minor modifications to ensure character names at the top of each line of dialogue can be distinguished from other mentions of the characters in stage directions and within dialogue.
The word “MUTE” is treated as a special character name that sets all microphones to MUTE.
A script running in QLab writes the headings to the spreadsheet to match a list of characters in the cue list named MICROPHONE. The cells under these headings will be referred to as “mute cells”, which is to say that they are the indication of whether a given microphone (column) should be muted or not in a given preset or memory (row.)
The script detects the beginning of each speech and, at that point, places any text read since the beginning of the previous in the TEXT column of the spreadsheet, with new lines denoted by a forward slash (/).
It then writes the character name(s) in the CHARACTER column, creates a memory number, and writes it in the MEM column.
It sets all the mute cells in that row to “mute”, indicated by an M on a red cell background and then unmutes the cell(s) matching the character(s) who are speaking (indicated by a blank white cell).
This repeats until the entire play text has been ingested.
Using a spreadsheet as a cue list
At the top of the screen recording is the CONTROL cart with buttons to navigate up and down the spreadsheet, jump to any MEM, and GO on any mute state.
The green marker in the left column indicates the state that will be fired on the next GO, and the blue marker in the MEM column indicates the current mute state.
When a GO is received, the mute state is updated to the mutes shown in that row. The second cue cart below the spreadsheet displays this new mute state, and QLab also outputs the required OSC or MIDI messages to control a hardware or, as in the demo, a software mixer. The dialogue for that speech is displayed on the second monitor (top right of screen recording in the demo) The increment cue is then started automatically, the green and blue indicators increment one cell down, and the cue on that line is read to preload the next mute state, and dialogue display so they will activate immediately when the next GO is received.
Preparing the text
In order for character names, and multiple character names, at the top of each speech to be distinguished from other mentions of the characters, in stage directions and within dialogue, a simple-to-remember and simple-to-implement rule is needed. The rule adopted in this demo is:
The character name(s) at the top of each speech must be single words in a stand-alone paragraph, in upper case, with no punctuation, separated by spaces.
The text used in this tutorial is from the Project Gutenberg open-source edition of Romeo and Juliet.
This is an excerpt of the text as it appears in the download from Project Gutenberg:
Enter Capulet in his gown, and Lady Capulet.
CAPULET.
What noise is this? Give me my long sword, ho!
LADY CAPULET.
A crutch, a crutch! Why call you for a sword?
CAPULET.
My sword, I say! Old Montague is come,
And flourishes his blade in spite of me.
Enter Montague and his Lady Montague.
MONTAGUE.
Thou villain Capulet! Hold me not, let me go.
LADY MONTAGUE.
Thou shalt not stir one foot to seek a foe.
Enter Prince Escalus with Attendants.The character names are already in upper case but have periods (.) after them which will need to be removed. We also need paragraph breaks before and after the character names. Where multiple characters speak, these will have to then be edited to bring them into a single paragraph again manually. Some character names have spaces in them. As we are using spaces to separate multiple characters speaking a single speech, we’ll replace spaces in names with an underscore (_).
This screenshot shows a find and replace action in progress for LADY CAPULET. removing the period, replacing the space within her name with an underscore and placing paragraph breaks either side. We also need to uncheck the ignore case option so that appearances of the character names in the stage directions and within the dialogue are not replaced.
The Ignore Case and Insert Pattern options are a little hard to find as they are in a pop-up menu activated by clicking the triangle to the right of the magnifying glass.
All the character names need to be found and replaced in this way.
Where all microphones should be muted, as they might when the prince and attendants enter, MUTE can be inserted in the play text conforming to the character name rule.
When the replacements are all applied, the text will look like this:
Enter Capulet in his gown, and Lady Capulet.
CAPULET
What noise is this? Give me my long sword, ho!
LADY_CAPULET
A crutch, a crutch! Why call you for a sword?
CAPULET
My sword, I say! Old Montague is come,
And flourishes his blade in spite of me.Enter Montague and his Lady Montague.
MONTAGUE
Thou villain Capulet! Hold me not, let me go.
LADY_MONTAGUE
Thou shalt not stir one foot to seek a foe.
MUTE
Enter Prince Escalus, with Attendants.
After the replacements, the entire text should be manually checked for outliers that have not been formatted correctly, and the text should be saved in a new document, the title of which should indicate that the text is prepared for import.
Spreadsheet setup
Microsoft Excel is a mature software package; the first version was published in 1985. It has an extensive AppleScript dictionary and its own macro language, Visual Basic for Applications (VBA). This makes it ideal for this project. However, the normal operation of the program, its AppleScript dictionary, and its VBA implementation haven’t been kept consistent with each other as the program has evolved over the years, meaning that care and caution are necessary to ensure they play nicely together.
It’s important to note that any alterations to a spreadsheet workbook by AppleScript or VBA do not appear in the undo stack, and in fact making changes via scripting will clear the existing undo stack. This means it is quite easy to erase rows of mute data quickly without the option of undoing that action!
Implementing undo stacks is way beyond the scope of this tutorial so, as a safety feature, this example includes a method to optionally autosave a copy of the spreadsheet every minute, limiting the amount of work that can be lost.
This will result in potentially hundreds of files which will have to be carefully managed by manually deleting those no longer required as necessary. Autosaving can be disabled when the programming is finished (and a backup copy is saved and readily available to revert to.)
In addition to being set up to receive data from QLab as part of a play text import, the spreadsheet can also be manually edited. Text can be entered in the TEXT, CHARACTER, and DIALOGUE cells. Mutes can be toggled by right-clicking in a mute cell or a range of mute cells.
Imported play texts can also be edited. For instance, in this screen recording, it has been decided to leave three microphones live through a section of dialogue to amplify fight reactions.
Included in the downloadable examples is a blank Excel workbook named Mutes 16ch Blank which is set up as follows:
- In the header row: all cells text alignment centered, cells A1 (AutoSave Indicator) and D1:S1 (Characters) text formatted vertically.
- Top row frozen (from the View menu on the Ribbon.)
- All cells bordered.
- Headings switched off (from the View menu on Ribbon.) This is to avoid double-clicking on an entire mute column, which would toggle all the mutes in that column but also extend the active cells in the sheet to a very large row number, which would slow down the operation of the spreadsheet considerably.
The file extension of the blank workbook is .xlsm; the “m” in that extension denotes a workbook that contains macros. The macros can be accessed and edited from the Visual Basic Editor, which is accessed from the Macros sub-menu in the Tools menu in the menu bar.
Visual Basic for Applications (VBA) is a programming language integrated into Microsoft Office applications, including Excel. It allows users to automate tasks, enhance functionality, and create custom solutions within spreadsheets. VBA is used to write macros, which are sequences of instructions that perform repetitive tasks, manipulate data, interact with Excel objects (like worksheets and cells), and extend the application’s capabilities beyond its standard features. It is accessible via the Visual Basic Editor (VBE) and is particularly useful for users looking to streamline workflows and build complex data-driven tools.
You can see from the screenshot that there are three sets of code. One for the sheet, another for the workbook, and a third that is a code module. The workbook and module code are used for the autosave features, and the sheet code is used for the right-click toggling of the mute cells and the double-click toggling of the AUTOSAVE cell (A1).
Here’s the sheet code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim AllowedRange As Range
Dim cell As Range
Dim ValidCells As Range
' Define the allowed range where changes are permitted
Set AllowedRange = Me.Range("D2:S9999") ' Adjust this range as needed
' Limit the Target range to cells that are within the AllowedRange
Set ValidCells = Intersect(Target, AllowedRange)
' Only proceed if there are valid cells within the allowed range
If Not ValidCells Is Nothing Then
' Cancel the default right-click menu immediately
Cancel = True
' Loop through each valid cell inside the AllowedRange
For Each cell In ValidCells
' Toggle the cell value between "Clicked" and an empty string
Select Case cell.Interior.ColorIndex
Case xlNone: cell.Interior.ColorIndex = 3
cell.Value = "M"
Case Else: cell.Interior.ColorIndex = xlNone
cell.Value = ""
End Select
Next cell
Else
' Enable the default right-click menu immediately
Cancel = False
End If
End SubThis VBA subroutine toggles the background color and text in a right-clicked cell between an “M” on a red background and a blank cell. It tests to see whether the cell being right clicked is a mute cell i.e. a cell in AllowedRange. If it is, it suppresses the normal right click action (displaying a contextual menu.) It then tests if the cell is empty, and if it is, toggles it to show an “M” on a red background. If it isn’t empty, toggle it to become empty.
If the cell is not a mute cell then the normal right click behavior is allowed.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Define the allowed range where changes are permitted
Set AllowedRange = Me.Range("A1:A1") ' Adjust this range as needed
' Limit the Target range to cells that are within the AllowedRange
Set ValidCells = Intersect(Target, AllowedRange)
' Only proceed if there are valid cells within the allowed range
If Not ValidCells Is Nothing Then
' Cancel the default right-click menu immediately
Cancel = True
' Loop through each valid cell inside the AllowedRange
For Each cell In ValidCells
' Toggle the cell value between "Clicked" and an empty string
Select Case cell.Value
Case "OFF": cell.Value = "AUTOSAVE"
Case Else: cell.Value = "OFF"
End Select
Next cell
Else
' Enable the default right-click menu immediately
Cancel = False
End If
End SubThis VBA subroutine toggles the text in cell A1 between “AUTOSAVE” and “OFF” when the cell is double-clicked. It tests to see whether the cell being double-clicked is cell A1. If it is, it suppresses the normal double-click action. It then tests if the cell value is “AUTOSAVE”, and if it is, toggles it to “OFF”. If it isn’t, toggles it to “AUTOSAVE”.
If the double-click isn’t in cell A1 then double-clicking reverts to the normal behavior and displays the contextual menu.
Unless you are doing regular complex work with spreadsheets, learning VBA is a lot of effort for very limited usefulness, and it’s certainly beyond the scope of this tutorial to explore in any depth. However, because VBA has so much published material to support it, ChatGPT and other so-called AI systems can answer most questions about using it. A combination of the commenting within the script and ChatGPT should enable you to find out as much (or as little) as you want to know. It is important to remember, though, that ChatGPT nor any other so-called “artificial intelligence” LLM-based tool can be relied upon to be factually correct. They present completely fictional answers as confidently as real ones, so you need to check the answers you receive from them.
The only part of the code that you may need to edit is the AllowedRange for the right-click action. e.g. If you modified the spreadsheet to only deal with eight microphones (by deleting columns L to S), you would change the code to:
Set AllowedRange = Me.Range("D2:K9999")Adjust this range as needed.
Worksheet code:
Private Sub Workbook_Open()
Dim backupPath As String
Dim originalPath As String
'Check that a backup folder exists for this file and save a copy of thefile (regardless of the setting of the AUTOSAVE)
' Get the path of the original workbook
originalPath = ThisWorkbook.Path
' Create the backup path at the same level
backupPath = originalPath & "/" & ThisWorkbook.Name & " Backups" & "/"
' If folder does not exist, create it
If Dir(backupPath, vbDirectory) = "" Then
MkDir backupPath
End If
'Make the file name without the extension
SaveCopyAsFileName = "Copy of " & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
'Get the file extension of the activeworkbook
FileExtStr = "." & LCase(Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, ".", , 1)))
'Save the copy of the ActiveWorkbook
ThisWorkbook.SaveCopyAs backupPath & SaveCopyAsFileName & FileExtStr
saveTimer = Now + TimeValue("00:01:00")
Application.OnTime saveTimer, "Save1"
End SubThis code runs once when the workbook is opened, and makes a backup copy of the workbook regardless of the AUTOSAVE toggle in cell A1.
If no backup folder for the specific workbook exists, one is created and the backup is stored there.
At the end of the script, the code sets a timer for one minute. When that minute has elapsed, it runs the subroutine “Save1”, which is contained in the code for the module (Module 1):
Global saveTimer As Variant
Sub Save1()
Dim cell As Range
Dim targetColorIndex As Integer
Dim backupPath As String
Dim originalPath As String
' Define the target color index (for example, 6 represents Yellow in the Excel default color palette)
targetValue = "AUTOSAVE"
' Set the cell you want to check (for example, A1 on the active sheet)
Set cell = ThisWorkbook.Sheets(ActiveSheet.Name).Range("A1")
' Check if the background color index of the cell matches the target color index
If cell.Value = targetValue Then
'Check if the file have a path
If ActiveWorkbook.Path = vbNullString Then
MsgBox "The ActiveWorkbook have no file path, save it first and try again"
Exit Sub
End If
' Get the path of the original workbook
originalPath = ThisWorkbook.Path
' Create the backup path at the same level
backupPath = originalPath & "/" & ThisWorkbook.Name & " Backups" & "/"
' If folder does not exist, create it
If Dir(backupPath, vbDirectory) = "" Then
MkDir backupPath
End If
'Make the file name without the extension
SaveCopyAsFileName = "Copy of " & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
'Get the file extension of the activeworkbook
FileExtStr = "." & LCase(Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, ".", , 1)))
'Save the copy of the ActiveWorkbook
ThisWorkbook.SaveCopyAs backupPath & SaveCopyAsFileName & FileExtStr
saveTimer = Now + TimeValue("00:01:00")
Application.OnTime saveTimer, "Save1"
Else
saveTimer = Now + TimeValue("00:01:00")
Application.OnTime saveTimer, "Save1"
End If
End SubThis checks the value of the background of cell A1, which is toggle-able by double clicking (as driven by the sheet code.) If the text is “AUTOSAVE”, it saves the workspace as a copy in the backups folder and re-starts the one minute timer.
If the text isn’t “AUTOSAVE”, then the timer starts again; if autosave is toggled later, autosaving will resume.
This coded autosave must not be confused with Excel’s built-in autosave, which only works for files stored in cloud services like OneDrive or SharePoint. Because of this, Excels built-in autosave is less suitable for this project, but it could be used if preferred. For locally saved files, Excel provides a feature called AutoRecover. If Excel crashes, AutoRecover can restore the last saved version of the workbook which is stored in a temporary AutoRecover directory. However, access to that directory is only made available if Excel has crashed, so this tool cannot be used to revert a change that was deliberately made.
If you use the autosave feature written in this script, it’s best to close Excel if you are changing to a different spreadsheet. The reason for this is that VBA code in the module continues running even when the workbook is closed. There are ways of stopping it, but these seem to cause Excel to hang if you try to quit without saving. At the moment, restarting Excel when switching away from using this workbook is the least problematic option.
Play text import
The script for play text import can be run from the SHOWRENDER Script cue in the Scripts cue list. That script looks like this:
--Ingest a prepared script and convert to a Microphone Plot in in an Excel
--set up
use AppleScript version "2.4"
use scripting additions
use framework "Foundation"
--initialise some variables
set theCast to {}
set theProgress to false
--confirm execution dialog
display dialog " This script requires an Excel Spreadsheert and a text edit file with Character name(s) at the top of each speech consisting of a single word in a stand-alone paragraph, in upper case, with no punctuation, and separated by spaces.
Character Names must match Names in the MICROPHONES Cue List
If the notes of this cue (SHOWRENDER) are SYNC then the Text Edit document will show the progress of the Ingest, but it will not be possible to halt the script. To enable the ESC key to stop the script leave notes of the cue blank!
Proceed?"
tell application id "com.figure53.QLab.5" to tell front workspace
if notes of cue "SHOWRENDER" is "SYNC" then set theProgress to true
--GetCast Variables and Excel Column Names from QLab workspace
set theCastCount to count of (cues whose q number begins with "char")
repeat with theIndex from 1 to theCastCount
set the end of theCast to q name of cue ("char" & (theIndex as text))
end repeat
--clear Column Names
repeat with theCol from 1 to theCastCount
tell application "Microsoft Excel" to set the value of cell (theCol + 3) of row 1 to ""
end repeat
--set Column Names
repeat with theCol from 1 to (count of items of theCast)
tell application "Microsoft Excel" to set the value of cell (theCol + 3) of row 1 to item theCol of theCast
end repeat
--Parse Play text
set theCharFlag to false
set firstFlag to false
set theIndex to 1 --counter for paragraphs of play text
set thecueCounter to 1 --counter for Dialogue Blocks
ignoring white space
tell application "TextEdit" to tell front document
--set up
considering case
if theProgress is true then activate
--cursor to top of text edit document by brute force
set theCount to count of paragraphs
repeat theCount times
tell application "System Events" to tell application process "Text Edit"
key code 126 using option down
end tell
end repeat
delay 1
if theProgress is false then tell application id "com.figure53.QLab.5" to activate
--Process Dialogue Blocks
set theDialog to ""
repeat until theIndex > theCount --main repeat loop*******
set theCharacterFlag to false
set theSummary to ""
set theParagraph to paragraph theIndex as text
set theCharacterList to {}
set theCharacter to ""
--deal with blank lines
set AppleScript's text item delimiters to {return & linefeed, return, linefeed, character id 8233, character id 8232}
set newText to text items of theParagraph
set AppleScript's text item delimiters to {""}
set theParagraph to newText as text
--parse paragraph
if theParagraph is not "" then
repeat with eachword in words of theParagraph
set theCharacter to eachword as text
if theCharacter is "MUTE" or theCharacter is in theCast then
set theCharacterFlag to true
set the end of theCharacterList to theCharacter
end if
end repeat
if theCharacterFlag is false then
set theDialog to theDialog & "/" & theParagraph
end if
end if
--processing characters
if theCharacterFlag is true then
set firstFlag to true
tell application "Microsoft Excel"
set the value of cell (theCastCount + 4) of row (thecueCounter) to theDialog
set theDialog to ""
repeat with eachitem in items of theCharacterList
set theCharacter to eachitem as text
set theSummary to (theSummary & theCharacter & " ")
set theRange to "B" & ((thecueCounter + 1) as text)
select cell theRange
set the value of cell theRange to thecueCounter
set theRange to "C" & ((thecueCounter + 1) as text)
--print the Summary
--Mute ALL
if firstFlag is true or theCharacter is "MUTE" then
repeat with theColumn from 1 to theCastCount
set the value of cell (theColumn + 3) of row (thecueCounter + 1) to "M"
set color index of interior object of cell (theColumn + 3) of row (thecueCounter + 1) to 3
end repeat
set firstFlag to false
end if
--Unmute Characters Channel
if theCharacter is not "MUTE" then
set thePosition to (my firstIndexOf:theCharacter inList:theCast)
set theColumnUnMute to 3 + thePosition
set the value of cell theColumnUnMute of row (thecueCounter + 1) to ""
set color index of interior object of cell theColumnUnMute of row (thecueCounter + 1) to 0
end if
end repeat
set the value of cell theRange to theSummary
set thecueCounter to the thecueCounter + 1
end tell
end if
--ready for next paragraph
set theIndex to theIndex + 1
--scroll text
if theProgress is true then
tell application "TextEdit"
tell application "System Events" to tell application process "Text Edit" to key code 125
end tell
end if
end repeat --main repeat loop****************************************
--last dialog
tell application "Microsoft Excel"
set the value of cell (theCastCount + 4) of row (thecueCounter) to theDialog
set theDialog to ""
end tell
end considering
end tell
end ignoring
end tell
--Sub routines
on firstIndexOf:anItem inList:aList
set anArray to current application's NSArray's arrayWithArray:aList
return (anArray's indexOfObject:anItem) + 1
end firstIndexOf:inList:The script uses NSArray to find the position of character names in a list so it knows which channels to mute. NSArray is part of the Apple Foundation library.
A dialogue explains what is about to happen and warns that the script, once running, can’t be halted unless the notes of cue “SHOWRENDER” are blank or, more accurately, do not contain the word SYNC.
To move the cursor through the TextEdit document as it processes, that document must be active. Because of this, the keyboard focus cannot be returned to QLab for long enough for it to receive any input to stop the script. If the notes of cue “SHOWREADER” are SYNC, then the text edit document will scroll to show the progress of the ingest. If the notes are blank, the text edit document won’t scroll, and QLab can stop the script at any time, either by selecting the Cue and pressing “S” or using ESC.
The script gets the cast names from the MICROPHONES cue list and adds them to a list variable theCast.
It clears the existing column headings for the Mic Mute columns and then sets them to the items in theCast list variable.
If the text document is syncing its progress, the cursor is sent to the beginning of the play by repeatedly simulating the keying of the up arrow.
A variable, thecueCounter, is initialized to a value of 1. This is incremented each time a new speech is detected and is used to calculate an identifying number for the mute state in the MEM column and to determine the spreadsheet row that data is written to as the text ingest progresses.
The program collects lines of text that do not contain character names as it reads through the text in the variable theDialog, separating each line with a forward slash (/). Blank lines are stripped out.
Because the code is contained in a consider case block, the match between character names in the text and character names in the MICROPHONE cue list in QLab will be case-sensitive. (Apple’s default is to ignore case when comparing string values.)
When it does find a name that matches a character in theCast variable or the word MUTE it:
- Writes a sequential number in the MEM column of the spreadsheet;
- Treats that paragraph as character names at the beginning of a speech, finds any more names in case there are multiple characters speaking and collects these in the variable
theSummary; - Writes the contents of
theDialoguevariable to the current row’s dialogue column and resets the variabletheDialogueto an empty string; - Sets all the mute cells in the current row to display “M” on a red background;
- Determines any current character’s position in the list variable
theCastusing a subroutine that uses NSArray; - Clears the cell for that character’s mute to indicate their microphone is live;
- Increments
thecueCounterby 1; - Proceeds to the next paragraph.
The final few lines write any dialog after the last character name has been detected.
The engine
The cues that are primarily responsible for the operation of the workspace are contained in the cue list named ENGINE.
A Text cue numbered DIALOGUE displays the current speech from the play text on a second display for the operator.
A Group cue numbered CLEAR disarms all the mute on and mute off cues in cue STATE.
A Group cue numbered STATE contains a Timeline Group cue containing a set of cues which set the mutes of the sixteen mixer channels ON (microphone muted), and another Group cue containing a set of cues which sets them OFF (microphone live).
Each MUTE ON Group cue contains a Network cue with an OSC message which sets the color of a cue in the MUTE MIMIC cart to red:
together with the cues to turn that channel’s mute ON on a physical mixer or, as in the case of the demo, a software mixer:
Each MUTE OFF Group cue contains a Network cue with an OSC message, which sets the color of a cart button in the MUTE MIMIC cart to none, together with the cues to turn that channel’s mute OFF.
The mute state is set by disarming the MUTE OFF Group cues, and arming the MUTE ON Group cues for channels that are muted, and arming the MUTE OFF groups, and disarming the MUTE ON Group cues for channels that are live.
The heart of the ENGINE is the Script cue numbered LOAD. This reads the spreadsheet and sets the arms and disarms of the cues in the STATE Group cue to match the mute cells in the spreadsheet.
--**********************Load the Mutes*******************************************************
--RUN WITH SCRIPT CUE INSPECTOR WINDOW ON BASICS TAB REGARDLESS OF WHETHER THE INSPECTOR IS OPEN OR NOT!!! 10 TIMES FASTER.
--Translate Mute Data from an Excel spreadsheet to control mutes on a sound console
set theCount to 16 --number of microphone channels
--try
set theMutes to {}
tell application id "com.figure53.QLab.5" to tell front workspace
start cue "CLEAR" --disarm all Mute Unmute Cues
set theRow to notes of cue "MEM"
end tell
set theTids to AppleScript's text item delimiters
set AppleScript's text item delimiters to ","
tell application "Microsoft Excel" to tell front sheet
set theRowRef to "B" & theRow
set theCast to value of cell ("C" & theRow)
set theDialogue to value of cell ("T" & theRow)
set theMem to value of cell ("B" & theRow)
repeat with eachMute from 1 to theCount
set the end of theMutes to value of cell (eachMute + 3) of row (theRow as integer)
end repeat
end tell
--Parse the data and perform actions depending on whether a cell contains M (MUTED) or has an empty string as its value (NOT MUTED)
tell application id "com.figure53.QLab.5" to tell front workspace
set theIndex to 1
repeat with eachitem in (text items of theMutes)
if text of eachitem is "M" then
set the armed of cue ("M" & theIndex & "ON") to true
else
set the armed of cue ("M" & theIndex & "OFF") to true
end if
set theIndex to theIndex + 1
end repeat
--Store Dialog
tell application id "com.figure53.QLab.5" to tell front workspace
--convert / to return
set AppleScript's text item delimiters to {"/"}
set newText to text items of theDialogue
set AppleScript's text item delimiters to {return}
set theDialogue to newText
set the notes of cue "DIALOGUE" to theCast & return & theDialogue
end tell
end tell
set AppleScript's text item delimiters to theTidsThe script gets the number of microphone channels in use from the MICROPHONES cue list.
It disarms all MUTE ON and OFF Group cues in cue STATE by starting cue CLEAR.
Then, it gets the number stored in the notes of cue MEM, which is the row number (not the MEM number) of the Spreadsheet line that will be actioned on the next GO trigger.
Then it gets the following information from the current row of the spreadsheet: character names, dialogue, MEM number, the mute state of each mixer channel.
Then it processes this data in QLab to arm the appropriate Group cues in cue STATE so that the mixer channel mutes will immediately be actioned when the next GO is triggered.
Finally it converts all the forward slash separators in theDialogue to returns and then sets the notes of the Text cue numbered DIALOGUE to the character name(s) plus the dialogue to make a complete screen of text, ready to display on the next GO.
The CONTROL cart
The control cart is the main controller for playback of the muse state presets.
DECREMENT, triggered in the demo workspace by hotkey NUM 1, is a Script cue with this script:
--get the current row number
tell application id "com.figure53.QLab.5" to tell front workspace to set theRow to notes of cue "MEM" as integer
--decrement the row number
if theRow is greater than 2 then set theRow to theRow - 1
tell application "Microsoft Excel" to tell front sheet
set theRangeString to ("A" & theRow as text)
set theRange to (range theRangeString)
set color index of interior object of column 1 to 0 --clear the green indicator (State to be set when next GO is triggered )
set color index of interior object of range theRangeString to 4 --set the green indicator to the new row
select range theRangeString
set theValue to value of cell ("B" & theRow as text) --get the MEM number for this MEM
end tell
tell application id "com.figure53.QLab.5" to tell front workspace
set the notes of cue "MEM" to theRow --store the Excel row number for the next GO in the notes of cue MEM
--display the MEM number of the mute state to be actioned on the next GO on the GO and SET MUTES CART BUTTON Mutes
set the q name of cue "GO" to theValue
set the q name of cue "SET MUTES" to theValue
start cue "LOAD"
end tellINCREMENT, triggered in the demo workspace by hotkey NUM 3, is a Script cue with this script:
--get the current row number
tell application id "com.figure53.QLab.5" to tell front workspace to set theRow to notes of cue "MEM" as integer
--increment the row number
set theRow to theRow + 1
tell application "Microsoft Excel" to tell front sheet
set theRangeString to ("A" & theRow as text) --& ":A" & theRow as text
set theRange to (range theRangeString)
set color index of interior object of column 1 to 0 --clear the green indicator (State to be set when next GO is triggered )
set color index of interior object of range theRangeString to 4 --set the green indicator to the new row
select range theRangeString
set theValue to value of cell ("B" & theRow as text) --get the MEM number for this MEM
end tell
tell application id "com.figure53.QLab.5" to tell front workspace
set the notes of cue "MEM" to theRow --store the Excel row number for the next GO in the notes of cue MEM
--display the MEM number of the mute state to be actioned on the next GO on the GO and SET MUTES CART BUTTON Mutes
set the q name of cue "GO" to theValue
set the q name of cue "SET MUTES" to theValue
start cue "LOAD"
end tellMEM is a Memo cue which displays the MEM number of the current mute state and stores the row number (not the MEM number) of the mute state that will be actioned when the next GO is triggered.
GO, triggered in the demo workspace by hotkey NUM 0, is a Script cue with this script:
--*********************DO MUTES & DIALOGUE***********************
tell application id "com.figure53.QLab.5" to tell front workspace
start cue "STATE"
set the text of cue "DIALOGUE" to (notes of cue "DIALOGUE" as text)
start cue "DIALOGUE"
set theRow to notes of cue "MEM"
set theRowRef to "B" & theRow
end tell
--Highlight the last memory data recalled in the excel spreadsheet
tell application "Microsoft Excel" to tell front sheet
set color index of interior object of column 2 to 0
set color index of interior object of range theRowRef to 8
select range theRowRef
set theValue to value of cell ("B" & theRow as text)
tell application id "com.figure53.QLab.5" to tell front workspace
start cue "INCREMENT"
set the q name of cue "MEM" to theValue
end tell
end tellThe order of events in this script prioritizes the immediate activation of the new mutes by starting cue STATE, followed by the display of the dialogue for that speech by copying the dialogue from the notes of cue DIALOGUE and starting the cue. (In case the cue is not currently running).
It then clears the background color from all cells in column 2 and sets the background color of the current MEM to indicate the current mutes applied.
Finally, the script updates the name of cue MEM to display the current MEM.
JUMP (hotkey J) is a Script cue with this script:
set theMaxCues to 9999 --maximum search range for cues
set theQ to text returned of (display dialog "Spreadsheet Memory to Jump To" default answer 1)
tell application "Microsoft Excel" to tell front sheet
set searchRange to range ("B1:B" & (theMaxCues as text))
try
set foundrange to (find searchRange what theQ look at whole)
on error
tell application id "com.figure53.QLab.5" to display alert "MEM does not exist"
return
end try
set theRow to first row index of foundrange
set theRowRef to "A" & theRow
set color index of interior object of column 1 to 0
set color index of interior object of range theRowRef to 4
select range theRowRef
end tell
tell application id "com.figure53.QLab.5" to tell front workspace
set the notes of cue "MEM" to theRow
set the q name of cue "GO" to theQ
set the q name of cue "SET MUTES" to theQ
start cue "LOAD"
end tellAll the other CONTROL cues use the number of the spreadsheet row stored in the notes of cue MEM as the row reference, but JUMP finds a cue from it’s MEM number.
The MEM number is entered by the user in a dialog. The reply to this dialog is searched for using Excel’s AppleScript search function:
set foundrange to (find searchRange what theQ look at whole)It then sets the green marker to the found cue.
SET MUTES is a script cue:
--*********************DO MUTES***********************
tell application id "com.figure53.QLab.5" to tell front workspace
set theRow to notes of cue "MEM"
set theRowRef to "B" & theRow
start cue "STATE"
end tell
--Highlight the last memory data recalled in the excel spreadsheet
tell application "Microsoft Excel" to tell front sheet
set color index of interior object of column 2 to 0
set color index of interior object of range theRowRef to 8
select range theRowRef
set theValue to value of cell ("B" & theRow as text)
--****************************DO DIALOG****************** --Display Dialog
tell application id "com.figure53.QLab.5" to tell front workspace
set the q name of cue "MEM" to theValue
set the text of cue "DIALOGUE" to notes of cue "DIALOGUE"
start cue "DIALOGUE"
end tell
end tell
Its operation is similar to GO, except it does not advance the playhead.
MICROPHONES cue list
This cue list lists the microphone/character names. These are the names that must match exactly, including case and punctuation, the character names in the play text.
Useful techniques for manipulating cells in spreadsheets
- Select a single cell and drag the corner handle to duplicate across a range.
- Right-click on a selected range of cells to toggle them together.
- Select a pair of cells that contain the beginning values of a data series and drag the corner handle to continue the series.
All images in this chapter were “AI” generated.
Text from ‘Romeo and Juliet’ via Project Gutenberg.
Excel is a product and registered trademark of Microsoft, Inc.