I have a Apps Script that I'm having users run to delete a list of identified files from their Google Drive. The entire list is in a Google Sheet and spans 66K rows. Some of these users have upwards of 5K items each, but most are hitting the App Script runtime limit.
I'm looking for a way to either optimize my current script to be more efficient in the read/action of each item, iterate over already actioned/trashed items, or looking for a way for the script to auto-pickup/kickoff itself again to continue deleting items.
Currently perform as such:
- User visits webapp front end I've written and hit the submit button.
- Button triggers the read/trash action {deleteFile()} grabbing Session.getactiveuser() and starts running down the 66k list looking for a match of email addresses.
- If items complete within the execution time, users get a prompt that they are done and can exit.
- If not, user gets timeout errors and asked to try again.
EDIT: I'm trying to implement a simple continuation token using the "Working example (linear iterator)" from this post, but I keep getting a "Exception: Invalid argument: continuationToken" error, not sure how to get around this. Code block edited to reflect new code
const sheetID = "IDgoeshere"; //sheet with ids and user names
const logSheetId = "IDgoeshere"; //sheet to record user subbmision
const sheetName = 'Sheet1' //name of the sheet in the doc
const sheetName2 = 'Sheet1'
const emailID = Session.getActiveUser().getEmail();
var userProperties = PropertiesService.getUserProperties();
var continuationToken1 = userProperties.getProperty('CONTINUATION_TOKEN');
var start = new Date();
var end = new Date();
var maxTime = 1000 * 60 * 4;
function doGet() {
return HtmlService.createHtmlOutputFromFile('index1');
}
function getEmailFromWebAPP(element){
var getEmailID = Session.getActiveUser().getEmail();
deleteFile();
return getEmailID;
};
function deleteFile() {
const spreadsheet = SpreadsheetApp.openById(sheetID);
const sheet = spreadsheet.getSheetByName(sheetName);
const lastRow = sheet.getLastRow();
const values = sheet.getRange(2, 4, lastRow - 1, 3).getValues();
values.forEach((row, index) => {
const owner = row[0];
const id = row[2];
if (continuationToken == null) { // first time execution, get all files from Drive
if (emailID == row[1]) {
try {
var file = DriveApp.getFileById(row[2])
if (file.isTrashed == true) {
console.info('was trashed');
row++;
}
else {
file.setTrashed(true);
}
//file.setTrashed(true);
//console.info(`Trashed ${id} belonging to ${owner}`);
}
catch (e) {
console.info(`Unable to find file with id ${id}`);
}
}
else {
// not the first time, pick up where we left off
var files = DriveApp.continueFileIterator(continuationToken1); ***//errors out here***
}
}
while (files.hasNext() && end.getTime() - start.getTime() <= maxTime) {
var file = files.next();
Logger.log(file.getName());
end = new Date();
}
if (files.hasNext()) {
var continuationToken = files.getContinuationToken();
userProperties.setProperty('CONTINUATION_TOKEN', continuationToken);
}
else {
// Delete the token
PropertiesService.getUserProperties().deleteProperty('CONTINUATION_TOKEN');
}
});
const spreadsheet2 = SpreadsheetApp.openById(logSheetId);
const sheet2 = spreadsheet2.getSheetByName(sheetName2);
const datetime = new Date();
sheet2.appendRow([datetime, emailID]);
console.info(`All items deleted, you may close this tab now.`);
}
Thanks,