Creating a Catalogue in Google Sheets

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)


   Reply
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.

   Reply
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.

   Reply

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