google sheets - .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();

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();