3

I have looked through a number of the questions asked on this particular question however none of the solutions I have seen appear to apply to what I am working on.

I have a script which is attached to a spreadsheet, the goal is to run this script each night to generate a master file listing of all files in the drive and present them on a spreadsheet. Due to the number of files in folders, sub-folders, sub-sub-folders (you get the idea) it is unable to complete the update to the spreadsheet within the specified time frame.

function clear_spreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['Name', 'Folder','Link', 'Last Updated']);
 }

function listFolders(folder) {
folder = folder || DriveApp.getRootFolder();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var file;
var name;
var urllink;
var row;
var currentfolder;
var lastupdated;
var files = folder.getFiles();

while ( files.hasNext() ) {
 file = files.next();
 name = file.getName();
 urllink = file.getUrl();
 currentfolder = file.getParents();
 lastupdated = file.getLastUpdated();
 sheet.appendRow( [name, folder, urllink, lastupdated]);
}

var subfolders = folder.getFolders();

while (subfolders.hasNext()) {
listFolders(subfolders.next());
}

};

function Run_Update(Folder) {
clear_spreadsheet();
listFolders();
}

Any thoughts as to how I can either streamline the code to run faster, or more likely find a way to generate a placeholder within the script and continue to run after a specified time frame until it comes to the end of its search?

EDIT: I've seen that you can utilize tokens and timers to facilitate this however its not something I am familiar with. Does anyone know the process for calling this type of function in this manner?

EDIT 2 See below for the final code which resolved the issue of exceeding the run time in this particular instance.

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: 'add your email',
      subject: "Master File Update - Complete",
      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: 'add your 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();
  }
}

1 Answer 1

2

You may be able to improve the performance by replacing individual row insertions in a loop by a batch operation post-loop (as Google themselves recommend):

var output = []; 
while (files.hasNext() ) {
 file = files.next();
 name = file.getName();
 urllink = file.getUrl();
 currentfolder = file.getParents();
 lastupdated = file.getLastUpdated();
 output.push([name, folder, urllink, lastupdated]);
}
if (output.length) {
  var lastRow = sheet.getRange.getLastRow();
  sheet.getRange(lastRow + 1, 1, output.length, 4).setValues(output);
}

Apart from that, you could try to split the task into smaller one: for example, "process the folders where the first character of the name is in the range A-E". Then set separate triggers for such functions, for different times. However, the algorithm gets a bit complicated since one should go through the subfolders under a folder that itself isn't a part of the currently processed range.

As a last resort, there's a way stop/resume the execution of a script, see this answer. You can try storing folder Id in script properties and resuming with that folder. But this also gets complicated because of the nested structure: there are file/folder iterators that you can't simply store and renew.

1
  • The optimization you provided made a significant time difference (approximately 13 records per second to 49). I am testing it now to see if it covers the full range, even if it doesn't perform through the whole list of documents I can use the answer from stopping/starting scripts to build a sequence that will cover everything needed.
    – STowslee
    Dec 13, 2016 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.