https://www.pbainbridge.co.uk/2022/09/search-google-drive-for-shared-with-me.html

Search Google Drive for Shared with me.gs

/*
Function to search for all files within the Google Drive 'Shared with me' space
and output the results into a Google Sheet.
*/

/**
 * @OnlyCurrentDoc
 */

function searchDrive() {

  // 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 maximum runtime
    var runtime = '5.5';

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


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


    // 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 files ...');

      // search Google Drive for files containing text
      var files = DriveApp.searchFiles(
        'sharedWithMe'
      );

    }
    else {

      // Token does exist in Script Properties

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

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

    }



    // create variable to use as Counter for check of files searched
    var fileCounter = 0;

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


    // loop through each returned file *************************
    while (files.hasNext()) {

      // get file
      var file = files.next();


      // check to make sure file not owned by current user ***************
      try {
        // get file owner
        var fileOwner = file.getOwner().getEmail();

        // get email address of active user running the tool
        var activeUser = Session.getActiveUser().getEmail();

        // compare 2 email addresses
        if (fileOwner == activeUser) {
          var userOwnsFile = true;
        }
        else {
          var userOwnsFile = false;
        }
      }
      catch (e) {
        // possible file permission issue
        fileOwner = "unknown";

        var userOwnsFile = false;
      }
      // check to make sure file not owned by current user ***************


      // only proceed if user does not own the file *************************
      if (userOwnsFile == false) {

        // get file name
        var fileName = file.getName();
        fileName = fileName.replace(/"/g, '');  // remove double quotes which breaks hyperlink

        // get file ID
        var fileID = file.getId();

        // get file URL
        var fileUrl = file.getUrl();
        // create nice hyperlink for pasting in sheet
        var hyperlink = '=HYPERLINK("' + fileUrl + '","' + fileName + '")';

        // get file type
        var fileType = file.getMimeType();

        // get date created
        var fileCreationDate = file.getDateCreated();

        // get last updated
        var fileLastUpdated = file.getLastUpdated();


        // get Parent folder URL
        var parentFolder = file.getParents();
        while (parentFolder.hasNext()) {

          // get Parent Folder
          var parent = parentFolder.next();

          // get folder URL
          var parentFolderUrl = parent.getUrl();

        }


        // collate row data and push into array for later adding to Sheet
        var rowData = [hyperlink, fileID, fileType, fileCreationDate, fileLastUpdated, parentFolderUrl, fileOwner];
        fileData.push(rowData);

        // increment file Counter by 1
        fileCounter++;

      }
      else {
        // ignore file as user owns it and doesn't need including
      }
      // only proceed if user does not own the file *************************



      // 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 file
        var continuationToken = files.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 files
      }


    }
    // loop through each returned file *************************


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


  }
  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 file/row data to Files sheet in one go
    var lastRow = filesSheet.getLastRow() + 1;
    var arrayLength = fileData.length;
    var arrayWidth = fileData[0].length;
    filesSheet.getRange(lastRow, 1, arrayLength, arrayWidth).setValues(fileData);


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

      // yes there are files still left to handle

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

      // no errors have occurred in the script
      var popupTitle = 'Script Complete - ' + fileCounter + ' files found';
      var popupMessage = 'No errors. There are files 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 files 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 files have been handled.');

        // no errors have occurred in the script
        var popupTitle = 'Script Complete - ' + fileCounter + ' files found';
        var popupMessage = 'No errors. All files 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.');
  }

}


/*
Function to look for 'Continuation Token' in Script 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.
*/

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.
*/

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 'Files' sheet of any information.
Also delete Script Property.
*/

function reset() {

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

  // get 'Files' sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Files');

  // get rows excluding Header
  var lastCol = sheet.getLastColumn();
  var lastRow = sheet.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', 'searchDrive') // 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);

}