[Blogger] 透過 Google 試算表取得資料並隨機顯示內容

我在 Google Blogger  小工具上張貼一些詩詞時便在想：「如何能隨機地顯示這些文字，又可以容易的擴充或刪除呢？」較簡單的方法，是在小工具中建立一個物件陣列來儲存這些詩詞和作者的資料，再隨機由陣列中取用某一物件來顯示。但這樣做的缺點是：每次修改都需更動程式碼中的物件陣列，那麼有沒有方法將資料和程式碼分開呢？網路搜尋後發現 Google 試算表（Google Spreadsheet）有提供 API 來取用表格資料，支援 JavaScript 語言，這也讓 Blogger 有機會使用試算表來儲存資料，而後再動態讀取資料進行操作。

 

這篇文章記錄如何取用 Google 試算表中的資料，並實作隨機取用存於試算表中的文字進行顯示。主題分為二大部份，首先是如何建立公開的 Google 試算表；而後則是使用 JavaScript 取得資料來顯示。
 

效果如下，每次頁面重載後便會隨機顯示試算表中的一列資料：

 

 

文章目錄

- [1] 建立 Google spreadsheet 並取得其 ID         - <1> 建立欄位標題         - <2> 填寫欄位         - <3> 試算表發布         - <4> 取得 Spreadsheet ID - [2] 取用 Google spreadsheet 進行操作         - <1> 建立 Google Sheets API 使用的試算表 URL         - <2> 取得試算表         - <3> 隨機取得某一列後顯示 - [3] 結語/心得 - [4] 參考

 

[1] 建立 Google spreadsheet 並取得其 ID

 

登入 Google 雲端硬碟，建立試算表：

此時網址列是 [Edit] 連結，連結中包含 Spreadsheet ID。

 

<1> 建立欄位標題

第一列（row）中的每一欄（column）填入欄位標題，如下面的 title、content、author。JavaScript 中的程式將以欄位標題為 key name 來取得試算表中的值。

 

<2> 填寫欄位

以每列為一組單位，將值填入每一欄位。

 

 

<3> 試算表發布

檔案>發布到網路，將整份文件發布。發布後的連結為 [Public] 公開連結，所有人都可透過此連結讀取試算表。

 

 

 

 

<4> 取得 Spreadsheet ID

透過 [Edit] 連結取得 Spreadsheet ID，參考：

https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id

 

◎ [Edit] 連結

https://docs.google.com/spreadsheets/d/1yb_eL-Tqhp8ZXDsK87ePa8nl1qkB4AvRVSO4G6sfaxk/edit#gid=0

 

xxx/d/SPREAD_SHEET_ID/edit#gid=0

從 /d/ 之後，到 /edit 之前即為 SPREAD_SHEET_ID

https: //docs.google.com/spreadsheets/d/1yb_eL-Tqhp8ZXDsK87ePa8nl1qkB4AvRVSO4G6sfaxk/edit#gid=0

因此 SPREAD_SHEET_ID 為： 1yb_eL-Tqhp8ZXDsK87ePa8nl1qkB4AvRVSO4G6sfaxk。

◎ [Public] 連結

https://docs.google.com/spreadsheets/d/e/2PACX-1vRZEcDY8sXqEWL6mGuens4herPTErafVJKGN38xRI5pv9-CGOVv0ZBA8-3aydSUaOPZadYLVV-dZ5-o/pubhtml

供任何知道此連結的人檢視試算表，但其內沒有 spreadsheet ID。

 

 

[2] 取用 Google spreadsheet 進行操作

 

<1> 建立 Google Sheets API 使用的試算表 URL

格式為：

https://spreadsheets.google.com/feeds/list/ SPREAD_SHEET_ID /od6/public/values?alt=json

 

程式碼範例如下：

var urlSheetId = '1yb_eL-Tqhp8ZXDsK87ePa8nl1qkB4AvRVSO4G6sfaxk'; var urlJson = 'https://spreadsheets.google.com/feeds/list/' + urlSheetId + '/od6/public/values?alt=json';

 

<2>  取得試算表

使用 $.getJSON 呼叫 Google Sheets API 取得試算表，並處理欄位資料。程式碼範例：

$.getJSON(urlJson, function (json){         for (var i=0; i < json.feed.entry.length; i++){             title[i] = json.feed.entry[i].gsx$title.$t;             content[i] = json.feed.entry[i].gsx$content.$t;             author[i] = json.feed.entry[i].gsx$author.$t;             $('#wordTable').append("<tr><td>" + title[i] + "</td><td>" + content[i] + "</td><td>" + author[i] + "</td></tr>");         }     });

 

gsx$xxx 代表每一欄位 key name，xxx 為欄位標題（即第一列中的每個欄位名稱）：

gsx$title	title
gsx$content	content
gsx$author	author

 

<3> 隨機取得某一列後顯示

完整程式碼如下：

<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>   <!-- random content --> <div id="goldword"> </div>   <style> #goldword{     /* enable for debug     width:260px;     height:80px     border:1px gray solid;     color: #FF0000; /* color of text */     */     word-break:break-all;     word-wrap: break-word;     font-size: 10pt; } #goldword pre{     white-space: pre-wrap;     word-wrap: break-word; }  </style>   <script> //<![CDATA[    function getRndInteger(min, max) {     return Math.floor(Math.random() * (max - min) ) + min; }   // // Retrieve data from: // https://docs.google.com/spreadsheets/d/1yb_eL-Tqhp8ZXDsK87ePa8nl1qkB4AvRVSO4G6sfaxk/edit#gid=0 //   function randomContent () {     var urlSheetId = '1yb_eL-Tqhp8ZXDsK87ePa8nl1qkB4AvRVSO4G6sfaxk';     var urlJson = 'https://spreadsheets.google.com/feeds/list/' + urlSheetId + '/od6/public/values?alt=json';       $.getJSON(urlJson, function (json){         var index = getRndInteger (0, json.feed.entry.length);           var $txtTitle = $("<pre id='txttitle'>" + json.feed.entry[index].gsx$title.$t + "</pre>");              var $txtContent = $("<pre id='txtcontent'>" + json.feed.entry[index].gsx$content.$t + "</pre>");         var $txtAuthor = $("<pre id='txtauthor'>" + json.feed.entry[index].gsx$author.$t + "</pre>");           var $goldword = $('#goldword')         $goldword.append($txtTitle);         $goldword.append($txtContent);         $goldword.append($txtAuthor);     }); }   $(function(){ //    listAll ();     randomContent (); });   //]]> </script>

 

 

[3] 結語/心得

 

[1] 小工具中的程式碼在每次讀取文章時都會重載，試算表會每次都被重讀，因此缺點很明顯：這個做法會拖慢網頁載入的時間，資料量愈多所需時間愈長。資料不要太多，控制在合理的數目是較好的做法。

 

[2] Some programming tips here：

◎ <pre> element auto-wrap

pre {     white-space: pre-wrap;     word-wrap: break-word; }

Refer to: How to use Pre tag to restrict word break in HTML 

◎ <div> element auto-wrap

#DIV_ID {     white-space:pre-line;     word-break:break-all; }

◎ Random number generator

function getRndInteger(min, max) { // min <= return num < max     return Math.floor(Math.random() * (max - min) ) + min; }

Refer to: JavaScript Random

  

[4] 參考

 

【心得】[JQuery推廣] 以Google Spreadsheet為網頁資料庫

寫給純前端，讓 Google Sheets 當你的後端完成寫入功能