If you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a function you understand!). Then, build it back up in steps to get back to the full formula. Ben Collins ‘Use The Onion Framework To Approach Complex Formulas’

I was recently asked to have a look at spreadsheet that listed all the guides and videos located in a support folder as a reference. This folder also included a number of folders within folders, which created a level of complexity.

The current workflow involved using an Add-on File Cabinet from the developers behind Awesome Table to generate a list. The problem with this list is that the exported data was not in the desired format. Whereas the output focused on file, folder and link, the list produced using File Cabinet included other information such as owner, creation date and file size. In addition to that, there were some folders and files listed that needed to be removed. Therefore, it had become manual heavy exercise to refresh the data and update the directory meaning it did not happen that regularly.

After looking at the list and the current finished product and the list produced using File Cabinet, I realised that rather than displaying the sub-folder, that the directory was showing a top level folder. Therefore to manage this, I used a SWITCH formula to create a new column updating the folder name to the overall area it belonged to.

To create the SWITCH, I created a UNIQUE list of all the folders and then pasted them as values so that I could delete those not required to be displayed in the directory. After culling the list, I then added the area in the cell next to each folder. Once I had these two columns complete, I used this formula to add quotation marks to them all:

=ArrayFormula(""""&Sheet1!A:D&"""")

I then copied this list into the SWITCH formula and added in the commas:

=IFNA(SWITCH(B1:B,
" EOY 2020","EOY",
"Absences and Attendances","eSIS",
"Class Maintenance","eSIS",
"Crystal Reports","eSIS",
"Debtors","eSIS",
"eSIS - Information Sheets","eSIS",
"eSIS Navigation-Overviews","eSIS",
"General _ Misc","eSIS",
"Managing Community","eSIS",
"Managing Student Lifecycle","eSIS",
"Reporting","eSIS",
"Staff Maintenance","eSIS",
"Student Medical Maintenance","eSIS",
"SynWeb","eSIS",
"Timetabling","eSIS",
"eLearn General ","eLearn",
"eLearn QRGs","eLearn",
"Accounts Payable","eFIN",
"General","eFIN",
"Purchasing","eFIN",
"ePortal","ePortal",
"Create New Staff","eHR",
"eHR - General","eHR",
"eHR Materials","eHR",
"Employee Self Service","eHR",
"Employee Self Service (ESS)","eHR",
"End of Year 2020 - eHR & Payroll Guide","eHR",
"Leave","eHR",
"ICON General Information","General Information",
""," "
))

With this addition column, I then used a QUERY to capture the title, area and link to present as a directory in a separate spreadsheet to share with a wider audience:

=SORT(QUERY(IMPORTRANGE("docs.google.com/spreadsheets/d/.../edit","Sheet2!A:D"),"SELECT Col1,Col4,Col3 WHERE Col4 is NOT NULL"),2,FALSE)

Ideally, if creating this solution from scratch, I would probably have started each folder with a prefix indicating which area it belonged to, therefore avoiding the need for the SWITCH formula. However, that horse had long bolted. I also like the idea of using the SWITCH formula to manage which folders are displayed. It is definitely easier to pass on to somebody else.


I feel that it would be plausible to find a different script (see for example these examples from Alice Keeler, mesgarpour and Spreedsheet Dev) that might do some other things, such as run the process as a cron job or pull only the desired data. However, that is beyond my current skillset and patience level to dig any further at this point.. Therefore, I will stick to using various formulas to filter out the data for me.


If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

Creating a Catalogue in Google Sheets by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

4 thoughts on “Creating a Catalogue in Google Sheets

  1. I have found a cleaner method for adding in the commas and quotations required for the SWITCH formula:

    =char(34)&join(“””,”””,ARRAYFORMULA(QUERY(A1:A,”SELECT A WHERE A IS NOT NULL”)&”””,”””&QUERY(B1:B,”SELECT B WHERE B IS NOT NULL”)))&char(34)

  2. In this guide, Atlassian and AXELOS have partnered to help jumpstart your agile journey. You’ll learn eight practices typically used by high-velocity IT teams, and tips from the Atlassian Team Playbook to bring more agility and collaboration into ITSM:

    Source: ITIL 4 is here—and it’s more agile than ever. by Atlassian

    Akshay Anand, Paul Buffington, Ian Buchanan and Teresa Fok from Atlassian and Axelos come together to provide a practical guide for working with ITIL 4 and Atlassian. The whitepaper begins by addressing the guiding principles to ITIL:

    • Focus on value
    • Start where you are
    • Progress iteratively with feedback
    • Collaborate and promote visibility
    • Think and work holistically
    • Keep it simple and practical
    • Optimize and automate

    It then explores the practices that the ‘best performing IT teams typically use’:

    • Continual improvement with retrospectives – This can involve two continual improvement practices: the Improvement Kata and retrospectives.
    • Agile project management to speed up project delivery
    • Knowledge management to empower team culture – This can involve aggregating your team’s knowledge in a single repository.
    • Customer-centered service desk and request management – This often involves a focus on developing resources and processes to support self-service and sharing documentation with lower levels.
    • Adaptive incident management – This involves planning, responding, and learning from every incident.
    • Streamlined change control through automation and collaboration
    • Continuous delivery for deployment management
    • Integrated software development and operations teams – This can include shifting your mindset towards better collaboration, tighter integration, and shared risks and responsibility.

    I found this paper interesting reflection upon my practices, as I feel that I am already doing many of the things intuitively, but that ITIL framework provides clarity on how to talk about this. For example, a few years ago I developed public facing catalogues associated with reports and guides which can be understood as a “Shift left” approach to setting up self-service strategies. While when implementing the eLearn solution, I created a process to support learning from incidents through the creation of a knowledge base organised into different modules. This was then used to develop proactive actions to prevent such incidents occuring again. I also introduced introduced Trello and Kanban to my team as a means of managing projects collaboratively.

  3. I recently discovered that Awesome Tables’ Filing Cabinet Add-on has been deprecated, this broke the catalogue I had created with Google Sheets.
    I searched online for any further explanation on the change, but was simply sent to Awesome Tables support page.
    I started exploring other options online and short of paying for API connectors, I could not really find anything. I subsequently turned to CoPilot, wondering what it might give me. Surprisingly, it gave me a basic script for everything that I needed.
    function listFilesInFoldersGEN() {
    var folders = [
    {folderId: 'URL', sheetName: 'General'},
    // Add more folders as needed
    ];

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    for (var i = 0; i < folders.length; i++) {
    var folderId = folders[i].folderId;
    var sheetName = folders[i].sheetName;
    var folder = DriveApp.getFolderById(folderId);
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) {
    sheet = spreadsheet.insertSheet(sheetName);
    }

    // Save the existing data
    var range = sheet.getDataRange();
    var values = range.getValues();

    try {
    sheet.clear();
    sheet.appendRow(["Name", "Date", "Size", "URL", "Folder"]);
    listFilesInFolderRecursiveGEN(folder, sheet, folder.getName());
    } catch (e) {
    // If an error occurs, revert to the saved data
    range.setValues(values);

    // Log the error
    var errorMessage = 'Error: ' + e.toString();
    Logger.log(errorMessage);

    // Send an email
    var emailAddresses = ['bwillis@edu.au', 'nlapin@edu.au'];
    // Enter your email address here
    var subject = 'Error in Support Catalogue - General script';
    var body = errorMessage;
    MailApp.sendEmail(emailAddress, subject, body);
    }
    }
    }

    function listFilesInFolderRecursiveGEN(folder, sheet, path) {
    var files = folder.getFiles();
    while (files.hasNext()) {
    var file = files.next();
    sheet.appendRow([file.getName(), file.getDateCreated(), file.getSize(), file.getUrl(), path]);
    }

    var subfolders = folder.getFolders();
    while (subfolders.hasNext()) {
    var subfolder = subfolders.next();
    listFilesInFolderRecursiveGEN(subfolder, sheet, path + '/' + subfolder.getName());
    }
    }

    After a bit of back and forward, I had a new working catalogue which I provided to the team to provide feedback on.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.