Question 414095 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Dynamically update variable in Spreadsheet popup UI element from google Apps Script

I have a google Apps Script function containing a forEach loop having a variable rowIndex which is to be displayed and updated in the popup (in a SpreadSheet) as the loop runs, but it only shows the last rowIndex in popup. As of now, only the title is dynamic but content of html is only displayed only for last rowIndex.

Here is my html:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>
<body>
  <p align="center">
    <?!=htmlVariable?> This is htmlVariable.
  </p>
  <p align="center"><button onclick="google.script.host.close()">Close</button></p>
  <script>
    function closer(){
        google.script.host.close();
      }
  </script>
</body>

</html>

Apps Script function:

function extractTestCases(ProgressUIInstance, _startTime) {

  const ui = SpreadsheetApp.getUi();

  // .....Some Code......

  let tempHtmlOutput = HtmlService.createTemplateFromFile('html_file');
  tempHtmlOutput.htmlVariable = "Starting. . ."
  const dialog = ui.showModelessDialog(tempHtmlOutput.evaluate(), "Extractingg Test Cases");
  
  // .....Some Code......

  // 6. Processing Each Row
  values.forEach((row, rowIndex) => {
    
    // .....Some Code......
    let dialogCount = 1;
    while ((match = regex.exec(testCaseText)) !== null) {
      
      // .....Some Code......

      let endTime = new Date();
      const elapsedSeconds = endTime - _startTime;

      Utilities.sleep(500);

      tempHtmlOutput.htmlVariable = rowIndex;
      ui.showModelessDialog(tempHtmlOutput.evaluate(), `EXTRACT - Row ${rowIndex + 1} - Dialog ${dialogCount}`);

      SpreadsheetApp.flush();
      Utilities.sleep(500);
      dialogCount++;
    }
    // .....Some Code......
  });

// .....Some Code......
}

Screencast for reference: Screencast

Output as of now:
Only the last rowIndex is shown in model/popup.
Only the last rowIndex is shown in model/popup.

Here, for me the expected output would be a model where these lines will get update sequentially:

0 This is html variable.
1 This is html variable.
2 This is html variable.
3 This is html variable.
4 This is html variable.
Tags:

2

Answers


  1. 0

    <?!=htmlVariable?> This is htmlVariable.

    That will only print one value in the HTML page. The loop in your current Apps Script function updates the value a few times, each time getting a new HTML page to replace the previous one, and finally the last version of the page gets rendered and remains on-screen.

    To make it work, use a loop in the HTML template, like this:

        <? var data = getData(); ?>
    <table>
      <? for (var i = 0; i < data.length; i++) { ?>
        <tr>
            <td><?= data[i] ?></td>
        </tr>
      <? } ?>
    </table>

    …where getData() is an Apps Script function that returns the values variable, or returns values.keys() if you really just want a sequence of numbers.

    See Calling Apps Script functions from a template.

    Login or Signup to reply.
  2. 0

    You just need to wait a bit longer for the html to load:

    Utilities.sleep(5000 /*or 10000*/);

    Or alternatively, call the server function after html finishes loading:

    <head>
  <base target="_top">
  <script>window.addEventListener('load', ()=>google.script.run.continueLoop(<?!=htmlVariable?>))</script>
</head>

    function continueLoop(rowIndex){/*some code to continue where you left off*/}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search