Google App Script: Invoked function executing before Main function has finished

Question

I have noticed that a secondary function, called from inside a main function, is trying to execute before the main function is done.

I'll graph the desired process in order to make it easier to understand.

As it shows, the script is trying to execute step 3 but for some reason it hasn't finished sending the information to the second sheet and it crushes.

The code for step 1:

function GrabarVenta(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spsheet_destino = SpreadsheetApp.openById("1WudWswy8xWF02jahGILZxEGWcBK4kKcN1xz6cWCNrzs");

  var origen = spreadsheet.getSheetByName("BackEndVentaCxC"); //Origin of information
  var soporte = spreadsheet.getSheetByName("BackEndPAX"); //support sheet for some variables..

  var ventas_destino = spsheet_destino.getSheetByName("Tb_Venta"); //Destination Sheet

  //Va a la hoja de Ventas de Central
  var fila_destino = soporte.getRange("Q3").getValue(); //We grab where we are going to put the data (row)

  var valores_venta_1 = origen.getRange("B3:Q3").getValues(); //We grab the values we wanna send, part 1
  var valores_venta_2 = origen.getRange("R3").getValue(); //We grab the values we wanna send, part 2

  ventas_destino.getRange(fila_destino, 2, 1, 16).setValues(valores_venta_1); //We set the values part 1
  ventas_destino.getRange(fila_destino, 23, 1, 1).setValue(valores_venta_2); //We set the values part 2

  //Other setting for another function, not important
  var fila_destino_facturacion = soporte.getRange("U1").getValue(); 
  var destino_fact_row = (fila_destino_facturacion - 1) * 15 + 3;  
  ventas_destino.getRange(fila_destino, 21).setValue(destino_fact_row); 

  //We finished setting the data in Sheet 2
  //Now we call the second function that will have as input the data we sent to Sheet 2
  GrabarMargen(); 
}

GrabarMargen() is a function that does someting similar to GrabarVenta(). It also send information to the second SpreadSheet (but to another sheet). I don't believe it's part of the issue but here the code:

function GrabarMargen(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spsheet_destino = SpreadsheetApp.openById("1WudWswy8xWF02jahGILZxEGWcBK4kKcN1xz6cWCNrzs");

  var origen = spreadsheet.getSheetByName("BackEndVentaCxC");
  var soporte = spreadsheet.getSheetByName("BackEndPAX");  
  var margen_destino = spsheet_destino.getSheetByName("Tb_Margen");

  var fila_destino = soporte.getRange("Q6").getValue();

  var margen_valores = origen.getRange("B16:AC16").getValues(); //Grabs values
  margen_destino.getRange(fila_destino, 2, 1, 28).setValues(margen_valores); //Set valuesto Sheet 2

  //Calls next Function
  PrepararEnviar(); //HERE IS THE PROBLEM
  LimpiarNuevaVenta();
  IrEnviar();
}

After that it's done it will call PrepararEnviar which will use the data from the second Spreadsheet.

function PrepararEnviar(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var origen = spreadsheet.getSheetByName("Pasajeros");  
  var apoyo = spreadsheet.getSheetByName("BE_Enviar");  
  var destino = spreadsheet.getSheetByName("Enviar");    


  //The data called from SpreadSheet2 is set in C5:C9
  //We check that the data is there
  var validar_valores = apoyo.getRange("C5:C9").getValues();
  for(var i = 0; i < validar_valores.length; i++){
    while (validar_valores[i] == "#N/A") {
      Logger.log(i + "aún es NA");
      Utilities.sleep(200);

    }
  }
  CargarEmail();
}

After executing the script I notice that the data is not saved into the Spreadsheet2 until these functions are executed. But this functions will fail since there is no data to use.

Any idea why this happens? I thought that the code will execute in the order is written. I even tried putting some logs there to test this and it seems it is like this, but why it does not set the values in SP2 before executing the rest?

Answer 1

Try using SpreadsheetApp.flush() between steps.

SpreadsheetApp.flush()