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();
}
}