.setValue is only executed when the script is finished

Question

D I have a problem with my Script. a part of my script:

    var a = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("A");
    var b = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B");

    a.getRange("A14").setValue("external File");
    Utilities.sleep(2000);
    a.getRange("A14:C29").copyTo(b.getRange("A1:C15"), {contentsOnly:true});
    a.getRange("A14:C29").clearContent();

now my only problem is,the Value("external File") will not be set, my script set this value first when the script stops. Is there a posible methode to solve it, pls hold it eZ im not that well at google script :-D

Answer 1

To elaborate on @Jack Brown's comment, when both writing to and reading from the Spreadsheet interface, Google Apps Script does not necessarily immediately perform the write - it will attempt to optimize calls over the Spreadsheet interface to minimize resources needed. Using SpreadsheetApp.flush() instructs the Apps Script engine to perform any pending changes to the spreadsheet (writes, calculations of cell formulas due to newly-written data, etc).

OP's snippet would then be:

var a = SpreadsheetApp.getActive().getSheetByName("A");
var b = SpreadsheetApp.getActive().getSheetByName("B");

a.getRange("A14").setValue("external File");

// Force the above value to be written (and any cells that refer to A14 to update).
SpreadsheetApp.flush();

// Without flush(), Apps Script may wait until making these calls to perform the write.
a.getRange("A14:C29").copyTo(b.getRange("A1:C16"), {contentsOnly: true});
a.getRange("A14:C29").clearContent();