Spreadsheet generated through Google Drive file iteration

Question

I put together a script which goes through all of the files in Google Drive, and writes a spreadsheet with all of the files with their name, containing folder, URL, and date last updated.

function Start_Up() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['Name', 'Folder', 'URL', 'Updated'])

  var start_time = new Date();
    MailApp.sendEmail({
      to: 'insert e-mail',
      subject: "Master File Update - Started",
      htmlBody: "The update of the master equipment document spreadsheet was started at: " + start_time});

  var Script_Properties = PropertiesService.getScriptProperties();
  Script_Properties.deleteAllProperties();
  Iterate_Files();
}

function Delete_Triggers() {
  var Triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < Triggers.length; i++) {
    ScriptApp.deleteTrigger(Triggers[i]);    
  }
}

function Iterate_Files() {
  var Script_Properties = PropertiesService.getScriptProperties();
  var continuationToken = Script_Properties.getProperty('C_Token');

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  Delete_Triggers();

  var Start_Time = new Date().getTime();
  var Seconds = 0; 

  if (continuationToken) {
    var files = DriveApp.continueFileIterator(continuationToken);
  } else {
    var files = DriveApp.getFiles();
  }

  var output = [];

  while (files.hasNext() && Seconds < 240) {
    var file = files.next();
    var Current_Time = new Date().getTime();
    var Seconds = Math.floor((Current_Time - Start_Time)/1000);

    var file_name = file.getName();
    if (file.getParents().hasNext()) {
      var folder_name = file.getParents().next().getName();
    } else {
      folder_name = 'Root';
    }
    var file_url = file.getUrl();
    var file_last_updated = file.getLastUpdated();
    output.push([file_name, folder_name , file_url, file_last_updated]);
  }
  if (output.length) {
    var last_row = sheet.getLastRow();
    sheet.getRange(last_row +1, 1, output.length, 4).setValues(output);
  }

  if (files.hasNext()) {
    var triggerID = null;
    continuationToken = files.getContinuationToken();
    Script_Properties.setProperty('C_Token', continuationToken);
    ScriptApp.newTrigger('Iterate_Files')
    .timeBased()
    .after(1 * 60 * 1000)
    .create();
  } else {
    Script_Properties.deleteAllProperties();
    var end_time = new Date();
    MailApp.sendEmail({
      to: 'insert email',
      subject: "Master File Update - Complete",
      htmlBody: "The update of the master equipment document spreadsheet was completed at: " + end_time});

    ScriptApp.newTrigger('Start_Up')
    .timeBased()
    .everyDays(1)
    .atHour(0)
    .create();
  }
}

It currently runs through 50,000+ files in a large number of folders and I am hoping that some of you may see areas which I may be able to improve performance in order to reduce run time.

** The sheet does not necessarily need to be cleared and re-created as long as there is a viable method of tracking which files have been indexed previously.

Answer 1

Tracking Previously Indexed Files

From my tests it appears that DriveApp.getFiles() returns the files in the order that they were last modified. This is not guaranteed in the Google Scripts specifications; however, this information could be used to drastically speed up your script. Just a few things need to be considered.

Storing the last time the script was run

You could store the time you last ran the script as a ScriptProperty, which you could get right before you delete them all at the start. Then you could iterate through the files until you find one with a file_last_updated date prior to when you last ran the script.

while (files.hasNext() && Seconds < 240) {
  var file = files.next();

  var file_last_updated = file.getLastUpdated();

  if (file_last_updated > Last_Time_Script_Was_Run) {
    // find file and update it
    // if not found then add new one
  }
}

Permanently deleted files

Another thing to note is that if you only check the most recently modified files your script will never notice when a file has been permanently deleted. The only way files are permanently deleted is when they are deleted from the trash (or by an authorized script). You could keep track of these by occassionally rechecking every single file in your drive, or by only permanently deleting files with a separate script that updates your sheet of drive files. Look into this SO question: Permenantly Deleting Files for more info.

Updating files on the sheet

You'll need to add the file id to your spreadsheet so that the script will be able to find the file data it needs to update. Array lookup may be too slow since you have so many files; you could use an object to map the file id to its index. But try both to be sure.

function Start_Up() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var fileData = sheet.getDataRange().getValues();

  fileData.shift(); // remove header row
  // say ID is the 4th index

  var indexMap = {};
  for (var i = 0; i < fileData.length; i++) {
    indexMap[fileData[i][4]] = i; // map the id to the index
  }

  Iterate_Files(fileData, indexMap);
}

function Iterate_Files(fileData, indexMap) {
  // ...

  // when updating a file
  var id = file.getId();

  if (indexMap[id] === undefined) { // add new file
    fileData.push([...]);
  }
  else { // update existing file
    fileData[indexMap[id]] = [...];
  }

  // ...
}

---

Minor optimizations

You can eke out a little extra performance by changing how you check the time. I got a 10 second speed up checking ~1200 files by doing the following.

var file,
    file_name,
    folder_name,
    file_url,
    file_last_updated;

while (files.hasNext() && Date.now() - Start_Time < 240000) {
  file = files.next();

  file_name = file.getName();
  if (file.getParents().hasNext()) {
    folder_name = file.getParents().next().getName();
  } else {
    folder_name = 'Root';
  }
  file_url = file.getUrl();
  file_last_updated = file.getLastUpdated();
  output.push([file_name, folder_name , file_url, file_last_updated]);
}