https://www.pbainbridge.co.uk/2021/11/search-google-drive-folders-for.html

Search Google Drive folders for creation & modified dates.gs

/*
Function to iterate through 1 level of sub-folders in a
Google/Shared Drive folder and extract creation/modified dates.
*/

function searchFolders() {

  try {
    // log start of script in sheet
    logEvent('Starting script.');


    // set error variable flag to true as no problems
    var noErrors = true;


    // get spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // display Toast notification to inform user
    ss.toast('Running ...', 'Script starting');


    // run Function to check Script Properties for 'Continuation Token'
    var continuationToken = getScriptProperties();

    // check no error getting Script Properties before proceeding ***********
    if (continuationToken !== false) {


      // get start time so can manage duration of script and avoid timeout
      var startTime = new Date().getTime();


      // get Welcome sheet
      var welcomeSheet = ss.getSheetByName('Welcome');

      // get Parent folder ID
      var parentFolderId = welcomeSheet.getRange(6, 2).getValue().trim();


      // set maximum runtime in minutes
      var runtime = '5.5';

      // set the maximum runtime of the overall script (5.5 minutes currently)
      var maxRunningTimeMS = runtime * 60 * 1000;


      // get Folders sheet for adding file information to
      var foldersSheet = ss.getSheetByName('Folders');


      // check if 'Continuation Token' exists to determine if continuing search
      if (continuationToken === null) {

        // no Token exists so proceed as first time running the script:

        // log message in sheet
        logEvent('No Continuation Token exists. Searching folders ...');

        // get Parent Google Drive folder
        var parentFolder = DriveApp.getFolderById(parentFolderId).getFolders();

      }
      else {

        // Token does exist in Script Properties

        // log message in sheet
        logEvent('Continuation Token does exist. Searching folders ...');

        // continue iterating through Drive folders with Token
        var parentFolder = DriveApp.continueFileIterator(continuationToken);

      }



      // create variable to use as Counter for check of folders searched
      var folderCounter = 0;

      // create emtpy array for pushing folder data into to append to Sheet at very end
      var folderData = [];


      // loop through each returned folder *************************
      while (parentFolder.hasNext()) {

        // get folder
        var folder = parentFolder.next();

        // get folder name
        var folderName = folder.getName();

        // get folder ID
        var folderID = folder.getId();

        // get folder URL
        var folderUrl = folder.getUrl();
        // create nice hyperlink for pasting in sheet
        var hyperlink = '=HYPERLINK("' + folderUrl + '","' + folderName + '")';

        // get date created
        var folderCreationDate = folder.getDateCreated();

        // get last updated
        var folderLastUpdated = folder.getLastUpdated();


        // collate row data and push into array for later adding to Sheet
        var rowData = [hyperlink, folderCreationDate, folderLastUpdated, folderID];
        folderData.push(rowData);

        // increment folder Counter by 1
        folderCounter++;



        // perform runtime check ************************************************

        // get current time
        var endTime = new Date().getTime();

        // find elapsed time by subtracting from start time
        var elapsedTime = endTime - startTime;

        // check against maximum runtime
        var timeLimitExceeded = elapsedTime >= maxRunningTimeMS;

        // perform runtime check ************************************************



        // check status of runtime check
        if (timeLimitExceeded) {

          // runtime has been met/exceeded, store 'Continuation Token' so can resume later
          // log message in sheet
          logEvent('Runtime has been met/exceeded.');

          // get Token from last folder
          var continuationToken = parentFolder.getContinuationToken();


          /*
          run Function to add Token to Script Properies for use later.
          capture in error variable as this is the last step in process anyway,
          so no matter what will be using 'break'
          */
          noErrors = setScriptProperties(continuationToken);

          // break out of loop to prevent script from continuing
          break;

        }
        else {
          // runtime has not been met/exceeded, script can continue looping through folders
        }


      }
      // loop through each returned folder *************************


      // log message in sheet
      logEvent('Number of folders sorted through is: ' + folderCounter);


    }
    else {
      // there has been an error getting Script Properties

      // set error variable flag to false as problem
      var noErrors = false;
    }
    // check no error getting Script Properties before proceeding ***********



    // check status of error variable flag to determine final steps/popup
    if (noErrors) {


      // now append folder/row data to Folders sheet in one go
      var lastRow = foldersSheet.getLastRow() + 1;
      var arrayLength = folderData.length;
      var arrayWidth = folderData[0].length;
      foldersSheet.getRange(lastRow, 1, arrayLength, arrayWidth).setValues(folderData);


      // check if there are still folders left to handle
      if (parentFolder.hasNext()) {

        // yes there are folders still left to handle

        // log message in sheet
        logEvent('There are still folders to handle.');

        // no errors have occurred in the script
        var popupTitle = 'Script Complete - ' + folderCounter + ' folders found';
        var popupMessage = 'No errors. There are folders still to search however so you may need to run this tool again.';
        htmlPopup(popupTitle, popupMessage);

        // log message in sheet
        logEvent('Script completed without errors.');

      }
      else {

        // no all folders have been handled

        // delete Script Properties of Token as no longer required
        noErrors = deleteScriptProperties('CONTINUATION_TOKEN');

        // check no error deleting Script Property before proceeding ***********
        if (noErrors) {

          // log message in sheet
          logEvent('All folders have been handled.');

          // no errors have occurred in the script
          var popupTitle = 'Script Complete - ' + folderCounter + ' folders found';
          var popupMessage = 'No errors. All folders have been searched.';
          htmlPopup(popupTitle, popupMessage);

          // log message in sheet
          logEvent('Script completed without errors.');

        }
        else {
          // error deleting Script Property - do nothing
        }
        // check no error deleting Script Properties before proceeding ***********

      }


    }
    else {
      // do nothing as the relevant 'catches' already display a user popup

      // log message in sheet
      logEvent('Script completed with errors.');
    }
  }
  catch (err) {
    // log error in sheet
    logEvent("Problem with 'searchFolders' Function" + err);

    // display user popup to inform of error
    var popupTitle = "'searchFolders' Function Error";
    var popupMessage = 'Error details are: ' + err;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }
}


/*
Function to look for 'Continuation Token' in Script Properties.

https://developers.google.com/apps-script/guides/properties
*/

function getScriptProperties() {

  try {

    // log message in sheet
    logEvent('Getting Script Properties.');

    // access Script Properties
    var scriptProperties = PropertiesService.getScriptProperties();

    // look for 'Continuation Token' Property
    var continuationToken = scriptProperties.getProperty('CONTINUATION_TOKEN');
    Logger.log('continuationToken is: ' + continuationToken);

    // return value to Parent Function
    return continuationToken;
  }
  catch (err) {
    // log error in sheet
    logEvent('Problem getting Script Properties: ' + err);

    // display user popup to inform of error
    var popupTitle = 'Get Script Properties error';
    var popupMessage = 'Problem getting Script Properties: ' + err;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }

}


/*
Function to set 'Continuation Token' in Script Properties.

https://developers.google.com/apps-script/guides/properties
*/

function setScriptProperties(continuationToken) {

  try {

    // log message in sheet
    logEvent('Setting Script Properties.');

    // access Script Properties
    var scriptProperties = PropertiesService.getScriptProperties();

    // set 'Continuation Token'  Script Property
    var continuationToken = scriptProperties.setProperty('CONTINUATION_TOKEN', continuationToken);

    // log message in sheet
    logEvent('Successfully set Script Properties.');

    // return true to Parent Function
    return true;
  }
  catch (err) {
    // log error in sheet
    logEvent('Problem setting Script Property: ' + err);

    // display user popup to inform of error
    var popupTitle = 'Set Script Property error';
    var popupMessage = 'Problem setting Script Property: ' + err;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }

}


/*
Function to delete 'Continuation Token' in Script Properties.

https://developers.google.com/apps-script/guides/properties
*/

function deleteScriptProperties(property) {

  try {

    // log message in sheet
    logEvent('Deleting Script Properties.');

    // access Script Properties
    var scriptProperties = PropertiesService.getScriptProperties();

    // delete 'Continuation Token' Property
    scriptProperties.deleteProperty(property);

    // log message in sheet
    logEvent('Successfully deleted Script Properties.');

    // return true to Parent Function
    return true;
  }
  catch (err) {
    // log error in sheet
    logEvent('Problem deleting Script Property: ' + err);

    // display user popup to inform of error
    var popupTitle = 'Delete Script Property error';
    var popupMessage = 'Problem deleting Script Property Token: ' + err;
    htmlPopup(popupTitle, popupMessage);

    // return false to Parent Function
    return false;
  }

}


/*
Function to clear Google Sheet of data.
Also delete Script Property.
*/

function reset() {

  // log message in sheet
  logEvent('Running Reset Function.');

  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();


  // get 'Welcome' sheet
  var welcomeSheet = ss.getSheetByName('Welcome');

  // clear folder ID box
  welcomeSheet.getRange(6, 2).clearContent();


  // get 'Folders' sheet
  var foldersSheet = ss.getSheetByName('Folders');

  // get rows excluding Header
  var lastCol = foldersSheet.getLastColumn();
  var lastRow = foldersSheet.getLastRow();
  sheet.getRange(2, 1, lastRow, lastCol).clearContent();


  // run Function to delete Script Property
  deleteScriptProperties('CONTINUATION_TOKEN');


  // log message in sheet
  logEvent('Completed Reset Function.');

}


/*
Create Menu item for running Functions
*/

function onOpen() {

  // add a custom menu to the spreadsheet
  SpreadsheetApp.getUi()
    .createMenu('Admin')
    //.addItem('Search Google Drive folders', 'searchFolders') // label for menu item, name of function to run.
    .addItem('Reset sheet', 'reset') // label for menu item, name of function to run.
    .addToUi();

}


/*
Function to output information to the 'Log' sheet.
*/

function logEvent(action) {

  // get the user running the script
  var theUser = Session.getActiveUser().getEmail();

  // get the relevant spreadsheet to output log details
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var logSheet = ss.getSheetByName('Log');

  // create and format a timestamp
  var dateTime = new Date();
  var timeZone = ss.getSpreadsheetTimeZone();
  var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");

  // create array of data for pasting into log sheet
  var logData = [niceDateTime, theUser, action];

  // append details into next row of log sheet
  logSheet.appendRow(logData);

}


/*
Display a modal dialog box with custom HtmlService content.
Does not suspend the script.
*/

function htmlPopup(popupTitle, popupMessage) {

  var htmlOutput = HtmlService
    .createHtmlOutput(popupMessage)
    .setWidth(360)
    .setHeight(180);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);

}