google apps script - Updated File List

Question

I am using a script to show me new files in google drive so people can see that a file has been updated. The script worked fine with one or two users using the system but after migrating a few more we started getting errors about exceeding maximum execution time. Currently the script deletes the current list and recreates it each and every hour

function listFilesInFolder() {
    //try {
    
    // If you want a tree of any sub folder
    //var parent = DriveApp.getFoldersByName("FOLDER_NAME").next();
    
    // If you want to search from the top (root) folder
    var files = DriveApp.getFiles();
    var ss = SpreadsheetApp.openById("1XbAaoNTiRXKSUAovY-QUvnc8V2q68g7Q3z4NIH_ioTc");
    var sheet = ss.getSheetByName("Sheet1");
    var file,folder,folders,folderTop,newFiles,lastUpdated,difference;
    var j = 0;
      var data = []
      var parentFolders = []
      var newDate = new Date();
    while (files.hasNext()) {
     file = files.next();
     lastUpdated = file.getLastUpdated();
     difference = newDate.getTime() -  lastUpdated.getTime();      
     if ((difference/86400000) < 100) {
       folders = file.getParents();
       while (folders.hasNext()) {
         folder = folders.next(); 
         folders = folder.getParents();
         parentFolders.push(folder.getName())
       }
       folderTop = parentFolders[parentFolders.length -2]
       data.push (new Array());
       data[j].push(file.getName());
       data[j].push(file.getLastUpdated());
       data[j].push("open_in_new");
       data[j].push("https://drive.google.com/file/d/"+file.getId()+"/edit");
       data[j].push("folder_open");       
       if (file.getParents().hasNext())
          {
           data[j].push("https://drive.google.com/drive/u/0/folders/" + file.getParents().next().getId());
         
          }
         else 
         {
           data[j].push("https://drive.google.com/drive/"); 
         }
       data[j].push(folderTop+"@xxx.net");
       j = j+1;
      }
   }
   sheet.deleteRows(2, sheet.getMaxRows()-1)
   sheet.deleteColumns(2, sheet.getMaxColumns()-1)
   sheet.clear()
  sheet.appendRow(["File Name","Date Updated","View File","File Link", "Open Folder","Folder Link", "Cell" ]);
  sheet.appendRow(["","","HyperlinkType(D)-iconType","", "HyperlinkType(F)-iconType","", "Permissions-Hidden"] );
  var destinationRange = sheet.getRange(3, 1,j , 7);
  destinationRange.setValues(data);
    }
 //    catch (e) {
    
 //   Logger.log(e.toString());
    
 // }
 //   }





function getFilesFromFolder(parent,targetSheet,topLevelName) {  
      var files = parent.getFiles();
    var data
    var file    
  
  while (files.hasNext()) {
      file =files.next()
      data = [ 
        file.getName(),
        file.getDateCreated(),
        file.getSize(),
        file.getUrl(),
        file.getDescription(),
        file.getLastUpdated(),
        file.getMimeType(),
        topLevelName+"@xxx.net"
      ];
    targetSheet.appendRow(data);
    
            row.push([file.getId(),
                   file.getName(),
                   file.getDateCreated(),
                   file.getSize(),
                   file.getUrl(),
                   file.getDescription(),
                   file.getLastUpdated(),
                   file.getMimeType()
                   ]);
  }
}

  


function getChildFolders(parent,targetSheet,topLevelName) {
  
  var childFolders = parent.getFolders();
  while (childFolders.hasNext()) {
    var childFolder = childFolders.next(); 
    getFilesFromFolder(childFolder,targetSheet,topLevelName);
    getChildFolders(childFolder,targetSheet,topLevelName);
  }
    
    // Recursive call for any sub-folders

    
  }

Thank you.

Answer 1

Here's a script that uses DriveActivity API to monitor changes to Google Drive. The Qwikstart is here and I took that script and modified it.

I hardwired the timezone into the query filter so you'll need to address that and there's also the variable n and nl that I used to limit the number of calls so that I would hit quota limits during development.

It generates the html for the results and displays everything in a dialog.

Code.gs:

/**
* Lists activity for a Drive user.
*/
function listDriveActivity() {
  const dt=new Date();
  const dt0=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()-1);
  const dt1=Utilities.formatDate(dt0, Session.getScriptTimeZone(),"yyyy-MM-dd");
  const dt2=Utilities.formatDate(dt0, Session.getScriptTimeZone(), "HH:mm:ss")
  const dt3=dt1+"T"+dt2+"-07:00";
  const dt4=Utilities.formatDate(new Date(dt3), Session.getScriptTimeZone(), "E MM/dd/yyyy HH:mm:ss")
  
  var html='Recent Activities ' + dt4 + ' ' + Session.getScriptTimeZone();
  html+='<style>td,th{padding:2px;border:1px solid black;overflow-wrap: break-word;} table{table-layout:fixed;width:100%;}</style>';
  html+='<table>';
  html+='<tbody>';
  html+='<tr><th>Time</th><th>Actors</th><th>Actions</th><th>Targets</th></tr>';
  var token="";
  var n=0;
  var nl=10;//I needed to reduce the number of calls to not exceed quota during development
  do{
    var request = {pageSize: 10,filter:Utilities.formatString('time>="%s"',dt3)};
    var response = DriveActivity.Activity.query(request);
    var activities = response.activities;
    if (activities && activities.length > 0) {
      for (var i = 0; i < activities.length; i++) {
        var activity = activities[i];
        var time = getTimeInfo(activity);
        var actions = getActionInfo(activity.actions);
        var actors = activity.actors.map(getActorInfo);
        var targets = getTargetInfo(activity.targets);
        //html+=Utilities.formatString('<tr><td style="width:15%;">%s</td><td style="width:15%;">%s</td><td style="width:25%;">%s</td><td style="width:35%;">%s</td></tr>',time,truncated(actors),actions,targets);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',time,actors,actions,targets);
        Logger.log(JSON.stringify(activities[i]));
        if(++n > nl)break;;
      }
    } else {
      html+='<br />No Further Activity';
    }
    token=response.nextPageToken;
  }while(token!='' && n<=nl);
  
  html+='</tbody></table><br />n=' + n;
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html).setWidth(1200), "Drive Activity")
}

/** Returns a string representation of the first elements in a list. */
function truncated(array, opt_limit) {
  var limit = opt_limit || 2;
  var contents = array.slice(0, limit).join(', ');
  var more = array.length > limit ? ', ...' : '';
  return '[' + contents + more + ']';
}

/** Returns the name of a set property in an object, or else "unknown". */
function getOneOf(object) {
  for (var key in object) {
    return key;
  }
  return 'unknown';
}

function getTimeInfo(activity) {
  if ('timestamp' in activity) {
    const dt=new Date(activity.timestamp);
    return Utilities.formatDate(dt, Session.getScriptTimeZone(), "E MM/dd/yyyy HH:mm:ss") + '<br />' + activity.timestamp;
  }
  return 'unknown';
}

function getActionInfo(acts) {
  let html='';
  acts.forEach(function(d,i){
    if(i>0)html+='<br /><br />';
    html+=JSON.stringify(d)
  });
  return html;
}

/** Returns user information, or the type of user if not a known user. */
function getUserInfo(user) {
  if ('knownUser' in user) {
    var knownUser = user.knownUser;
    var isMe = knownUser.isCurrentUser || false;
    return isMe ? 'people/me' : knownUser.personName;
  }
  return getOneOf(user);
}

/** Returns actor information, or the type of actor if not a user. */
function getActorInfo(actor) {
  if ('user' in actor) {
    return getUserInfo(actor.user)
  }
  return getOneOf(actor);
}

/** Returns the type of a target and an associated title. */
function getTargetInfo(targets) {
  let html='';
  targets.forEach(function(t,i){
    if(i>0)html+='<br /><br />';
    //html+=JSON.stringify(t);
    html+='target: ' + i;
    html+=Utilities.formatString('<br />title: %s, type: %s, personName: %s, domainName: %s, id: %s',t.driveItem.title,t.driveItem.mimeType,getUserInfo(t.driveItem.owner.user),t.driveItem.owner.domain.name,t.driveItem.name.slice(6));
  });
  return html;
}