# Week 7(10/23)：用Google Chart讀取Google試算表
## 從Google試算表中讀取資料並顯示
要讀取的試算表網址寫法：
https://docs.google.com/spreadsheets/d/瀏覽器網址列上的網址擷取序號部分/gviz/tq?sheet=工作表名稱
範例程式碼：
```html=
<!DOCTYPE html>
<html>
<head>
<!-- 利用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.//設定API有空時回傳資料的對象。
google.charts.setOnLoadCallback(init);
function init() {
//url放要讀取的表單網址
var url ='https://docs.google.com/spreadsheets/d/1QScI5d56Kh-LGywY4WR6EvRGPcalFQYUAdqMxbFVDvk/gviz/tq?sheet=工作表1';
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(); //列數
var str = "";
for (var r=0 ; r<rows ; r++) {
str += data.getFormattedValue(r,0) + "(" + data.getFormattedValue(r,1) + ")" + " 訂 " + data.getFormattedValue(r,2) + " : " + data.getFormattedValue(r,3) + "個 <br />";
}//data.getFormattedValue(r,0)：取得第r列0行的資料
document.getElementById("demo").innerHTML = str;
}
</script>
</head>
<body>
<div id="demo"></div>
</body>
</html>
```
更多語法：https://developers.google.com/chart/interactive/docs/querylanguage?hl=zh-tw#language-clauses
## 應用：生成卡片
google試算表：https://docs.google.com/spreadsheets/d/1TNbPscrJgsMjxd5sm5hiIIBnLGXpnWm57GdXxzsAPbk/edit#gid=0
自己練習程式碼：
```html=
<!DOCTYPE html>
<html>
<head>
<!-- Compiled and minified CSS -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">
<!-- Compiled and minified JavaScript -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.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);
x=["","","","","","","","","","",""];
a=["","","","","","","","","","",""];
num=["","","","","","","","","","",""];
function init() {
var url =
'https://docs.google.com/spreadsheets/d/1TNbPscrJgsMjxd5sm5hiIIBnLGXpnWm57GdXxzsAPbk/gviz/tq?sheet=工作表1';
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();
var str = "";
for (var r=0 ; r<rows ; r++) {
x[r]=data.getFormattedValue(r,0);
a[r]=data.getFormattedValue(r,1);
num[r]=data.getFormattedValue(r,2);
}
//document.getElementById("demo").innerHTML = str;
}
function f()
{
var s='';
for(i=0;i<x.length;i++){
s +=
`<div class="col s12 m6 l4">
<div class="card">
<div class="card-image">
<div id="demo">
<img src="${x[i]}">
</div>
<span class="card-title">MOMO</span>
</div>
<div class="card-content" id="text">
<p>${a[i]}</p>
</div>
<div class="card-action">
<a href="test_gomaji.html?XXX=${num[i]}">This is a link</a>
</div>
</div>
</div>`;
}
document.getElementById("tt").innerHTML = s;
}
</script>
<!--Import Google Icon Font-->
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<!--Import materialize.css-->
<link type="text/css" rel="stylesheet" href="css/materialize.min.css" media="screen,projection"/>
<!--Let browser know website is optimized for mobile-->
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<!--JavaScript at end of body for optimized loading-->
<script type="text/javascript" src="js/materialize.min.js"></script>
<div class="row" id="tt">
</div>
<a class="waves-effect waves-light btn" href="javascript:f();"><i class="material-icons left">pets</i>Create Card</a>
</body>
</html>
```
## 相關資料
陳育毅老師網站：https://nchu-mis-html5.blogspot.com/2017/07/tabletopjs.html
Sign in with Wallet
Connect another wallet