Date Based Sheetrock.js Query

Queries a Google Sheets spreadsheet using a date-based Sheetrock.js query.

by Michael Wender

HTML

<script src="//code.jquery.com/jquery-2.1.4.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery-sheetrock/1.0.0/dist/sheetrock.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/handlebars.js/1.1.2/handlebars.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/milligram/1.1.0/milligram.min.css">
<table id="events-table">
  <thead>
    <tr>
      <th>Date</th>
      <th>Time</th>
      <th>City</th>
      <th>Venue/Host</th>
      <th>Tickets</th>
    </tr>
  </thead>
  <script id="event-row-template" type="text/x-handlebars-template">
    <tr class="event-row event-{{cells.Tickets}}">
      <td>{{cells.Date}}</td>
      <td>{{cells.Start_Time}} - {{cells.End_Time}}</td>
      <td>{{cells.City}}</td>
      <td><a href="{{cells.Map_Link}}" target="_blank" title="View on Google Maps">{{cells.Host}}</a></td>
      <td><a href="https://www.itickets.com/register/new/{{cells.Tickets}}" class="button buytickets" target="_blank" title="Buy Tickets for Love Worth Fighting For Marriage Event in {{cells.City}} on {{cells.Date}}">Buy Tickets</a><a class="button soldout button-disabled"
        title="Love Worth Fighting For Marriage Event in {{cells.City}} on {{cells.Date}} is SOLD OUT">SOLD OUT</a></td>
    </tr>
  </script>
</table>

CSS

#events-table thead tr.event-row {
  display: none;
}

table.striped tbody tr:nth-child(odd) {
  background-color: #eee;
}

table.striped tr th:first-child,
table.striped tr td:first-child {
  padding-left: 8px;
}

#events-list {
  list-style: none;
  border-top: 1px solid #dd2127;
  margin-left: -2rem;
  margin-right: -2rem;
}

#events-list li {
  border-bottom: 1px solid #dd2127;
  padding: 20px;
  margin: 0;
  text-align: center;
  background: #000;
  color: #fff;
}

#events-list li .logo {
  background: transparent url(../images/love-worth-fighting-for.2200x380.png) no-repeat center;
  background-size: contain;
  height: 60px;
  margin-bottom: 20px;
}

#events-list li:first-of-type {
  padding-top: 20px;
}

.soldout {
  display: none;
}

.event-soldout .buytickets {
  display: none;
}

.event-soldout .soldout {
  display: inline-block;
}

JavaScript

Date.prototype.yyyymmdd = function() {
  var yyyy = this.getFullYear().toString();
  var mm = (this.getMonth() + 1).toString(); // getMonth() is zero-based
  var dd = this.getDate().toString();
  return yyyy + '-' + (mm[1] ? mm : "0" + mm[0]) + '-' + (dd[1] ? dd : "0" + dd[0]); // padding
};
var date = new Date();
var today = date.yyyymmdd();

var EventSheet = 'https://docs.google.com/spreadsheets/d/19lC67SDAvkcMeJTn6fvMRR0MzxhTTmOz-WEQu4jwBA0/edit#gid=0';
var EventRowTemplate = Handlebars.compile($('#event-row-template').html());
var EventQuery = "SELECT B,C,D,E,F,H,I WHERE J='publish' AND K >= '" + today + "' ORDER BY A ASC";

// Desktop View
$('#events-table').sheetrock({
  url: EventSheet,
  query: EventQuery,
  rowTemplate: EventRowTemplate,
  labels: ['Date', 'Start_Time', 'End_Time', 'City', 'Host', 'Map_Link', 'Tickets'],
  headersOff: true
});