3

I have a Google Script function that populates cells based on values from Javascript The GS function is called inside a Javascript 'For' loop. When I run the code, the cells are not being populated until ALL increments of the 'For' loop are finished running.

Magically, after the For loop finishes, the GS function starts populating relevant cells (somehow it remembers all the dynamic values). However, not all expected cells get populated and also not in the correct order.

Tried using .flush() - did not help

GS function:

function rolesInputer(role, i){
    var rolesInputer = role;
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var column = 3+i;
    var cell = sheet.getRange(17,column);
    cell.setValue(role);
}

JS function:

function saveInput() {
    var i;
    for (i = 1; i <= dataEncoded; i++) {
        sleep(1000);
        var temprole = "role" + (i);
        var roleSelect = document.getElementById(temprole);
        var roleSelected = roleSelect.options[roleSelect.selectedIndex].value;
        google.script.run.withSuccessHandler(roleSelected, i).rolesInputer(roleSelected, i);
        alert("executed");
    }
    google.script.host.close();
}
Ilya Perelman
  • 45
  • 5
  • Flush the write buffer manually. Also, consider implementing a batch method to handle all the inputs at once. – tehhowch Apr 11 '19 at 19:00
  • Possible duplicate of [Why do we use SpreadsheetApp.flush();?](https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush) – tehhowch Apr 11 '19 at 19:01
  • Thank you for you response. I have tried adding SpreadsheetApp.flush(); on the bottom of my GS function, but it did not solve the problem. Am I doing something wrong? I will look into optimisation later, thank you for the suggestion. – Ilya Perelman Apr 11 '19 at 19:19
  • I'm also curious why you pass a non-function value to `withSuccessHandler`. Consider reviewing the documentation for the `google.script.run` client-side API – tehhowch Apr 11 '19 at 20:13

1 Answers1

3
  • You want to send values at HTML side to Google Apps Script side using google.script.run().

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

At first, about your following issue, I think that the reason of your issue is that google.script.run() works by the asynchronous processing.

Magically, after the For loop finishes, the GS function starts populating relevant cells (somehow it remembers all the dynamic values). However, not all expected cells get populated and also not in the correct order.

In order to avoid this, I think that there are 2 patterns for your situation.

Pattern 1:

In this pattern, after all values are retrieved, the values are sent to Google Apps Script. When you test this pattern, please modify as follows.

Google Apps Script:

function rolesInputer(values){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(17, 3, 1, values.length).setValues([values]);
}

HTML and Javascript:

I used <select>...</select> as the sample values. In this sample, when the HTML is opened, the script is run.

<select id="role1"><option value="role1" selected>role1</option></select>
<select id="role2"><option value="role2" selected>role2</option></select>
<select id="role3"><option value="role3" selected>role3</option></select>
<select id="role4"><option value="role4" selected>role4</option></select>
<select id="role5"><option value="role5" selected>role5</option></select>

<script>
  function saveInput() {
    var dataEncoded = 5;
    var values = [];
    for (var i = 1; i <= dataEncoded; i++) {
      var temprole = "role" + (i);
      var roleSelect = document.getElementById(temprole);
      var roleSelected = roleSelect.options[roleSelect.selectedIndex].value;
      values.push(roleSelected);
    }
    google.script.run.withSuccessHandler(() => {google.script.host.close()}).rolesInputer(values);
  }
  saveInput();
</script>

Pattern 2:

In this pattern, the value is sent every one value to Google Apps Script using the for loop. When you test this pattern, please modify as follows.

Google Apps Script:

In this pattern, Google Apps Script is not modified.

HTML and Javascript:

In this sample, when the HTML is opened, the script is run.

<select id="role1"><option value="role1" selected>role1</option></select>
<select id="role2"><option value="role2" selected>role2</option></select>
<select id="role3"><option value="role3" selected>role3</option></select>
<select id="role4"><option value="role4" selected>role4</option></select>
<select id="role5"><option value="role5" selected>role5</option></select>

<script>
  function work(roleSelected, i) {
    return new Promise((resolve, reject) => {
      google.script.run.withSuccessHandler(() => resolve()).rolesInputer(roleSelected, i);
    });
  }

  async function saveInput() {
    var dataEncoded = 5;
    for (var i = 1; i <= dataEncoded; i++) {
      var temprole = "role" + (i);
      var roleSelect = document.getElementById(temprole);
      var roleSelected = roleSelect.options[roleSelect.selectedIndex].value;
      await work(roleSelected, i);
    }
  }

  saveInput().then(() => google.script.host.close());
</script>

Note:

  • When the process cost is thought, I think that the pattern 1 is better.

References:

  • google.script.run

    google.script.run is an asynchronous client-side JavaScript API available in HTML-service pages that can call server-side Apps Script functions.

If this was not useful for your situation, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165