Running Long Running Scripts

THIS IS HOW YOU (OR I) SOLVE THIS ISSUE.

This is how I make scripts that run for days...weeks even...bearing in mind I don't oupace other quotas etc.

First, imagine there's some process I need to do to a particular row, such as check it, and if it's ready then change its status or something.... doesn't matter what the function does.... just that it does it to a given rowIndex row.

Create a Code tab and create a function like this...

function prcocessRow( rowIndex, yourParam1, yourParam2 etc){
//do your stuff here...
}

... what goes in yourParam1 is up to you.... you might not need them at all

Here's an example that I'm sure you can modify....

Now you need a Script that ...

a. stores the currentRow in the Script properies.... so when the script runs again, it knows which row to start at.
b. When the script is running it checks to see how much time it has available (then takes a little off for breathing space)
c. When it runs out of time, it creates a Trigger that runs a function which looks at "a" and runs every TIMEOUT_MINUTES ... in my case it 30 minutes...
d. If all the rows have been done, then delete the Trigger and sends the user a report...
e. you also need a startFullProcess() function, just to get things running.

Here's an example that I'm sure you can modify....

// --- CONFIGURATION ---
// The name of the function the trigger will call.
const TRIGGER_FUNCTION_NAME = "continueProcessing";
const TIME_LIMIT_MINUTES = 20;
const RUN_EVERY_MINUTES = 30 // For the Trigger.
var documentsMadeCount = 0 //tally for the user report at the end


// Runs ONCE manually to start the entire process. It clears any old state and creates the 30-minute trigger.
function startFullProcess() {
  Logger.log("Starting new process...");
  PropertiesService.getUserProperties().deleteAllProperties(); // Clear any state from a previous run  
  deleteTrigger();// Remove any old triggers to prevent duplicates 
  createTrigger();// Create the trigger that will run the script every 30 minutes
  Logger.log("Trigger created. Running first batch now.");
  continueProcessing();// Run the main processing function for the first time
}


/** // --- 2. MAIN TRIGGERED FUNCTION ---
 * This is the main function called by the trigger.
 * It picks up where it left off, processes rows one-by-one, and stops when the time limit is near.
 */
function continueProcessing() {
  const properties = PropertiesService.getUserProperties();
  const startTime = new Date().getTime();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Marking")
  var sheetName = sheet.getName()
  var lastRow = sheet.getLastRow()
  var date = Utilities.formatDate(new Date(), "Europe/London", "dd/MM/yyyy"); // fucking .toLocalDateBullshit()
  let rowIndex = parseInt(properties.getProperty('currentRowIndex') || '2'); // Assuming 1-based row indexing


  try {
    // LOAD THE GLOBAL ITEMS
    var destinationFolder = getDestinationFolder() // Get from Admin sheet
    var templateDocument = getTemplateDocument()
    // END LOAD GLOBALS


    //rowIndex = 2; // Reset row index for the next sheet
    properties.setProperty('currentRowIndex', rowIndex);


    Logger.log(`Processing sheet: "${sheetName}"  Starting from row ${rowIndex}.`);// DO A SINGLE ROW LOGIC HERE!


    // Loop while there are rows left to process on this sheet
    while (rowIndex <= lastRow) {
      processRow(rowIndex, sheet, destinationFolder, templateDocument, date)
      documentsMadeCount ++
      // Increment row index for the next loop
      rowIndex++;


      const timeElapsed = (new Date().getTime() - startTime) / 1000 / 60;
      if (timeElapsed > TIME_LIMIT_MINUTES) {
        properties.setProperty('currentRowIndex', rowIndex);
        Logger.log(`Time limit reached. Pausing. Next run will start at ${sheetName}, row ${rowIndex}.`);
        return; // Stop execution. Trigger will run again in 30 mins.
      }
    }


    // If we're here, the sheet is finished.
    Logger.log(`Finished sheet: "${sheetName}".`);
    rowIndex = 2; // Reset row index for the next sheet
    properties.setProperty('currentRowIndex', rowIndex);


    // If the 'for' loop completes, all sheets are done.
    Logger.log("All sheets processed successfully. Deleting trigger.");
    deleteTrigger();
    properties.deleteAllProperties(); // Clean up state


    // Send email report for the user running the code
    var email = Session.getActiveUser().getEmail()
    var url = ss.getUrl()
    var body = `
    <p>The creation of <b>${documentsMadeCount}</b> The Marking AppsScript has finished.</p>
    <p>Run on: ${date}</p>
    <p>See the results in the <a href="${url}"> FORMATIVE MARKING</a> sheet.</p>`
    var options = { noReply: true, htmlBody: body }
    MailApp.sendEmail(email, "The FORMATIVE MARKING AppsScript Has Finished", '', options)
    Logger.log(`Email sent to ${email}`)
    ss.toast("Finished!")
  } catch (e) {
    Logger.log(`Error encountered: ${e}. Script will retry at next trigger.`);
    // Note: We don't delete the trigger. We want it to try again.
    // We also don't delete properties, so it retries the same batch.
  }
}


// --- 4. TRIGGER HELPER FUNCTIONS ---
function createTrigger() {
  // Ensure no other triggers exist
  deleteTrigger();


  ScriptApp.newTrigger(TRIGGER_FUNCTION_NAME)
    .timeBased()
    .everyMinutes(RUN_EVERY_MINUTES)
    .create();
}


function deleteTrigger() {
  const allTriggers = ScriptApp.getProjectTriggers();
  for (const trigger of allTriggers) {
    if (trigger.getHandlerFunction() === TRIGGER_FUNCTION_NAME) {
      ScriptApp.deleteTrigger(trigger);
    }
  }
}