Code.gs 檔

function doGet() {
  var html = HtmlService.createTemplateFromFile("DataTable");
  var check = html.evaluate();
  // 避免無法顯示，使用XFrame allow all
  var display = check.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  return display;
}


function getData() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSheet.getSheetByName('Sheet1'); // Replace with your sheet name
  
  // Get the range for columns A and D
  var range = sheet.getRange("A1:D8");
  var data = range.getValues();
  
  // Extract columns A and D from the data array
  var filteredData = data.map(function(row) {
    return [row[0], row[3]]; // Keep only values from columns A and D
  });
  
  return filteredData;
}

DataTable.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

    <!-- JQuery連結 -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"> </script>

    <!-- DataTable Links連結 -->

    <!-- <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.css"> -->
  
<!-- <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.js"></script> -->
  
  <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css"/>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs5/dt-1.11.3/datatables.min.css"/>
 
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/js/bootstrap.bundle.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/bs5/dt-1.11.3/datatables.min.js"></script>



    <!-- RWD Javascript連結 -->
    <script src="https://cdn.datatables.net/1.11.2/js/jquery.dataTables.min.js"></script>

    <script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script>



    <!-- RWD CSS連結 -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.2/css/jquery.dataTables.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.dataTables.min.css">



  <!-- 抓資料 從試算表 -->

    <script>
            google.script.run.withSuccessHandler(showData).getData();
          
            function showData(dataArray){
            $(document).ready(function(){
              $('#data-table').DataTable({

                
                data: dataArray,
              
                columns: [
                  {"title":"ID"},
                  {"title":"姓名"},
                  {"title":"生日"},
             {"title":"電子郵件"},
                
                ]
              });
            });
          }

</script>

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


  </body>
</html>

發表迴響