readme.markdown

omeka historical maps datatable

html to create an omeka page that displays the contents of a google spreadsheet.

setting up the google sheet

• File => Publish to the Web
• choose "Entire Document" and "Web Page", then publish.

adding the table to omeka-s

Copy and paste the entire HTML blob into an HTML block. Update the googleSheetId JavaScript variable (see line 72) with the ID of the published Google Sheet (the value after https://docs.google.com/spreadsheets/d/ and before /edit).

For additional DataTable functionality, visit the docs.

omeka-historical-maps-datatable.html

<!-- v3 updated to parse CSV output from gviz url -->
<style type="text/css">/* table styling */
#data-table {
  font-size: .85em;
  table-layout: fixed;
  width: 100%;
}

#data-table th {
  background-color: transparent;
  cursor: pointer;
}
#data-table .sorting_asc,
#data-table .sorting_desc {
  background-color: #8a1700;
  color: #ffffff;
}

#data-table .sorting::after,
#data-table .sorting_asc::after,
#data-table .sorting_desc::after { margin-left: .125em; }

#data-table .sorting::after { content: "\2195"; }
#data-table .sorting_asc::after { content: "\2191"; }
#data-table .sorting_desc::after { content: "\2193"; }

.dt-utils {
  display: flex;
  justify-content: space-between;
}

.paginate_button {
  border: 1px solid #ccc;
  cursor: pointer;
  display: inline-block;
  padding: .25em .5em;
}

.paginate_button.current {
  background-color: #eee;
}

.paginate_button:not(.previous,.next) {
  margin: .5em;
}
</style>

<table class="compact order-column" id="data-table">
  <thead>
    <tr>
      <th style="width:40%;">Map</th>
      <th style="width:10%;">Date</th>
      <th style="width:10%;">Time Period</th>
      <th style="width:10%;">Region</th>
      <th style="width:10%;">Institution</th>
      <th style="width:10%;">Map ID</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>Map</th>
      <th>Date</th>
      <th>Time Period</th>
      <th>Region</th>
      <th>Institution</th>
      <th>Map ID</th>
    </tr>
  </tfoot>
</table>
<script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.js"></script>
<script>
var googleSheetId = '';
  
// adapted from https://gist.github.com/Jezternz/c8e9fafc2c114e079829974e3764db75
function csvRowToArray(data) {
  var re = /(,|\r?\n|\r|^)(?:"([^"]*(?:""[^"]*)*)"|([^,\r\n]*))/gi
  var result = []
  let matches

  while ((matches = re.exec(data))) {
    if (matches[1].length && matches[1] !== ',') result.push([])
    result.push(
      matches[2] !== undefined ? matches[2].replace(/""/g, '"') : matches[3]
    )
  }
  return result
}

function parseCSVdata(text) {
  var rows = text.split('\n').map(function (row) { return csvRowToArray(row); });
  var headers = rows.shift()
                    .filter(function (n) { return n; }) // remove blanks
                    .map(function (h) { return h.toLowerCase().replace(' ', '_')}) // downcase + remove spaces
                    ;
  var headerCount = headers.length;
  // google will send us a csv with a ton of empty cells, this reduces the row to match the # of headers
  var fixedRows = rows.map(function (row) {
    return row.filter(function (_n, idx) { return (idx + 1) <= headerCount; });
  });

  // then convert the rows into objects so it's easer to specify which columns to use
  return fixedRows.map(function (row) {
    return row.reduce(function (obj, value, idx) {
      obj[headers[idx]] = value;
      return obj
    }, {});
  });
}

$(document).ready(function () {
  $('#data-table').DataTable({
    dom: '<<"dt-utils"if><t>lp>',
    ajax: function (_data, callback, _settings) {
      $.ajax('https://docs.google.com/spreadsheets/d/' + googleSheetId + '/gviz/tq?tqx=out:csv')
        .done(function (data) {
          var parsed = parseCSVdata(data);
          console.log(parsed)
          return callback({data: parsed});
        })
    },
    columns: [
      { data: 'map' },
      { data: 'date' },
      { data: 'time_period' },
      { data: 'region' },
      { data: 'institution' },
      { data: 'map_id' }
    ]
  });
});
</script>

<p>&nbsp;</p>