GSheet Sidebar with Materialize CSS & jQuery

Monday, July 6th, 2020
 |  3 Comments

This is a tutorial on creating a Google Spreadsheet Sidebar using Materialize CSS framework, jQuery JavaScript framework for the sidebar (the client-side), and Google Apps Script (the server-side).

Materialize and jQuery have long been my go-to for quickly creating a UI on my Google Apps Script projects: Materialize having been created by Google and the easiest way to match in with the Material Design slowly been rolled out over all their products, and jQuery … well, being jQuery!

In this post, I will walk through the various code snippets needed to create a simple form in the sidebar. The sidebar form has the added advantage of doing simple client-side validation, using HTML5 and Materialize validation, before copying the data to the spreadsheet.

Installation

If you are in a hurry to get to work on the script you can either take a copy of this demo GSheet. The code is also on GitHub.

Otherwise, create a new Google Spreadsheet, or open up your existing one, go into the script editor (File >> Script editor) and copy in the four files below.

Refresh the spreadsheet and open the sidebar with the new “Sidebar” menu.

Fill out fields in the sidebar form (leave one empty to demonstrate the client-side validation).

On clicking the “Submit” button the values are written to a “Results” tab. The Results tab is created if it doesn’t already exit.

Sidebar.html

The main HTML file for the sidebar.

Note:

  • include() – The CSS (Sidebar.css.html) and JavaScript (Sidebar.js.html) and kept in separate files we’ll come to later. A HtmlService template is used to pull those files into the main HTML before it is rendered.
  • class=”validate” and required – these two along with the input type invoke the HTML5 and Materialize client-side validation. On hitting Enter or the Submit button, a basic check is made to ensure there is a value in the input field, and the email is valid, before the form is allowed to be submitted.
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Sidebar.css') ?>
  </head>
  <body>
    <div class="container">
      <div class="section">
        <h5>Test Form</h5>
        <div class="row">
          <form id="test-form" class="col s12">
            <div class="row">
              <div class="input-field col s6">
                <input 
                  placeholder="Placeholder" 
                  id="first_name" 
                  name="first_name" 
                  type="text" 
                  class="validate" 
                  required>
                <label for="first_name">First Name</label>
              </div>    
              <div class="input-field col s6">
                <input 
                  id="last_name" 
                  name="last_name" 
                  type="text" 
                  class="validate" 
                  required>
                <label for="last_name">Last Name</label>
              </div>
            </div>
            <div class="row">
              <div class="input-field col s12">
                <input 
                  id="password" 
                  name="password" 
                  type="password" 
                  class="validate" 
                  required>
                <label for="password">Password</label>
              </div>
            </div>
            <div class="row">
              <div class="input-field col s12">
                <input 
                  id="email" 
                  name="email" 
                  type="email" 
                  class="validate" 
                  required>
                <label for="email">Email</label>
              </div>
            </div>
            <button 
              id="submit-button" 
              name="submit-button" 
              type="submit" 
              class="waves-effect waves-light btn">
              <i class="material-icons right">send</i>Submit
            </button>
          </form>
        </div>
      </div>
      <div class="row">
        <div class="flow-text status" id="submit-status" hidden></div>
      </div>
    </div>
    <?!= include('Sidebar.js') ?>  
  </body>
</html>

Sidebar.js.html

The client-side JavaScript for the sidebar, included in the main Sidebar.html file.

Note:

  • The jQuery and Materialize JS is included here.
  • onSidebarFormSubmit() – this custom “on submit” function sets, hides and shows the various divs.
  • event.preventDefault() – this stops the default action which would be to try and send the form to a remote server, whereas the script simply writes it to the local spreadsheet.
  • google.script.run.onFormSubmit() – this is the main call from the client to the server-side code (in Server.gs) that writes the form entries to the spreadsheet.
  • withSuccessHandler() and withFailureHandler() – act appropriately depending on whether the code is successfully written or not.
<script
  src="https://code.jquery.com/jquery-3.5.1.min.js"
  integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0="
  crossorigin="anonymous"></script>
  
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>

<script>

  $(document).ready(function() {  
    $('#test-form').submit(onSidebarFormSubmit)
  })

  function onSidebarFormSubmit() {
  
    event.preventDefault()
 
    var status = $('#submit-status')
    var button = $('#submit-button')
    var form = $('#test-form')
  
    status.hide()
    button.text('Submitting...')
    button.addClass('disabled')

    google.script.run
      .withSuccessHandler(function() {        
        form.hide()
        status.removeClass('error')
        status.text('Form submitted OK!')
        status.show()
      })
      .withFailureHandler(function(error) {
        status.addClass('error')
        status.text(error)
        status.show()
        button.text('Submit')
        button.removeClass('disabled')
      })
      .onFormSubmit(form.get()[0]);  
  }

</script>

Sidebar.css.html

The client-side CSS for the sidebar, included in the main Sidebar.html file.

The Materialize CSS is included here along with a small bit of custom CSS.

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

<style type="text/css">

  .error {
    color:#FF4081;  
  }
  
  .status {
    padding-left:10px;
  }
  
</style>

Server.gs

The server-side code that:

  • onOpen() – Creates the custom “Sidebar” menu.
  • onOpenSidebar() – Opens the sidebar.
  • onFormSubmit() – Writes the form entries into the Results tab of the active spreadsheet, creating the tab if required (getSheet_()).
  • include() – Used to include the separate CSS and JS files into the main sidebar HTML file.
function onOpen() {
  SpreadsheetApp
    .getUi()
    .createMenu('Sidebar')
    .addItem('Open', 'onOpenSidebar')
    .addToUi()
}

function onOpenSidebar() {

  var ui = HtmlService.createTemplateFromFile('Sidebar')
    .evaluate()
    .setTitle('Materialize CSS Sidebar Example')
  
  SpreadsheetApp.getUi().showSidebar(ui)
}

function onFormSubmit(form) {

  var spreadsheet = SpreadsheetApp.getActive()
  var sheet = getSheet_(spreadsheet) 
  sheet.appendRow([
    new Date(),
    form.first_name,
    form.last_name,
    form.password,
    form.email
  ])
}

function getSheet_(spreadsheet) {
  
  var sheet = spreadsheet.getSheetByName('Results')  
  
  if (sheet === null) {
    sheet = spreadsheet.insertSheet().setName('Results')
  }
  
  if (sheet.getRange('A1').getValue() === '') {
  
    sheet
      .getRange('A1:E1')
      .setValues([['Timestamp', 'First Name', 'Last Name', 'Password', 'Email']])
      
    sheet.setFrozenRows(1)
  }
  
  return sheet
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent()
}
Share
Computing, Google Apps Script, Web App Development
 |  Tags: , , , itconsultant