Google Sheets to HTML table

For many years Google Sheets has been my go-to, given the speed with which one can sketch any sort of project in their fantastically fast & intuitive UI:

• metrics tracking for SMEs
• database architectures (up to about the 5k x 5k mark)

In 2017 I began to add a further application to the list:

• automations

Google Apps Script has gone through many guises & names; nowadays simply called Google Script it is accessed either from the GCP console (where you can manage it alongside other cloud resources e.g. your project IDs, billing accounts, storage & hosting etc) or via the Tools menu of any Google Sheet. The tieback to Sheet’s parent technology, Google Drive, is that you can also view all your Scripts in a single sindow at https://script.google.com. This is a distinct advantage over other cloud providers as it’s roughly equivalent to a developer being able to filter an entire file structure for “.js files”. Timesaver.

It’s essentially a version of JavaScript tailored to the Alphabet ecosystem; see Ben L Collins’ excellent introductory tutorial for full info.

In the course of delivering metrics, databasing, and automations, for all sorts of clients during the data consulting days of Stone Street Productions, I frequently encountered the simple request whereby an existing table in some Sheet should be drawn into an auto-compiled email for sending out at regular intervals, to stakeholders needing a snapshot of metric without having to log in manually. The following is an elegant solution to that basic request. It’s also a nice gateway into what’s possible with Sheet+Script and will hopefully spark some thoughts as to the superiority of this language over others… where else can you run a script so tightly coupled to a database that is so open to non-technical users?

function toHTMLTable(a) {
  var content = a.map(function(row, i) {
    var rowHTML = row.map(function (col) {
      return "<td>" + col + "</td>";
    }).join("");

    return "<tr>" + rowHTML + "</tr>";
  }).join("");
  return "<table>" + content + "</table>";
}
// credit to stackoverflow.com/users/5426909/robin-gertenbach

To be discussed further in a future post.