Why my script doesn't wait despite that it has Utilities.sleep()

[Mateusz Krajewski]

Hi everyone, 
This is my first message here. I am trying to write quite easy macro. Firstly maybe I will explain what it does.
It setsFormula() in 4 cells in a row then after Utilities.sleep(3000) should copy value from col7 and PASTE_VALUES in the same place, after that clearContent() of col5, col6, col8 in this row. All is in the for loop. I do not know why when I fire it, script doesn't set the formulas. All I see are blank cells. It seems to me that right after settingFormula() it clearsContent()...
Hope someone understands what I am trying to achieve. 
Script code:

function testGetIds() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('item_nameid');

var activeCellRow = spreadsheet.getActiveCell().getRow();

for (var i = activeCellRow; i < spreadsheet.getLastRow(); i++) {

var product = spreadsheet.getRange(i,3).getValue();

if (product.length > 0) {

spreadsheet.getRange(i, 5)

.setFormulaR1C1('=ADDRESS(MATCH("Market_LoadOrderSpread*";R[0]C[3]:C[3];0)+ '+i+';8;1;TRUE)');

spreadsheet.getRange(i, 6)

.setFormulaR1C1('=CONCATENATE(INDIRECT(R[0]C[-1]))');

spreadsheet.getRange(i, 7)

.setFormulaR1C1('=REGEXEXTRACT(R[0]C[-1];"Market_LoadOrderSpread..(\d*)")');

spreadsheet.getRange(i, 8)

.setFormulaR1C1('=IMPORTDATA(R[0]C[-4];";")');

Utilities.sleep(3000);

spreadsheet.getRange(i, 7)

.copyTo(spreadsheet

.getRange(i, 7), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

spreadsheet.getRange(i, 5)

.clearContent();

spreadsheet.getRange(i, 6)

.clearContent();

spreadsheet.getRange(i, 8)

.clearContent();

} else {

var ui = SpreadsheetApp.getUi();

var userAnswer = ui.alert("Nie znaleziono więcej produktów", ui.ButtonSet.OK);

if (userAnswer = ui.Button.OK) {

return;

} else {

return;

}

}

}

}

Thanks in advance!
Cheers,
Mat

[Clark Lind]

Right before the utilities.sleep(), try adding   SpreadsheetApp.flush() 

This forces the sheet to take all queued actions instead of waiting to do them in a batch all at once.

[Mateusz Krajewski]

Yep, exactly that is the answer! Thanks a lot