How to fix Google Apps Script Function Running in random sequence

I have a project that will generate a dynamic Listbox from GSheet and it will update the customized form i’ve created in Google Apps Script.

The code will get the input from the URL when it is loaded and generate 2 ListBox first before getting the input data to pre-fill the form.

So the Sequence should be

Generate the Selection for Car Brand
Generate the Selection for Colour
Get the customer data to pre-fill the form

But what I’m getting is whenever I refresh the page, the sequence of function loading is random. Sometimes it is working fine, sometimes it is loading 3 > 1 > 2 or 2 > 3 > 1 or any other random sequence.

Please suggest anyway that we can make sure the sequence is running as per design.

Below is the sample code

Code.gs

var SHEET_CAR_URL = 'https://docs.google.com/spreadsheets/d/{sheet1ID}/edit#gid=0';
var SHEET_COLOUR_URL = 'https://docs.google.com/spreadsheets/d/{sheet2ID}/edit#gid=0';
var SHEET_CUSTOMER_URL = 'https://docs.google.com/spreadsheets/d/{sheet2ID}/edit#gid=0';


function doGet(request) {
 return HtmlService.createTemplateFromFile('CustomerForm').evaluate().setTitle("Demo Form");
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function loadCarList(){
  //load Cars GSheet
  var carsspreadsheet = SpreadsheetApp.openByUrl(SHEET_CAR_URL).getSheetByName('Cars');
  numItemCars = carsspreadsheet.getLastRow()-1;// get the number of rows in the sheet
  colItemCars = carsspreadsheet.getLastColumn();// get the number of rows in the sheet
  listCarsArray = carsspreadsheet.getRange(2,1,numItemCars,colItemCars).getValues(); 

  var listCar = "<option value=''></option>";  
   for(var i=0; i<numItemCars; i++){
    listCar += "<option value='"+listCarsArray[i][0]+"'>"+listCarsArray[i][0]+"</option>";
  }
  Logger.log(listCar);
  return listCar;
}

function loadColourList(){
  //load Colour GSheet
  var colourspreadsheet = SpreadsheetApp.openByUrl(SHEET_COLOUR_URL).getSheetByName('Colour');
  numItemColour = colourspreadsheet.getLastRow()-1;// get the number of rows in the sheet
  colItemColour = colourspreadsheet.getLastColumn();// get the number of rows in the sheet
  listColourArray = colourspreadsheet.getRange(2,1,numItemColour,colItemColour).getValues();

  var listColour = "<option value=''></option>";  
   for(var i=0; i<numItemColour; i++){
    listColour += "<option value='"+listColourArray[i][0]+"'>"+listColourArray[i][0]+"</option>";
  }
  Logger.log(listColour);
  return listColour;
}

function loadCustomer(inputID){
  //load Customer GSheet
  var customerspreadsheet = SpreadsheetApp.openByUrl(SHEET_CUSTOMER_URL).getSheetByName('Customer');
  numItemCust = customerspreadsheet.getLastRow()-1;// get the number of rows in the sheet
  colItemCustr = customerspreadsheet.getLastColumn();// get the number of rows in the sheet
  listCustArray = customerspreadsheet.getRange(2,1,numItemCust,colItemCustr).getValues(); 

  var custDetails = [];
   for(var i=0; i<numItemCust; i++){
    var custID = listCustArray[i][0];
    var custName = listCustArray[i][1];
    var custArea = listCustArray[i][2];
    var custCar = listCustArray[i][2];
    var custCarColour = listCustArray[i][2];
    if(custID == inputID){
      custDetails = [custID,custName,custArea,custCar,custCarColour];
    }
  }
  Logger.log(custDetails[0]);
  return custDetails;
}

JavaScript
​x
 
var SHEET_CAR_URL = 'https://docs.google.com/spreadsheets/d/{sheet1ID}/edit#gid=0';
var SHEET_COLOUR_URL = 'https://docs.google.com/spreadsheets/d/{sheet2ID}/edit#gid=0';
var SHEET_CUSTOMER_URL = 'https://docs.google.com/spreadsheets/d/{sheet2ID}/edit#gid=0';
​
​
function doGet(request) {
 return HtmlService.createTemplateFromFile('CustomerForm').evaluate().setTitle("Demo Form");
}
​
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}
​
function loadCarList(){
  //load Cars GSheet
  var carsspreadsheet = SpreadsheetApp.openByUrl(SHEET_CAR_URL).getSheetByName('Cars');
  numItemCars = carsspreadsheet.getLastRow()-1;// get the number of rows in the sheet
  colItemCars = carsspreadsheet.getLastColumn();// get the number of rows in the sheet
  listCarsArray = carsspreadsheet.getRange(2,1,numItemCars,colItemCars).getValues(); 
​
  var listCar = "<option value=''></option>";  
   for(var i=0; i<numItemCars; i++){
    listCar += "<option value='"+listCarsArray[i][0]+"'>"+listCarsArray[i][0]+"</option>";
  }
  Logger.log(listCar);
  return listCar;
}
​
function loadColourList(){
  //load Colour GSheet
  var colourspreadsheet = SpreadsheetApp.openByUrl(SHEET_COLOUR_URL).getSheetByName('Colour');
  numItemColour = colourspreadsheet.getLastRow()-1;// get the number of rows in the sheet
  colItemColour = colourspreadsheet.getLastColumn();// get the number of rows in the sheet
  listColourArray = colourspreadsheet.getRange(2,1,numItemColour,colItemColour).getValues();
​
  var listColour = "<option value=''></option>";  
   for(var i=0; i<numItemColour; i++){
    listColour += "<option value='"+listColourArray[i][0]+"'>"+listColourArray[i][0]+"</option>";
  }
  Logger.log(listColour);
  return listColour;
}
​
function loadCustomer(inputID){
  //load Customer GSheet
  var customerspreadsheet = SpreadsheetApp.openByUrl(SHEET_CUSTOMER_URL).getSheetByName('Customer');
  numItemCust = customerspreadsheet.getLastRow()-1;// get the number of rows in the sheet
  colItemCustr = customerspreadsheet.getLastColumn();// get the number of rows in the sheet
  listCustArray = customerspreadsheet.getRange(2,1,numItemCust,colItemCustr).getValues(); 
​
  var custDetails = [];
   for(var i=0; i<numItemCust; i++){
    var custID = listCustArray[i][0];
    var custName = listCustArray[i][1];
    var custArea = listCustArray[i][2];
    var custCar = listCustArray[i][2];
    var custCarColour = listCustArray[i][2];
    if(custID == inputID){
      custDetails = [custID,custName,custArea,custCar,custCarColour];
    }
  }
  Logger.log(custDetails[0]);
  return custDetails;
}
​

CustomerForm.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body onload="myLoadFunction()">
    <table>
      <tr>
        <td> Customer ID : </td>
        <td> <input type="text" id="CustID"> </td>
      </tr>
      <tr>
        <td> Customer Name : </td>
        <td> <input type="text" id="CustName"></td>
      </tr>
      <tr>
        <td> Customer Area : </td>
        <td> <input type="text" id="CustArea"></td>
      </tr>
      <tr>
        <td> Car Brand : </td>
        <td><select class='listbox' id='listCar' required></select> </td>
      </tr>
      <tr>
        <td> Car Colour : </td>
        <td><select class='listbox' id='listColour' required></select> </td>
      </tr>
    </table>
  </body>
</html>
<script>

function myLoadFunction(){
    // Get the URL parameter
    google.script.url.getLocation(inputstrings => {
 
        let inputjson =  JSON.stringify(inputstrings.parameter);
        let inputparameters = JSON.parse(inputjson)
        var in_custID = inputparameters.id;
        alert('This is the ID '+in_custID);

        google.script.run.withSuccessHandler(initializeCar).loadCarList(); 
        google.script.run.withSuccessHandler(initializeColour).loadColourList(); 

        google.script.run.withSuccessHandler(initializeForm).loadCustomer(in_custID); 

    })

    function initializeCar(inputList){
      alert('Loading Cars')
      document.getElementById('listCar').innerHTML = inputList;   
    }
    
    function initializeColour(inputList){
      alert('Loading Colour')
      document.getElementById('listColour').innerHTML = inputList;   
    }

    function initializeForm(inputDetails){
      alert('Loading Form')
      document.getElementById('CustID').value = inputDetails[0];
      document.getElementById('CustName').value = inputDetails[1];
      document.getElementById('CustArea').value = inputDetails[2];
      document.getElementById('listCar').value = inputDetails[3];
      document.getElementById('listColour').value = inputDetails[4];
    }
}

</script>

JavaScript
 
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body onload="myLoadFunction()">
    <table>
      <tr>
        <td> Customer ID : </td>
        <td> <input type="text" id="CustID"> </td>
      </tr>
      <tr>
        <td> Customer Name : </td>
        <td> <input type="text" id="CustName"></td>
      </tr>
      <tr>
        <td> Customer Area : </td>
        <td> <input type="text" id="CustArea"></td>
      </tr>
      <tr>
        <td> Car Brand : </td>
        <td><select class='listbox' id='listCar' required></select> </td>
      </tr>
      <tr>
        <td> Car Colour : </td>
        <td><select class='listbox' id='listColour' required></select> </td>
      </tr>
    </table>
  </body>
</html>
<script>
​
function myLoadFunction(){
    // Get the URL parameter
    google.script.url.getLocation(inputstrings => {
 
        let inputjson =  JSON.stringify(inputstrings.parameter);
        let inputparameters = JSON.parse(inputjson)
        var in_custID = inputparameters.id;
        alert('This is the ID '+in_custID);
​
        google.script.run.withSuccessHandler(initializeCar).loadCarList(); 
        google.script.run.withSuccessHandler(initializeColour).loadColourList(); 
​
        google.script.run.withSuccessHandler(initializeForm).loadCustomer(in_custID); 
​
    })
​
    function initializeCar(inputList){
      alert('Loading Cars')
      document.getElementById('listCar').innerHTML = inputList;   
    }
    
    function initializeColour(inputList){
      alert('Loading Colour')
      document.getElementById('listColour').innerHTML = inputList;   
    }
​
    function initializeForm(inputDetails){
      alert('Loading Form')
      document.getElementById('CustID').value = inputDetails[0];
      document.getElementById('CustName').value = inputDetails[1];
      document.getElementById('CustArea').value = inputDetails[2];
      document.getElementById('listCar').value = inputDetails[3];
      document.getElementById('listColour').value = inputDetails[4];
    }
}
​
</script>
​

Sample Customer Data

ID No	Customer Name	Customer Area	Car Brand	Car Colour
1001	Alice	IN	Toyota	Blue
1002	Bob	OH	Honda	Red
1003	Charlie	WD	BMW	Brown

Sample Colour

Colour
Blue
Red
Brown
Green
Yellow

Sample Car Brand

Brand
BMW
Toyota
Honda
Tesla
VW

I’ve tried to use If Else to make sure the ListBox is already populated before running the 3rd function but no luck with that.

Thanks in Advance to anybody that can help in this.

Answer

Because google.script.run runs asynchronously, which means the second one doesn’t wait for the first to return before running. You need to nest them. Then in the html <script> simply run the first only. I’ve moved in_custID outside of the setLocation call so its available to the other functions.

function myLoadFunction(){
    // Get the URL parameter
    var in_custID = null;
    google.script.url.getLocation(inputstrings => {
 
        let inputjson =  JSON.stringify(inputstrings.parameter);
        let inputparameters = JSON.parse(inputjson)
        in_custID = inputparameters.id;
        alert('This is the ID '+in_custID);

        google.script.run.withSuccessHandler(initializeCar).loadCarList(); 

    })

    function initializeCar(inputList){
      alert('Loading Cars')
      document.getElementById('listCar').innerHTML = inputList;
      google.script.run.withSuccessHandler(initializeColour).loadColourList(); 
    }
    
    function initializeColour(inputList){
      alert('Loading Colour')
      document.getElementById('listColour').innerHTML = inputList;   
      google.script.run.withSuccessHandler(initializeForm).loadCustomer(in_custID); 
    }

    function initializeForm(inputDetails){
      alert('Loading Form')
      document.getElementById('CustID').value = inputDetails[0];
      document.getElementById('CustName').value = inputDetails[1];
      document.getElementById('CustArea').value = inputDetails[2];
      document.getElementById('listCar').value = inputDetails[3];
      document.getElementById('listColour').value = inputDetails[4];
    }
}

JavaScript
 
function myLoadFunction(){
    // Get the URL parameter
    var in_custID = null;
    google.script.url.getLocation(inputstrings => {
 
        let inputjson =  JSON.stringify(inputstrings.parameter);
        let inputparameters = JSON.parse(inputjson)
        in_custID = inputparameters.id;
        alert('This is the ID '+in_custID);
​
        google.script.run.withSuccessHandler(initializeCar).loadCarList(); 
​
    })
​
    function initializeCar(inputList){
      alert('Loading Cars')
      document.getElementById('listCar').innerHTML = inputList;
      google.script.run.withSuccessHandler(initializeColour).loadColourList(); 
    }
    
    function initializeColour(inputList){
      alert('Loading Colour')
      document.getElementById('listColour').innerHTML = inputList;   
      google.script.run.withSuccessHandler(initializeForm).loadCustomer(in_custID); 
    }
​
    function initializeForm(inputDetails){
      alert('Loading Form')
      document.getElementById('CustID').value = inputDetails[0];
      document.getElementById('CustName').value = inputDetails[1];
      document.getElementById('CustArea').value = inputDetails[2];
      document.getElementById('listCar').value = inputDetails[3];
      document.getElementById('listColour').value = inputDetails[4];
    }
}
​

Advertisement

Answer