From Sheet to Web App – Quick, simple, dirty…

In this tutorial we’ll have a look at one of the fastest ways to display Google Sheet tables inside a Web App.

The method explained in this tutorial is quite literally “quick, simple and dirty”. It’s far from best practice, but it surely gets the job done 🙂

One of the awesome functions of Google web-apps is that you can share information with other users through a web interface, in the browser. If you update your table and the users refresh their browser, they will have instant access to the updated information. Neat isn’t it?

This is what the output of our Web-app will look like (not pretty, but it works):

In this tutorial, we will choose what the end-user will see in our web-app. In later tutorials we’ll have a look at using AJAX in order to give the user the flexibility to select the data he or she wants to see. AJAX code will allow us to apply filters, similar to using Slicers in Excel, but directly in your browser (such as Chrome or Internet Explorer).

We will also have a look at other methods of displaying Google sheets data in a web-app (including the use of JSON) in future tutorials.

In order for the Web-app (in this tutorial) to display our Google sheet data, we will need the following files:

  • A Google sheet with some dummy data;
  • A “Code.gs” file
  • An “index_html.html” file; and
  • A “styles_css.html” file.

The files mentioned above (apart from the google sheet) will be created with the help of Google’s “Script Editor”.

Let’s get started…

  • The first thing we need to do is to create a blank Google workbook. You can name the workbook whatever you like.
  • Next, we have to add some “dummy” data to display in our Web-app. You can add whatever you like. Below is a screenshot of two Google sheet data tables that I would like to display in the web-app

Table 1 & 2

  • Now to add our code.
  • Open the “Script editor” under “Tools”
  • Give your script a name (top left corner next to the big blue arrow)
  • Then click on “File”, “New”, “HTML file”
  • Name the file “index_html”
  • Repeat this step and name the second file “styles_css”
  • Your end result should look like this:
  • Copy the code below in the files as specified. The code is commented, explaining what the code will do.

Code.gs

//@OnlyCurrentDoc

// --------------------------------  doGet --------------------------------
// All about web-apps: https://developers.google.com/apps-script/guides/web
// A script can be published as a web app if it meets these requirements:
//   *  It contains a doGet(e) or doPost(e) function.
//   *  The function returns an HTML service HtmlOutput object or a Content service TextOutput object.
function doGet(request) {
  // This code will launch the 'index_html' page
  return HtmlService.createTemplateFromFile('index_html').evaluate();
}

// --------------------------------  include() --------------------------------
// This custom function allows us to import the 'styles_css file into our webpage
// We can something similar for javascript files if we wanted to place our JavaScript code in a "seperate" file
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

// --------------------------------  displayTable1() --------------------------------
// This custom function "gets" the the values from Table 1
// Table one's data is contained in cells 'A1 to C13', inside 'Sheet1' of this workbook
function displayTable1(){
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange("A1:C13").getValues();
  // We can run the two lines of code below if we want to make sure that our data is being pulled through.
  // var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange("A1:C13").getValues();
  // Logger.log(ss);
}

// --------------------------------  displayTable2() --------------------------------
// This custom function "gets" the the values from Table 2
// Table one's data is contained in cells 'A15 to C27', inside 'Sheet1' of this workbook
function displayTable2() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange("A15:C27").getValues();
   // We can run the two lines of code below if we want to make sure that our data is being pulled through.
  // var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange("A1:C13").getValues();
  // Logger.log(ss);
}


index_html

<!-- The 'index_html' file serves as the 'face' of our web-app -->
<!-- The code in the 'index_html' file, combined with the 'styles_css' file will determine what the end-user will see -->

<!DOCTYPE html>
<html>
  <!-- Standard 'html' documents include '<html></html>', <head></head>', and '<body></body>' elements -->
  <!-- Source: https://www.w3schools.com/html/html_elements.asp -->
  <!-- -------------------------------------------------------  Head ------------------------------------------------------- -->
  <head>
    <base target="_top">
    <!-- The code below 'imports' the 'styles_css' file so the we can style the look and feel of our Web-app -->
    <?!= include('styles_css'); ?>
  </head>
  <!-- -------------------------------------------------------  Body ------------------------------------------------------- -->
  <body>
  <div id="heading">This is our quick, simple and dirty Web-app</div>
  <br><br>
  <!-- -------------------------------------------------------  Table 1 ------------------------------------------------------- -->
  <!-- The code below adds the information from Table 1 -->
  <div id="table1_div">
    <!-- The code below creates a heading for Table 1 -->
    <label for="table1_div">Table 1</label>
    <br><br>
    <!-- The code below, runs the 'displayTable1()' function in our 'Code.gs' file and assigns the data to a variable called 'data' -->
    <? var data = displayTable1(); ?>
    <!-- The code below creates the 'table' element that will contain our data from Table 1 -->
    <table>
      <!-- The first 'for loop' determines the Column number for each data point -->
      <!-- The function is repeated over and over until all the data points have been added -->
      <!-- The script determines the total number of data points by using 'data.length' -->
      <? for (var i = 0; i < data.length; i++) { ?>
        <tr>
          <!-- The second 'for loop' determines the Row number of each data point -->
          <? for (var j = 0; j < data[i].length; j++) { ?>
            <!-- The code below adds the data to our web-app -->
            <td><?= data[i][j] ?></td>
          <? } ?>
        </tr>
      <? } ?>
    </table>
  </div>
  <br><br>
  
  <!-- -------------------------------------------------------  Table 2 -- ------------------------------------------------------->
  <!-- The code below adds the information from Table 2 -->
  <div id="table2_div">
    <label for="table2_div">Table 2</label>
    <br><br>
    <? var data = displayTable2(); ?>
    <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>
  </div>
  </body>
  
<!-- Source: https://stackoverflow.com/questions/16660906/display-spreadsheet-data-in-sites-with-html-service -->
</html>


styles_css

<style>
* {
  padding: 0em 0em 0em 0.2em;
  margin: 0em;
  border: none;
}
#heading {
  font-size: 50px;
  font-weight: 900;
  color: purple;
}
label {
  font-weight: 900;
  color: blue;
}
td {
  color: black;
  border-style: groove;
  border-width: 1px;
}
</style>

  • Once you copied the code, click on “File”, “Save all”
  • Next, click on “Publish”, “Deploy as web app…”
  • You will be prompted with several windows asking for ‘access’. Ensure that you give the necessary permissions to run the app. Without these permissions, the app will not run.
  • Once the window below pops up, click “Deploy”
  • In the next window, click “latest code.”

And there you go.

Your web-app should launch…quick…simple…dirty 🙂

If you have any comments or suggestion, feel free to reach out in the comment section below.

Until next time…

Leave a Reply

Your email address will not be published. Required fields are marked *