Skip welcome & menu and move to editor
Welcome to JS Bin
File
Add library
Share
Login or Register
Blog
Help
Load cached copy from
  • Run with JS
  • Save
  • Share
  • …
 
<!DOCTYPE html>
<html>
  <head>
  
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.4/jquery.min.js" integrity="sha512-pumBsjNRGGqkPzKHndZMaAG+bir374sORyzM3uulLV14lN5LyykqNk8eEeUlUkB3U0M4FApyaHraT65ihJhDpQ==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    
    <!-- 利用DataTable呈現表格 https://datatables.net/manual/installation -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />      
    <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>  
  
  
    <!-- 利用Google Visualization讀取Google Sheet https://www.labnol.org/code/google-sheet-d3js-visualization-200608 -->
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    
    <script>
    
    // https://developers.google.com/chart/interactive/docs/quick_start
    // Load the Visualization API and the corechart package.
    google.charts.load('current', {'packages':['corechart']});
​
    // Set a callback to run when the Google Visualization API is loaded.   
    google.charts.setOnLoadCallback(init);
​
    function init() {
      // 這個例子使用的試算表是 https://docs.google.com/spreadsheets/d/14O1tvCnp4FozwHnRjwp_e-NQCIYZ2xK6i6jZt8K0cCc/copy
      // 將上述試算表另存副本後，你要在自己的雲端硬碟將該試算表設定成[知道連結的使用者都能查看]。
      // 然後讓程式讀取 https://docs.google.com/spreadsheets/d/瀏覽器網址列上的網址擷取序號部分/gviz/tq?sheet=工作表名稱
      // 也就是將下列的 14O1tvCnp4FozwHnRjwp_e-NQCIYZ2xK6i6jZt8K0cCc 更換成自己的檔案序號，以及最後部分指讀取哪個工作表。
      var url =
        'https://docs.google.com/spreadsheets/d/14O1tvCnp4FozwHnRjwp_e-NQCIYZ2xK6i6jZt8K0cCc/gviz/tq?headers=0&sheet=最新消息';
      var query = new google.visualization.Query(url);
      query.setQuery('select *');
      query.send(processSheetsData);
    }   
    
    function processSheetsData(response) {
      var data = response.getDataTable();
      var columns = data.getNumberOfColumns();
      var rows = data.getNumberOfRows();
​
      // 表格以DataTable呈現設定class="display order-column"
      var str = `<table id="myTable" class="display order-column">
                    <thead>
                        <tr>`;
      // 此迴圈取得試算表第一列的各欄位標題加以安排呈現
      for (var c=0 ; c<columns ; c++) { 
          if (data.getFormattedValue(0,c)=='') columns=c; //可能試算表後面有多欄空白，就要將columns數值訂為到此為止
          else if (data.getFormattedValue(0,c)!='*')
             str +=         '<th>' + data.getFormattedValue(0,c) + '</th>';         
      }  
      // 後續的Tag...........
          str += `      </tr>
                    </thead>
                    <tbody>`;
​
      // 此迴圈取得試算表第二列起的資料逐一安排呈現
      for (var r=1 ; r<rows ; r++) {
          str += '<tr>';
          // 此迴圈取得試算表第r列的各欄位內容加以安排呈現
          for (c=0 ; c<columns ; c++) { 
              if (data.getFormattedValue(0,c)=='*') { // 該欄第一列標題為*就是下一欄的超連結網址
                 if (data.getFormattedValue(r,c)!="") {
                    str += '<td><a href="' + data.getFormattedValue(r,c) + '" target="_blank">' + data.getFormattedValue(r,c+1) + '</a></td>';
                    c++;
                 }
              } 
              else
                 str += '<td>' + data.getFormattedValue(r,c) + '</td>'; 
          }
          // 後續的Tag...
          str += '</tr>';
      }   
      str += '</tbody></table>';
      
      document.getElementById("demo").innerHTML = str;
​
      
      // 利用DataTable呈現表格 https://datatables.net/manual/installation
      $(document).ready( function () {
          $('#myTable').DataTable({
            // Language Plug-in for Chinese-traditional https://datatables.net/plug-ins/i18n/Chinese-traditional.html
            language: {url: '//cdn.datatables.net/plug-ins/1.13.4/i18n/zh-HANT.json'},
            // Disable ordering in the table https://datatables.net/reference/option/ordering
            // ordering: false
            // The order parameter https://datatables.net/examples/basic_init/table_sorting.html
            order: [[0, 'desc']]
          });
      } );    
      
    }
    
    </script>
​
  </head>
​
  <body>
    <div id="demo"></div>
  </body>
​
</html>
Output

You can jump to the latest bin by adding /latest to your URL

Dismiss x
public
Bin info
anonymouspro
0viewers