It’s been a magnificently productive day at Innerspace Labs and we’ve reached what is to date our most prestigious milestone. I published a feature
last March about the evolution of my life-long list-making of sound works, cinema, and literature that I’ve been meaning to explore. These lists also served to touch upon some of the special collections in my archive.
In the previous article I described how this process began with leather pocket journals, and as the scale of my library grew I began to publish annual print editions itemizing large collections.
These efforts were radically transformed several years ago when I migrated to Google Drive. But as the years passed and spreadsheets and documents multiplied, it rapidly became apparent that I needed to consolidate all of these various lists into a single, deep searchable index otherwise countless lists would be forgotten and disappear into the digital void of my Google Drive.
Thus began the Innerspace Labs Master Workbook project this past spring. Though this venture posed several new dilemmas. As the workbook grew to nearly 200 tabs, I received this error stating that Google Workbooks are limited to 5 million or fewer cells.
And it quickly became evident that navigation of all those tabs was painfully arduous in the mobile environment, as was its loading time. Thankfully, after careful research into various potential solutions, I’ve implemented a system of scripts and formula expressions which make navigating this large workbook a snap and its interactive response time nearly instantaneous.
By combining over 200 named ranges, and incorporating a primary dynamic drop-down and a dependent secondary drop-down field, along with an "=INDIRECT(CONCATENATE" expression calling named ranges based on user input, I’m now able to hide and lock all but one master sheet and made the entire workbook navigable from that single homepage.
The home sheet offers the user a primary drop-down of LITERATURE, SOUND, or VIDEO, which in turn controls a secondary dependent drop-down to populate and auto-alphabetize a list of all related content for that category.
I’ve also employed a script which is triggered by Google Clock to rescan the entire workbook for newly-added lists and to automatically incorporate them into the search fields alphabetically and by category as the workbook continues to grow.
I understand that it may not have significant value to anyone other than myself, but it’s intended to serve as a reference document along with the over 200-pages of archive summaries I’ve drafted in a companion Google Doc. With this easy-to-reference Workbook, I can pull up a list in seconds and start exploring. My hope is that the project helps introduce me to some spectacular content and that it helps me rediscover forgotten areas of my library.
The next phase of the project is to apply uniform formatting to all lists, as these were drafted independently over the course of nearly a decade, so I apologize for the crudity of its present format. And of course, there may be errors or omissions among the lists. But you know that I’ll work tirelessly to make this project as accurate and accessible as I can.
Here is a link to a copy of the latest version. It showcases and attempts to organize ~26,000 of the most noteworthy elements of my personal library and related subjects of interest. All cells are locked for editing except the two dynamic drop downs, which is sufficient for general users to explore and interact with the document. It’s far from perfect, but it’s a labor of love that I will continue to work on and which I hope will enrich my life as it continues to expose me to some of the greatest works of the ages.