The spreadsheet toolbar is used for setting up interaction between VHV and Google Sheets. This allows for doing more advanced editing of Humdrum text in a spreadsheet than can be done in the plain-text editor of VHV, such as adding and deleting columns, hiding columns, or adding temporary columns for data entry or analysis. Working with orchestral scores is also easier in a spreadsheet, where you can freeze a header at the row showing the instrument names while you edit the music further below.
The spreadsheet toolbar contains a text box and five icons:
||See instructions below for creating a spreadsheet and getting its script ID to paste into this box to interact with the spreadsheet. The spreadsheet ID can be appended to the script ID, which will cause a "open linked spreadsheet" icon to appear in the toolbar.|
||The contents of the VHV text editor will be uploaded to the linked spreadsheet, replacing any current contents of the spreadsheet.|
||The contents of the linked spreadsheet will be downloaded and replace the current contents of the VHV text editor.|
||The spreadsheet ID can be appended to the script ID in the input text box, seprating them with a pipe character (|) or a space. When a spreadsheet ID is present, this icon will appear, and you can click on it to open the linked spreadsheet in another tab. Once you add the spreadsheet ID to the Spreadsheet script ID box, this icon will only appear after you click on the upload or download button; however, it will show up when reloading the VHV webpage after that.|
||6+alt-n (when not focused on the text editor). Press the shift key while clicking to cycle through the toolbars in the opposite direction.To return to this toolbar directly, press|
Setting up a link to Google Sheets
To make a connection between VHV and Google Sheets, you first need to create a Google spreadsheet, then copy and paste and publish this Google Apps Script for the spreadsheet, and finally copy and paste the published script’s ID into the input box on spreadsheet toolbar. The instructions for setting up the script are given in the following sub-sections.
Step one: create a spreadsheet
Create a new spreadsheet in Google Sheets. You need to have a Google account to do this step.
Step two: open the script editor
Save the spreadsheet with a name of your choosing, such as “Humdrum interaction” in the following example. Then go to the menu Tools → Script editor:
A new tab will open, displaying the script editor:
Step three: copy and save the script
Copy the current Humdrum interaction script from this link, or click on the
copy button above this paragraph. Then paste into the
tab in the script editor, replacing the original contents. This
script will be updated over time, so you can recopy it every once
in a while to get the script with more features and/or bug fixes
(as well as new bugs).
Then save the script, giving a name of your choice to the script project:
Step four: deploy the script
To use the script, it must be “deployed”. Press the blue “Deploy” button and select “New development”:
A sub-window will appear. Click on the gear icon next to the “Select type” and choose “Web app”:
The following subwindow will appear. Add a description of your choosing, change “Who has access” to “anyone”, and then click on the blue “Deploy” button:
The first time you deploy, you will have to authorize the script:
In the next window, choose that account that you use with Google Sheets:
A warning window will appear. Click on the Advanced link:
Then click on the link “Go to Humdrum interaction (unsafe)” (or whatever you named the script). And then finally click on the “Allow” button to activate the script:
A window such as the following will appear if the deployment is successful:
Copy the Deployment ID and click on the blue “Done” button.
Linking VHV to Google Sheets
After deploying the script, you now are ready to connect VHV to Google Sheets.
The script deployment ID will be something like this:
Paste this deployment ID into the “Spreadsheet script ID” box on the spreadsheet toolbar in VHV. Once the Deployment ID is pasted into the script ID box, the cloud upload/download buttons will work to copy data between the VHV and the Google Spreadsheet:
You can use the above script ID for testing purposes, which links to this publicly editable spreadsheet. But don’t use this particular spreadsheet for actual work, since other people testing spreadsheet interaction can delete your data on it and/or copy your data on the spreadsheet to their own VHV editor. However, you can set up a collaborative editing spreadsheet with other people by sharing your script/spreadsheet IDs with them to copy into their VHV spreadsheet toolbar text box.
Adding spreadsheet URL to script toolbar
For easy access to the spreadsheet you can optionally add a link to the spreadsheet to the toolbar. To do this, not the URL of the spreadsheet (not the script), such as:
In this case the spreadsheet ID is
Add this spreadsheet ID after the script ID in the text box on the spreadsheet toolbar, separating
the two IDs with a pipe character (
This will enable an icon on the spreadsheet toolbar that opens up the linked spreadsheet:
The spreadsheet icon will display after you do your first upload or download from VHV. Once you have set up the script ID and sheet ID, VVH will remember it as long as you use the same browser.
Interacting with the spreadsheet
You are now ready to transfer Humdrum data between VHV and Google Sheets. Click on the cloud-upload button immediately to the right of the script ID box. This will cause the contents of the VHV editor to be uploaded to the Google Sheet you created for that script ID:
After you finish editing the Humdrum file in Google Sheets, click on the cloud-download button on the VHV spreadsheet toolbar to transfer the data back into the text editor. Here is an example where all parts except the Bass part are deleted from the spreadsheet, and then the Humdrum data is downloaded data back into VHV:
Then clicking on the cloud-download button in the VHV spreadsheet toolbar, will copy the contents of the spreadsheet back into the VHV text editor:
Placing the spreadsheet and VHV side-by-side in separate browser windows is a good method for working with this feature. You can make edits in the spreadsheet, then move to the VHV editor and click on the cloud-download button to view the changes as often as desired.
In the above example configuration, the VHV text editor is hidden with the alt-y keyboard shortcut, since editing the Humdrum text in both windows is not necessary and frees up display real-estate for the music notation.
Cell text escaping
When uploading Humdrum data into the spreadsheet, all cells are formatted to be text cells. The spreadsheet will still try to interpret some text cells, such as when an equals sign starts the text for Humdrum barlines. A single quote character is added at the start of barline cells to force the cell’s contents to be treated as text without further interpretation. This single quote will be stripped off of the data automatically when downloading from the Google spreadsheet back into VHV. Also, tokens starting with a single quote will have an additional quote added to escape the initial single quote.
Below is a demonstration of the effect the single quote has on the
display of barlines. The text
=9|| will be interpreted as a
formula, but this produces a syntax error due to the double-barline
styling, resulting in the text
#ERROR! being show in the
spreadsheet. This text will be downloaded into VHV if the barline
token does not have a single quote added at the start of the cell.
In cell A4, the cell text is
'=9||, which prevents formula
interpretation of the text.
So if you are entering barlines on the spreadsheet, ensure that you
type a single quote character before the barline. The problem
mostly only involves text starting with an
= sign. Since all
cells on the sheet are set to be text rather than numbers, unescaped
numbers will be treated as text by default. If you want to do
numeric operations on data in the Humdrum content, you should
reformat them as numbers from the Format → Number menu.
When uploading data to a spreadsheet, it will first be run through the
tabber filter to straighten out spines into columns. When the data
is downloaded back to the text editor, the state of the current data
in the text editor will be matched. If the text editor contains
data with expanded tabs, the downloaded data will be left expanded.
If the text editor contains data with compressed tabbing, the downloaded
data will be run through the
tabber -r filter to remove the alignment
tabs before it is placed in the text editor.
For orchestral works, freezing the spreadsheet row with instrument names is useful.
After freezing, the header lines will always remain visible. In the example below, measure 4 is being displayed, yet the instrument names remain visible.
If there are a lot of reference records above the instrument name row, you can hide the unwanted rows by selecting them by clicking on the first row number and shift-click on the last row number to hide. Then right-click in the row number area to bring up the following menu and select the hide-rows option:
After hiding the first three rows, the spreadsheet looks like this:
Notice that there is a little arrow for row 4, which indicates the presence of the hidden rows. You do not need to unhide the rows when downloading the spreadsheet’s contents into VHV, since the hidden rows will always be included in the download.
If you place the text
IGNORE in a cell in the first row of the spreadsheet,
that column will not be exported back into VHV when you download the data. This
is useful for adding scratch columns for data processing purposes or doing
calculations interleaved within the data. Here is an example use of the
When downloading back into VHV, the
IGNORE columns are ignored:
Moving music between subspines
A useful function of spreadsheet editing is the ability to move notes between voices/layers much more quickly than is possible in the VHV text editor. Here is an example of how to switch the order of two subspines to get the voices in the desired order for automatic stem assignments. Starting with this data where the two subspines are in the wrong order:
Expand the tabs and upload to a spreadsheet:
Then copy a region in the right-hand subspine and paste into an unused column (column G in this example). You can optionally add an “IGNORE” marker at the top of the scratch column so that you do not need to delete the leftover content when finished swapping the subspines:
Then copy the left subspine tokens from column C to D:
And finally, copy the new left spine data from column G to C:
Downloading the Humdrum score back into VHV shows the voices in their proper positions:
However, this manual process of switching subspines can be handled
faster and automatically with the flipper filter
in a single step, using
-a to flip all subspines in the score:
A custom Humdrum entry is added to the spreadsheet menu by the Humdrum script:
Each option is describe below. In the future, more capabilities will be added.
Fix barlines rows
If you add barlines to the spreadsheet, but do not add a single quote in front of the equals sign, run this script to add the single quote so that the barline cells are not interpreted as formulas.
If you add data lines or columns to the Humdrumd data and the colorization is no longer valid, select this menu item to recolorize the Humdrum data. This item is also useful to run after copy and pasting Humdrum data onto the spreadsheet.
Add above current line…
Options that add null-token lines above the current line to fill in with content, such as layout parameters, a new sonority or interpretations such as clef changes.
Add a empty interpretation above the current row (or top cell in the selection), which
will add a new row with
* above each non-empty column in the selected row.
Local comment line
Add an empty local comment line above the current row (
Add an null data line above the current row (
Options to selectivly show or hide data spines. These options are useful to select a subset of spines for data entry.
Hide non-kern spines
Hide add data spines that are not
**kern. In other words hide
lyrics, dynamics, text, harmony, etc., and only show the notes. This is
useful for cases where you want to edit/view notes.
Show only selected spines
Select one or more columns to display, hiding other columns. Any rows can be used for the selection. If the tabs are expanded, the selection will include all subspines even if they are not in the selection.
Hide selected spines
Select one or more columns to hide, showing other columns. Any rows can be used for the selection. If the tabs are expanded, the selection will include all subspines even if they are not in the selection.
Show all spines
Unhide all columns of data on the spreadsheet.