0

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:

  1. User visits webapp front end I've written and hit the submit button.
  2. Button triggers the read/trash action {deleteFile()} grabbing Session.getactiveuser() and starts running down the 66k list looking for a match of email addresses.
  3. If items complete within the execution time, users get a prompt that they are done and can exit.
  4. 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,

levenoples
  • 11
  • 3

0 Answers0