I suppose the error message refers to the Maps service. Then converting to array formula won't help since you will still be calling Maps as many times. Here are some things you can do:
Don't call the Maps service unnecessarily. Right now, you are calling it a dozen times with empty destination and origin. Check that those are nonempty strings beforehand.
Switch from a custom function to a function that's triggered by form submission. That is, instead of putting =DrivingSeconds(...)
in the spreadsheet, add a trigger that will call it on form submission. Here is how this function can look like
function DrivingSeconds() {
var sheet = SpreadsheetApp.getActiveSheet();
var input = sheet.getRange("A2:A").getValues();
var output = sheet.getRange("B2:B").getValues();
for (var i = 1; i < output.length; i++) {
if (!output[i][0] && input[i-1][0] && input[i][0]) {
output[i][0] = Maps.newDirectionFinder()
.setOrigin(input[i-1][0])
.setDestination(input[i][0])
.getDirections()
.routes[0].legs[0].duration.value;
}
}
sheet.getRange("B2:B").setValues(output);
}
Notice that Maps API is invoked only if we have both origin and destination but do not have duration yet. Output:
+---+-------------------------------------------------+-----+
| | A | B |
+---+-------------------------------------------------+-----+
| 1 | Address | ETA |
| 2 | 5020 IMPERIAL AVENUE, SAN DIEGO, CA,92113 | |
| 3 | 4699 EL CAJON BLVD, SAN DIEGO, CA,92115 | 604 |
| 4 | 8620 LA MESA BLVD, LA MESA, CA,91942 | 674 |
| 5 | 2820 VIA ORANGE WAY #Q, SPRING VALLEY, CA,91978 | 638 |
+---+-------------------------------------------------+-----+
According to Simple triggers, they can't call services that require authorization to run, that explains why the script run from the Apps Script Editor but not when the spreadsheet is opened.
In order to make it able to run on open, considering that onOpen is a reserved word for simple triggers, change it's name then create a installable trigger.
If you have thousands of template copies that include the script on the question and should be updated too, bear in mind that bounded scripts can't be programmatically edited so you if you want to update them, it should be done manually.
Best Answer
You will have to loop over the sheets and use some criteria to decide whether to do the copy on a given sheet. Perhaps it would be easiest to use a regular expression to match sheet names, like this:
See https://regexone.com/ and MDN for more info.