Time-triggered Google app script that lists out Google drive's folder shared link

Question

I have a google drive folder with the structure like this:

Folder/
--SubFolder1/
----1.png
----2.png
----3.png
--SubFolder2/
----4.png
----5.png

Now I need a script to list out the shared link of the SubFolders (not the file inside) and that app must automatically run once every 3-4 hours.

The shared-link-listing script I've found is below, but it list out ALL files and folders within, while on the other hand I only need to get to the 2nd level.

function onOpen() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('List Files/Folders')
    .addItem('List All Files and Folders', 'listFilesAndFolders')
    .addToUi();
};

function listFilesAndFolders(){
  var folderId = Browser.inputBox('Enter folder ID', Browser.Buttons.OK_CANCEL);
  if (folderId === "") {
    Browser.msgBox('Folder ID is invalid');
    return;
  }
  getFolderTree(folderId, true); 
};

// Get Folder Tree
function getFolderTree(folderId, listAll) {
  try {
    // Get folder by id
    var parentFolder = DriveApp.getFolderById(folderId);
    
    // Initialise the sheet
    var file, data, sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(["Full Path", "Name", "Type", "URL"]);
    
    // Get files and folders
    getChildFolders(parentFolder.getName(), parentFolder, data, sheet, listAll);
  } catch (e) {
    Logger.log(e.toString());
  }
};

// Get the list of files and folders and their metadata in recursive mode
function getChildFolders(parentName, parent, data, sheet, listAll) {
  var childFolders = parent.getFolders();
 
  // List folders inside the folder
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next();
    var folderId = childFolder.getId();
    data = [ 
      parentName + "/" + childFolder.getName(),
      childFolder.getName(),
      "Folder",
      childFolder.getUrl(),
      ];
    // Write
    sheet.appendRow(data);
    
    // List files inside the folder
    var files = childFolder.getFiles();
    while (listAll & files.hasNext()) {
      var childFile = files.next();
      data = [ 
        parentName + "/" + childFolder.getName() + "/" + childFile.getName(),
        childFile.getName(),
        "Files",
        childFile.getUrl(),
      ];
      // Write
      sheet.appendRow(data);
    }
    // Recursive call of the subfolder
    getChildFolders(parentName + "/" + childFolder.getName(), childFolder, data, sheet, listAll);  
  }
};

For the time-triggered feature, I've looked for the Installable Triggers on the Google dev page, but honestly I don't have any clue what they mean in there.

Answer 1

You want to create a link to subFolders of a Google Drive folder

function linkSubFolders() {

  // List all sub-folders in a single folder on Google Drive
  // declare the folder name
  var foldername = "<<Enter name of Parent Folder>>"
  
  // declare this sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheetname = "<<Enter name of output sheet>>"
  var sheet = ss.getSheetByName(sheetname)

  // get the last row of data
  var lr = sheet.getLastRow()

  // create an array to save data
  var tempArray = []
  
  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  var folders = DriveApp.getFoldersByName(foldername)
  var foldersnext = folders.next();
  // get the subfolders
  var subfolders = foldersnext.getFolders();
  // loop on the SubFolder list
  while (subfolders.hasNext()) {
    var mysubfolders = subfolders.next();
    var subFolderName = mysubfolders.getName(); 
    var subFolderURL = mysubfolders.getUrl() 
    var myLink = '=HYPERLINK(\"' + subFolderURL+ '\",\"' + subFolderName + '\")'
    var subFolderDate = mysubfolders.getDateCreated()  

    // test for first pass
    if (lr == 2){ 
      //Logger.log("first pass; use all subfolders")
      tempArray.push([subFolderName,subFolderDate,myLink])
    }else if (lr > 2){
      var lastRunDate = sheet.getRange("B1").getValue()
      if (subFolderDate.valueOf() > lastRunDate.valueOf()) {
        // Logger.log("DEBUG: subsequent pass: New subfolder: mycreate:"+subFolderDate.valueOf()+" > lastrun:"+lastRunDate.valueOf())
        tempArray.push([subFolderName,subFolderDate,myLink])
      }
      else{
        // Logger.log("DEBUG: subsequent pass: Old subfolder: create:"+subFolderDate.valueOf()+" = lastrun:"+lastRunDate.valueOf())
     }
    }
  }


  // test for first pass
  // set the row number to save data
  if (lr == 2){
    var row = 3
  }else{
    var row = +lr+1
  }
   
  //save the run date
  sheet.getRange(1,2).setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss")
  // save the subfolders
  if (tempArray.length > 0){
    // Logger.log("DEBUG: output range = "+sheet.getRange(row,1,tempArray.length,3).getA1Notation())
    sheet.getRange(row,1,tempArray.length,3).setValues(tempArray)
  } else{
    // Logger.log("DEBUG: no data - do nothing")
  }
}


  // test for first pass
  // set the row number to save data
  if (lr == 2){
    var row = 3
  }else{
    var row = +lr+1
  }
   
  //save the run date
  sheet.getRange(1,2).setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss")
  // save the subfolders
  if (tempArray.length > 0){
    Logger.log("DEBUG: output range = "+sheet.getRange(row,1,tempArray.length,3).getA1Notation())
    sheet.getRange(row,1,tempArray.length,3).setValues(tempArray)
  } else{
    Logger.log("DEBUG: no data - do nothing")
  }
}

---

To-do

• Create a manual header in
  • Cell A1: "Last run date =>"
  • Cell A2: "Subfolder Name"
  • Cell B2: "Date Created"
  • Cell C2: "Link"
• var foldername = "<<Enter name of Parent Folder>>": enter name of parent folder
• var sheetname = "<<Enter name of output sheet>>": enter output sheet name

Script logic

• The date of each run is saved to Cell B1.
• when a subFolder is found, getDateCreated() is compared to the last run date.
  • if the "last run" > "Created", then the subFolder is already in the list and it is ignored
  • if the "last run" < "Created", then the subFolder is new and it is added to the list.

---

getFoldersByName(name) vs getFolderById(id)

It IS possible to use getFolderById(id) rather than getFoldersByName(name) but it isn't a straight swap.

• getFoldersByName(name) returns a FolderIterator. So it needs next() to return the folder
• OTOH, getFolderById(id) returns the folder with the given ID.

Modification - Replace

  // getFoldersByName = Gets a collection of all folders in the user's Drive that have the given name.
  var folders = DriveApp.getFoldersByName(foldername)
  var foldersnext = folders.next();
  // get the subfolders
  var subfolders = foldersnext.getFolders();

Modification - Substitution

  // getFolderByID = Gets the folder with the given ID
  var folder = DriveApp.getFolderById(folderID)
  // get the subfolders
  var subfolders = folder.getFolders();

---

SAMPLE