Google-sheets – Script not always starting when using html popup to gather data

google sheetsgoogle-apps-script

I have a script that uses a html popup window to get user input and pass the data to my main script after the user enters data and click the 'submit' button. I want the window to close after the user clicks submit so I added a google.script.host.close() in my code but when I add that line the script doesn't always run. The script works fine when I remove the close().

I'm quite new to javascript / google script so I suspect I did something wrong. I tried adding a sleep function before the close() but it doesn't seem to do anything.

Is there something wrong in my code? I need some help.

Here's my HTML file:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <form class="form-inline">
    <p>Entrez le # de ligne des formulaires à créer.</p>
    <label for="debut">Première ligne :</label>
    <input type="number" id="debut" name="debut" min="2"/><br/><br / >
    <label for="fin">Dernière ligne :</label>
    <input type="number" id="fin" name="fin"/><br/><br / >
    <input type="submit" id="soumettre" value="Soumettre" onclick="getInput()"/>
    <input type="button" id="annuler" value="Annuler" onclick="google.script.host.close()" />
  </form>
</html>

<script>

function getInput(){

    var valueDebut = document.getElementById("debut").value;
    var valueFin = document.getElementById("fin").value;
    google.script.run.main(valueDebut, valueFin);
    google.script.run.sleep();
    google.script.host.close();

}

</script>

Here are parts of my google script code:

/**** UI ****/

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Formulaires')
      .addItem('Créer des formulaires', 'showDialog')
      .addItem('Synchroniser les formulaires', 'checkAttente')
      .addToUi();
}

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('Dialog')
      .setWidth(315)
      .setHeight(185);


  SpreadsheetApp.getUi()
      .showModalDialog(html, 'Création de formulaire');

} 


/**** MAIN ****/

function main(valueDebut, valueFin) {

  var minRow = valueDebut;
  var maxRow = valueFin;
  var ss = SpreadsheetApp.openById('xxx');
  var sheet = ss.getSheetByName('xxx');

  for (var i = minRow; i <= maxRow; i++){

    var currentRow = i;
    var data = getData(sheet, currentRow);

    if (data[6] == "" && data[7] != 0) {
      var update = createForm(data[0],data[1],data[2],data[3],data[4],data[5],nomFolder);
      sheet.getRange(currentRow, colLienEdit).setValue(update[0]);
      sheet.getRange(currentRow, colLienPartage).setValue(update[1]);
      sheet.getRange(currentRow, colId).setValue(update[2]);
      sheet.getRange(currentRow, colStatut).setValue(update[3]);
    }
  }
}

function sleep(){
  Utilities.sleep(1000);
}

Best Answer

The "problem" occurs because google.script.run is asynchronous. The solution is to use withSuccesHandler and put google.script.host.close() inside it.

Replace

google.script.run.main(valueDebut, valueFin);
google.script.run.sleep();
google.script.host.close();

by

google.script.run.withSuccessHandler(function(){
  google.script.host.close(); 
}).main(valueDebut, valueFin);

Reference

Related Topic