How can I force Google Visualization Table to use my header row?

Question

I have two pages, each of which load a different sheet from my Google Sheets document. The two pages are identical except for the sheets they query. I'm using the Table visualization on my pages.

Both load a visualization.

However, only one of them gives me what I would consider a "real" header row - that is, it's sortable (I can click the top of a column to sort my data by that column), its appearance is unique (bold, slightly different background), it's frozen in place so that it stays at the top as I scroll.

It seems like the visualization tool is not "detecting" my header row...

How can I get it to recognize my first row as a header?

This is the "bad" code. Using sheet "Crew_Basics" it works fine.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">  
    <?var url = getScriptUrl();?>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['table']});
      google.charts.setOnLoadCallback(drawTable);
      function drawTable() {
        var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1I3N5DtdXGWFootaOCQM201K_ao2ZPWSWyw9_l7QcwQg/gviz/tq?sheet=Crew_Traits');
        query.send(handleQueryResponse);
      }

      function handleQueryResponse(response) {
        if (response.isError()) {
          alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
          return;
        }

        var data = response.getDataTable();
        var options = { height: 500 }

        var chart = new google.visualization.Table(document.getElementById('source_table_div'));
        chart.draw(data, options)
      }
    </script>

  </head>
  <body>
    <div id="full_page_grid_container">
      <div class="title"><h1>Select Your Crew</h1></div>
      <div class="link_home"><a href="<?=url?>">Return Home</a><br /></div>
      <div id="source_table_div" class="google_table"></div>
    </div>
  </body>
</html>

TheMaster TheMaster · Accepted Answer · 2019-05-22T15:37:29

You can set the optional header parameter in the query:

headers=N: Specifies how many rows are header rows, where N is an integer zero or greater. These will be excluded from the data and assigned as column labels in the data table. If you don't specify this parameter, the spreadsheet will guess how many rows are header rows. Note that if all your columns are string data, the spreadsheet might have difficulty determining which rows are header rows without this parameter.

var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/gviz/tq?sheet=Crew_Traits&headers=1');

How can I force Google Visualization Table to use my header row?

1 Answers