google sheets - copyToRange (and similar) execution at end of script?

Question

I am working on a kinda large sheet which I would like to update on a regular basis (weekly) via Google Apps Scripts. Every week I need to add a new column at the "end" (lastDataColumn) of my sheet and then move the last two colums (with Fomulas to calculate weekly relative changes) to the "end" (ie move them one column to the right). This leaves me with a blank column addressed with lastDataColumn - 2. This is where the new report data will go.

I have two functions. copyCols and getReports.

They both work fine on their own, so copyCols creates a new empty column at position lastDataColumn - 2 using the method explained above - and getReports fetches report Data from Analytics, third party APIs and other sheets and then writes these values in the column at position lastDataColumn - 2.

However, if I group these two functions in let's say my main function which I then want to trigger on a 7-day basis, copyCols seems to only execute to the point of creating a new empty column. Then getReports executes fully and writes all data in lastDataColumn - 2. But no columns were moved, so getReports overwrites last weeks data. After executing everything from getReports copyCols starts moving the rows (ie copying). This leaves me with a duplicate column of lastDataColumn - 3 (which should have last weeks data, but was overwritten with this weeks data because it was still in lastDataColumn - 2 before the execution of getReports) in lastDataColumn - 2.

To clarify: Executing copyCols and getReports afterwards (each on it's own) works perfectly fine.

Is Google Apps Script threadless? If so why does the problem described happen? Do "bulk" operations (like copying ranges) execute at the end of the script?

Code:

var today = new Date();
var start = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
var end = new Date(today.getTime() - 1 * 24 * 60 * 60 * 1000);
var dateString=Utilities.formatDate(start, Session.getScriptTimeZone(),'dd.MM.')+'-'+Utilities.formatDate(end, Session.getScriptTimeZone(),'dd.MM.')

var nc=2 //num of cols with growth rates to move

function main() {
  copyCols();
  getReports();
}

function copyCols(){
  var ss=SpreadsheetApp.openById('1dSpy7teczLwViKbfr-VjfQRuOq3iaRfSm3LghFldjZk')
  var sh_DB=ss.getSheetByName('data')
  var w=sh_DB.getLastColumn(); //width
  var h = sh_DB.getLastRow();  //heigth
  // insert new column
  sh_DB.insertColumnAfter(w);
  // copy last n cols to next col
  for (i=0;i<=nc;i++){
    sh_DB.getRange(1,w-i,h,1).copyTo(sh_DB.getRange(1,w-i+1,h,1));
  }
  sh_DB.getRange(1,w-nc+1).setValue(dateString);
}

function getReports(){
  var sh_DB=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data')
  var w=sh_DB.getLastColumn(); //width
  var h = sh_DB.getLastRow();  //heigth
  dc=sh_DB.getRange(1,w-nc); //lastDataColumn
  data = [50, 60, 870, 2];
  report = {'rows':[2,3,4,5]};
  for (i in data){
    sh_DB.getRange(report['rows'][i],w-nc).setValue(data[i]);
  } 
}

Thank you for any help provided.

Answer 1

Add SpreadsheetApp.flush() between copyCols() and getReports() in order to tell to the Google Apps Script engine to apply the changes made by the first before running the second.