Replied to Creating a Catalogue in Google Sheets by Aaron DavisAaron Davis (

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 …

I recently discovered that Awesome Tables’ Filing Cabinet Add-on has been deprecated, this broke the catalogue I had created with Google Sheets.

Message from Awesome Tables about the move to a paid add-onI 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.appendRow(["Name", "Date", "Size", "URL", "Folder"]);
      listFilesInFolderRecursiveGEN(folder, sheet, folder.getName());
    } catch (e) {
      // If an error occurs, revert to the saved data

      // Log the error
      var errorMessage = 'Error: ' + e.toString();
      // Send an email
      var emailAddresses = ['', '']; 
      // 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 =;
    sheet.appendRow([file.getName(), file.getDateCreated(), file.getSize(), file.getUrl(), path]);
  var subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    var subfolder =;
    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.

Replied to A Guide To Named Functions In Google Sheets (New For 2022!) – by Ben Ben (

Named functions in Google Sheets let you create and name custom functions built with regular formulas, and reuse them in other Sheets.

Ben, am I right to say that Named Functions allows the functionality that is available via Google Scripts, it just means that you do not have to go through the effort of creating the script? I am assuming that if I made a copy of a Sheet with a Named Function that this comes with the copy? I also assume that unlike with Google Scripts, there is no need to provide permission for Named Functions? I am left wondering about combining the use of macros and Named Functions.
Bookmarked Send email when a Google Sheet is edited (

Suppose you’re a school teacher and you’re planning an upcoming Parent-Teacher conference. Instead of using a printed sign up sheet, you decide to use a Google Sheets spreadsheet where parents can sign up for a meeting slot.

In the past you’ve had parents forget which slot they signed up for so you’re hoping that a shared Google Sheet that they can access at any time will help solve this problem. You also want to receive an email notification whenever a parent signs up for a slot. This will help you keep track of who hasn’t signed up yet so you can remind them when they drop off or pick up their kids.

In this tutorial, I’ll show you how to send email notifications whenever a Google Sheets spreadsheet is edited. We’ll use Google Sheets and some Google Apps Script code to create this “application”.

Although there is a built in Notification Rules within Google Sheets, one of the limitations is that it does not actually stipulates what has changed. I subseqently found this guide for creating a script to send an email when a Google Sheet is edited, as well as this solution to sending to multiple users.

"" + "," + "";

The next step is to think about how this information can be made clearer, possibly listing the specific changes, rather than just highlighting the particular school updated.

Replied to Mass Producing Google Slides from Sheets: Scripts Are Your Friends (CogDogBlog)

To me, buying an app is serving me a fish dinner; giving me a script I can modify is a lesson in fishing. I’d rather fish. You?

Thank you so much for sharing Alan. I really like the starting point Rohe provides. I too would rather fish, than processed fish fingers.
Replied to How to Inject Data from Google Sheets Into a Google Slides Presentation | UpBuild (UpBuild)

Stop copying and pasting data from Google Sheets. Have it automatically injected into a Google Slides presentation with this Apps Script.

I always wondered about the possibility of automating the process of populating Slides using text from Sheets to create visual quotes, however I never quite ended up down that rabbit hole.

Using this script, I was able to create a start. I think that the things that I still wonder:

  • Insert an image from URL
  • Adjust the size of the text based on the length of the text in the spreadsheet
  • Replacing/updating the current slides in the deck

This is at least a start.

via Alan Levine

Bookmarked How To Connect Google Sheets To A Database, Using Apps Script – Ben Collins (Ben Collins)

Google Sheets is great for quickly spinning up dashboards and analysis, but getting raw data into Sheets from databases can be tedious.

In this post we cover a few ways to get data from your SQL database into Google Sheets.

I usually drag in data from my blog via RSS, however I wonder if this might be a cleaner method.
Replied to Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins (Ben Collins)

Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge. I posted Formula Challenge #3 — to alphabetize a string of words separated by commas using a single formula — in January 2020 and had over 150 replies! It would have been too time consuming to reply to … Continue reading Gmail Mail Merge For A Specific Label With Apps Script

Ben, I really need to find some time to dig into these ideas for automation and your work on Apps Script. Along with Martin Hawksey’s post on sending personalised images, they offer some great ideas for improving workflows.

Liked My personal how/why/what of Google Apps Script (MASHe)

Recently I had the opportunity to be interviewed with some of my fellow G Suite GDEs for a Google Apps Script marketing video that is being pulled together by Google. The final video is still in production and I look forward to seeing the results. Over the years I’ve either produced or appeared in a number of videos and the recording session for this video was definitely on a new level. Before the recording session I was given the questions I would be asked so that I could prepare some thoughts.

Bookmarked AppsScriptPulse – The latest from the Google Apps Script community (AppsScriptPulse)

Apps Script Pulse is a new space to keep up-to-date with the latest contributions from the Google Apps Script community. Pulse is designed to compliment other community spaces – with the Apps Script Stack Overflow site supporting Q&A, the Apps Script Google Groupproviding a discussion space and the new G Suite Solutions Gallery providing a space for sample solutions – Apps Script Pulse is a distribution channel for community created content as well as surfacing highlights from existing spaces.

You can visit Apps Script Pulse and start browsing the content we’ve already collected and if you’d like to receive updates in your inbox you can subscribe to our daily digest of new posts. If you have content you would like featured in Apps Script Pulse you can ping us on Twitter @AppsScriptInfo or dropping us a line at

Martin Hawksey has created a site to collate different App Script projects.
RSVPed Interested in Attending Apps Script Blastoff!

A free, introductory course teaching Google Apps Script from scratch

I have long had an interest in Google Apps Scripts. However, I have really gotten past simply appropriating other people’s code, especially Martin Hawksey’s work.

I have read a number of posts:

I look forward to this new offering. I always enjoy the way in which Collins’ breaks things down with his courses.

Bookmarked Totally Unscripted (YouTube)

Totally Unscripted is a new Google Apps Script show organized by the community for the community. Each month members of the community will talk through some new developments in Google Apps Script as well as talking about a coding project or problem.

Totally missed this and only just came upon it via Ben Collins’ newsletter.