Having some difficulties in making my Apps Script pause between iterations. What I am trying to achieve is to take a list of 'keys' from the Initiatives sheet and one at a time, paste each key into cell A2 of the Calculator sheet. This is where i need the pause to kick in, that sheet has a series of calculations etc. that take around 10 seconds to complete. Once complete I then copy the answers to the calculations from cells D5:F5 back into the Initiatives sheet in the same row that the key came from. I am by no means fluent in Apps Script (or any script for that matter) and have pieced this together from what little knowledge I have along with some web searches and a good old root around this place (thats where I found the lock. info which I can't seem to get right (if indeed that is what I need here). Hoping the hive mind can solve this one for me please.
function autoCalcTest(){
var ss = SpreadsheetApp.getActive();
var init = ss.getSheetByName('Initiatives'); //sheet containing list of keys
var lr = init.getLastRow(); // last row of keys
var data = init.getRange('A2:A'+lr).getValues(); // get values of all keys
//var copyrange = ss.getSheetByName('Calculator').getRange(5, 4, 1, 4); // range of cells to be copied
data.forEach(function(key,i){
ss.getSheetByName('Calculator').getRange(2, 1).setValue(key); // enter the key into cell A2
var lock = LockService.getScriptLock(); // Trying to make the script pause between each iteration
lock.waitLock(30000);// Trying to make the script pause between each iteration
Utilities.sleep(1000);// Trying to make the script pause between each iteration
ss.getSheetByName('Calculator').getRange('D5:F5').copyTo(ss.getSheetByName('Initiatives').getRange(i+2, 9), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); //once the pause is over, copy range D5:F5 from Calculator sheet to Initiatives (row by row according to key)
Logger.log(i,key); // Check to see if all keys are in the array - they are
lock.releaseLock();//release the lock?
});
}
