Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active August 12, 2022 00:20
Show Gist options
  • Save tanaikech/f1436d88423077115d2c249862f12f17 to your computer and use it in GitHub Desktop.
Save tanaikech/f1436d88423077115d2c249862f12f17 to your computer and use it in GitHub Desktop.
Benchmark: Process cost for HTML Template using Google Apps Script

Benchmark: Process cost for HTML Template using Google Apps Script

Introduction

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

As the result, it was clearly found that when an HTML table is created with Google Apps Script and shows the HTML table with the HTML template, the process cost becomes low. And also, it was found that in order to reduce the process cost for using the HTML template, it is required to prepare the HTML data with the Google Apps Script side.

Experimental procedure

In order to measure the process cost of the HTML template, I used the following sample situation.

  • Create an HTML table by giving a value from Google Apps Script to the HTML template.

In order to achieve this, I prepared the following 2 patterns.

  1. Create a 2-dimensional array in Google Apps Script, and gives the array to the HTML template, create an HTML table in the HTML template.

  2. Create an HTML table in Google Apps Script, and gives the HTML table to the HTML template, show an HTML table in the HTML template.

As a sample value for measuring the process cost, a 2-dimensional array constructed by n rows and 10 columns was used. In this experiment, the number of rows is changed.

By the way, at GAS, the processing time is not stable as you know. So the average value for more than 100 times measurements was used for each data point which is shown in the figure. At this time, the fluctuation of the average values was less than 1 %. I worry that each detailed-data point in my environment might be different from that of other user's environments. But I think that the trend of this result can be used.

Results and discussions

Fig 1. Process costs for creating an HTML table with HTML template and Google Apps Script.


Figure 1 shows the process costs for creating an HTML table with an HTML template and Google Apps Script. From this figure, it was clearly found that when an HTML table is created with Google Apps Script and shows the HTML table with the HTML template, the process cost becomes low. And also, it was found that when an HTML table is created with the HTML template, the process cost becomes high. This difference is very large.

Also, it was found that the reason for this high process cost is due to evaluate(). This indicates that when the script is run on the HTML template like <? for (var i = 0; i < data.length; i++) { ?>, the process cost will be high.

And, it is considered that in order to reduce the process cost for using the HTML template, it is required to prepare the HTML data with the Google Apps Script side.

Summary

In this report, the process costs for creating an HTML table on an HTML template have been investigated. From this report, the following result was obtained.

  1. It was clearly found that when an HTML table is created with Google Apps Script and shows the HTML table with the HTML template, the process cost becomes low.

  2. It was found that when an HTML table is created with the HTML template, the process cost becomes high.

  3. In order to reduce the process cost for using the HTML template, it is required to prepare the HTML data with the Google Apps Script side.

The process cost of this report might be modified by future updates of Google.

Appendix

Sample scripts

Ths script used in this report is as follows. Here, as a sample, I used the container-bound script of Google Spreadsheet. And, the HTML template is used to a dialog on Spreadsheet.

HTML template: index1.html

This is from this official document.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    <table>
      <? for (var i = 0; i < data.length; i++) { ?>
      <tr>
        <? for (var j = 0; j < data[i].length; j++) { ?>
        <td><?= data[i][j] ?></td>
        <? } ?>
      </tr>
      <? } ?>
    </table>
  </body>
</html>

HTML template: index2.html

In this template, the content of table is changed to <?!= data ?>.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top" />
  </head>
  <body>
    <table>
      <?!= data ?>
    </table>
  </body>
</html>

Google Apps Script

function createSampleData_() {
  const maxRow = 100;
  const maxCol = 10;
  return [...Array(maxRow)].map((_, i) =>
    [...Array(maxCol)].map((_, j) => `(${i + 1},${j + 1})`)
  );
}

function measure1() {
  const data = createSampleData_();
  const html = HtmlService.createTemplateFromFile("index1");
  html.data = data;
  const h = html.evaluate();
  SpreadsheetApp.getUi().showDialog(h);
}

function measure2() {
  const data = createSampleData_();
  const html = HtmlService.createTemplateFromFile("index2");
  html.data = data.reduce(
    (s, r) => (s += `<tr>${r.map((c) => `<td>${c}</td>`).join("")}</tr>`),
    ""
  );
  const h = html.evaluate();
  SpreadsheetApp.getUi().showDialog(h);
}

Reference

@imthenachoman
Copy link

imthenachoman commented Jul 11, 2022

Great writeup!

Did you consider a 3rd approach -- sending the raw data back to the client to process?

Also, if efficiency is paramount, especially if you have a lot of data, I find find loops faster than forEach/map/reduce/etc.

@tanaikech
Copy link
Author

Thank you for your comment.

About Did you consider a 3rd approach -- sending the raw data back to the client to process?, in this case, you are considering to use google.script.run?

About Also, if efficiency is paramount, especially if you have a lot of data, I find find loops faster than forEach/map/reduce/etc., I cannot understand this. If it is required to do the loop for all elements, how about this?

@imthenachoman
Copy link

you are considering to use google.script.run?

I guess two different options:

  1. google.script.run to get the data from the backend and then process client side
  2. use template to set js variable to data (JSON.stringify and JSON.parse) and then client side to use the js variable

In my experience, for(var i = 0, numItems = arr.length; i < numItems; ++i){ .... } is faster than arr.forEach(....).

@tanaikech
Copy link
Author

Thank you for your comment.

About In my experience, for(var i = 0, numItems = arr.length; i < numItems; ++i){ .... } is faster than arr.forEach(....)., inmy experimental data can be seen at here and .

About google.script.run to get the data from the backend and then process client side, in this report, the process cost of HTML Template is measured. So, I didn't measure it. But, from this result, if you want to create the HTML data using Javascript and Google Apps Script with google.script.run, it has already known that the process cost will be lower than that of creation of HTML data in the script of HTML Template.

About use template to set js variable to data (JSON.stringify and JSON.parse) and then client side to use the js variable, JSON.stringify and JSON.parse are used in HTML Template? If my understanding is correct, I'm worried that the process cost is required to be used. How about this?

@imthenachoman
Copy link

Oh wow. Thanks for sharing your for loop performance.

And good point about process cost for JSON.stringify/parse. I hadn't considered that.

@LindaLawton
Copy link

How are you tracking this processing time?

@tanaikech
Copy link
Author

In this case, the result of Fig.1 has been obtained by measuring the processing time of measure1() and measure2() in the above sample script.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment