javascriptgoogle-sheetsgoogle-apps-script

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.
Solution

  • 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*/}