This error occurs when the script runs for more than 6 minutes.
To address this issue, consider limiting the time-consuming part of your script (such as the for loop) to 5 minutes. If it still does not complete within this timeframe, create a trigger to continue the loop in a separate instance.
Script:
function addressToPosition() {
// Identify a cell containing an address followed by two blank spaces
var sheet = SpreadsheetApp.getActiveSheet();
...
var options = {
muteHttpExceptions: true,
contentType: "application/json",
};
// Retrieve the last processed row value or set to 0 if not available
var continueRow = ScriptProperties.getProperty("lastRow") || 0;
var startTime = Date.now();
var resume = true;
for (addressRow = ++continueRow; addressRow <= cells.getNumRows(); ++addressRow) {
var address = cells.getCell(addressRow, addressColumn).getValue();
...
// Stop the loop if it exceeds 5 minutes
if ((Date.now() - startTime) >= 300000) {
// Store the last processed row and exit the loop
ScriptProperties.setProperty("lastRow", addressRow)
break;
}
// Prevent triggering the next run if the last row is reached
else if (addressRow == cells.getNumRows())
resume = false;
}
// Continue the loop if addressRow is less than getNumRows()
if (resume) {
// Set a trigger to resume the function after 1 second in a new instance
var next = ScriptApp.newTrigger("addressToPosition").timeBased();
next.after(1000).create();
}
}
Apply the same approach to optimize your other functions.