bpwebs/#How to pull data from Google Sheets to HTML table.txt

## #How to pull data from Google Sheets to HTML table.txt
How to pull data from Google Sheets to HTML table

## Code.gs
//https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
function getData(){
  var spreadSheetId = "1tMODRuz4T5MYVOGtdLV5j5EqX1MKoz4F_RySpr0YLdE"; //CHANGE
  var dataRange     = "Data!A2:F"; //CHANGE

  var range   = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
  var values  = range.values;

  return values;
}

//INCLUDE JAVASCRIPT AND CSS FILES
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

## Index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">

    <?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
  </head>

  <body>
    <div class="container">
      <br>
      <div class="row">
        <table id="data-table" class="table table-striped table-sm table-hover table-bordered">
          <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
        </table>
      </div>
    </div>
  </body>
</html>

## JavaScript.html
<script>
  /*
  *THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE,
  *AND PASS RETURNED DATA TO showData() FUNCTION
  */
  google.script.run.withSuccessHandler(showData).getData();

  //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
  function showData(dataArray){
    $(document).ready(function(){
      $('#data-table').DataTable({
        data: dataArray,
        //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
        columns: [
          {"title":"Rating"},
          {"title":"Reviews"},
          {"title":"Book title"},
          {"title":"Number of Pages"},
          {"title":"Type"},
          {"title":"Price"}
        ]
      });
    });
  }
</script>
	//https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/

	function doGet() {
	return HtmlService.createTemplateFromFile('Index').evaluate();
	}

	//GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY
	function getData(){
	var spreadSheetId = "1tMODRuz4T5MYVOGtdLV5j5EqX1MKoz4F_RySpr0YLdE"; //CHANGE
	var dataRange = "Data!A2:F"; //CHANGE

	var range = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange);
	var values = range.values;

	return values;
	}

	//INCLUDE JAVASCRIPT AND CSS FILES
	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.getContent();
	}
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	<!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES-->
	<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
	<script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
	<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
	<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">

	<?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE-->
	</head>

	<body>
	<div class="container">
	<br>
	<div class="row">
	<table id="data-table" class="table table-striped table-sm table-hover table-bordered">
	<!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE -->
	</table>
	</div>
	</div>
	</body>
	</html>
	<script>
	/*
	*THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE,
	*AND PASS RETURNED DATA TO showData() FUNCTION
	*/
	google.script.run.withSuccessHandler(showData).getData();

	//THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY
	function showData(dataArray){
	$(document).ready(function(){
	$('#data-table').DataTable({
	data: dataArray,
	//CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE
	columns: [
	{"title":"Rating"},
	{"title":"Reviews"},
	{"title":"Book title"},
	{"title":"Number of Pages"},
	{"title":"Type"},
	{"title":"Price"}
	]
	});
	});
	}
	</script>