Spreadsheet Toolbar

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:

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 script to clipboard

Copy the current Humdrum interaction script from this link, or click on the copy button above this paragraph. Then paste into the Code.gs 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.

If you want to edit the script to add your own functionalities (which you could post to the Verovio Humdrum Viewer issues if you want to share), see the Google Apps Script documentation. The script is in the JavaScript language, integrated with Google Sheets API functionality.

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:

AKfycbxIXX29Z3qsWzhhrurmmoQ9MNcLfhS-z5gyejXLTIn2VcHXzMZ50Qha-VcoJLjzi5xm

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:

https://docs.google.com/spreadsheets/d/1_E31WEm8_u8dg0zgBTC2SJnKRm1OuJBCl_ameYv64m8/edit#gid=0

In this case the spreadsheet ID is 1_E31WEm8_u8dg0zgBTC2SJnKRm1OuJBCl_ameYv64m8.

Add this spreadsheet ID after the script ID in the text box on the spreadsheet toolbar, separating the two IDs with a pipe character (|):

AKfycbxIXX29Z3qsWzhhrurmmoQ9MNcLfhS-z5gyejXLTIn2VcHXzMZ50Qha-VcoJLjzi5xm|1_E31WEm8_u8dg0zgBTC2SJnKRm1OuJBCl_ameYv64m8

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.

Expanded-tab formatting

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.

Freezing rows

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.

IGNORE columns

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 IGNORE feature:

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:

Humdrum menu

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.

Colorize data

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.

Interpretation line

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 (!).

Data line

Add an null data line above the current row (.).

Show/hide columns…

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.