Google 試算表

本頁說明如何搭配使用 Google 圖表與 Google 試算表。

  1. 說明
  2. 在試算表中嵌入圖表
  3. 使用獨立試算表建立圖表

簡介

Google 圖表與 Google 試算表緊密整合。您可以在 Google 試算表中放入 Google 圖表，如此一來，Google 圖表就能從 Google 試算表擷取資料。本文件會說明如何進行這兩項操作。

無論您選擇哪種方法，每當基礎試算表有所變更時，圖表都會隨之變更。

在試算表中嵌入圖表

您可以輕鬆在試算表中加入圖表，在試算表工具列中，依序選取「插入」和「圖表」，即可選擇圖表類型及各個選項：

使用獨立試算表建立圖表

一般而言，使用者會填入資料表，並使用這些資料繪製圖表，藉此建立 Google 圖表。如要改為從 Google 試算表擷取資料，您可以查詢試算表來擷取要繪製成圖表的資料：

function drawChart() {
  var query = new google.visualization.Query(URL);
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  var data = response.getDataTable();
  var chart = new google.visualization.ColumnChart(document.getElementById('columnchart'));
  chart.draw(data, null);
}

這項功能之所以有效，是因為 Google 試算表支援 Google 圖表的查詢語言，可用於排序及篩選資料；任何支援查詢語言的系統均可做為資料來源使用。

請注意，在未明確授權的情況下，圖表不得使用查看應用程式的人的權限。試算表必須所有人都能查看，或者頁面必須明確取得使用者憑證 (如本頁「授權」一節所述)。

如要使用 Google 試算表做為資料來源，請先備妥網址：

  1. 開啟現有的試算表。這份試算表應採用圖表預期的格式，且應已正確設定檢視權限。(最簡單的方式就是查看「公開在網路上」或「擁有連結的使用者」的權限，本節的操作說明則以此方式設定試算表。如要限制共用，您可以將試算表保持「不公開」，並授予個別 Google 帳戶存取權限，但必須按照下方的授權操作說明執行。
  2. 從瀏覽器複製網址。 如要進一步瞭解如何選取特定範圍，請參閱查詢來源範圍。
  3. 將網址提供給 google.visualization.Query()。 這項查詢支援下列選用參數：
    • headers=N：指定標題列有多少列，其中 N 為整數零或大於整數。這些標籤會從資料中排除，並指派為資料表中的資料欄標籤。如未指定這個參數，試算表會猜測標題資料列的數量。請注意，如果所有資料欄都是字串資料，試算表可能難以判斷哪些資料列是不含這個參數的標題列。
    • gid=N：如果沒有連結至第一張工作表，請指定要在多工作表文件中要連結的工作表。 N 是工作表的 ID 編號。如要取得 ID 編號，請前往該工作表的已發布版本，然後找出網址中的 gid=N 參數。您也可以改用 sheet 參數來代替這個參數。Gotcha：在瀏覽器中查看時，Google 試算表可能會重新排列網址中中的 gid 參數。如果是從瀏覽器複製，請確定所有參數都位於網址 # 標記「之前」。範例：gid=1545912003。
    • sheet=sheet_name：在未連結至第一份工作表的情況下，指定您要連結的多工作表文件中哪一份工作表。sheet_name 是工作表的顯示名稱。範例：sheet=Sheet5。

以下提供完整範例：

以下是繪製這張圖表的兩種方法，一種使用 gid 參數，另一種使用 sheet 參數。在瀏覽器中輸入任一網址即可產生與圖表相同的結果/資料。

GID
    function drawGID() {
      var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?gid=0&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var data = response.getDataTable();
      var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
      chart.draw(data, { height: 400 });
    }
試算表
    function drawSheetName() {
      var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);
      query.send(handleSampleDataQueryResponse);
    }

    function handleSampleDataQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var data = response.getDataTable();
      var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
      chart.draw(data, { height: 400 });
    }

查詢來源範圍

查詢來源網址可指定用於查詢的試算表部分：特定儲存格、儲存格範圍、資料列或資料欄，或者整個試算表。請使用「range=<range_expr>」語法指定範圍，例如：

https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?range=A1:C4

以下列舉幾個語法示例：

  • A1:B10 - 儲存格 A1 到 B10 的範圍
  • 5:7 - 第 5 到 7 列
  • D:F - D 到 F 欄
  • A:A70 - A 欄的前 70 個儲存格
  • A70:A - 從第 70 列到結尾的 A 欄
  • B5:5 - B5 至第 5 列結尾
  • D3:D - D3 至 D 欄結尾
  • C:C10 - 從 C 欄的開頭到 C10

授權

Google 試算表需要使用者憑證，才能透過 Google Visualization API (「/tq 要求」) 存取私人試算表。

注意：如果試算表設為「知道連結的使用者皆可檢視」，則不需要憑證。變更試算表的共用設定比實作授權簡單得多。

如果連結共用設定不是可行的解決方案，開發人員需要更改程式碼來傳遞 Google Sheets API 範圍 (https://www.googleapis.com/auth/spreadsheets) 的授權 OAuth 2.0 憑證。

如需更多 OAuth 2.0 背景資訊，請參閱「使用 OAuth 2.0 存取 Google API」一文

範例：使用 OAuth 存取 /gviz/tq

事前準備：從 Google Developers Console 取得用戶端 ID

如需進一步瞭解如何整合 Google 的 Identity Platform，請參閱「Google 登入」和「建立 Google API 控制台專案和用戶端 ID」。

要取得使用者的 OAuth 權杖，您必須先透過 Google Developers Console 註冊專案，並取得用戶端 ID。

  1. 在 Play 管理中心建立新的 OAuth 用戶端 ID。
  2. 選擇「Web application」(網頁應用程式) 做為應用程式類型。
  3. 請挑選一個名稱，這個名稱僅供參考。
  4. 將您的網域 (及任何測試網域) 新增為「已授權的 JavaScript 來源」。
  5. 將「AuthorizedRedirect URIs」(授權的重新導向 URI) 留白。

按一下「建立」後，請複製用戶端 ID 供日後參考。這項練習不需要用戶端密鑰。

更新網站以取得 OAuth 憑證。

Google 提供 gapi.auth 程式庫，可大幅簡化取得 OAuth 憑證的程序。下方的程式碼範例會使用這個程式庫取得憑證 (視需要要求授權)，並將產生的憑證傳遞至 /gviz/tq 端點。

demo.html 
<html>
<body>
  <button id="authorize-button" style="visibility: hidden">Authorize</button>
  <script src="./demo.js" type="text/javascript"></script>
  <script src="https://apis.google.com/js/auth.js?onload=init"></script>
</body>
</html>
demo.js 
// NOTE: You must replace the client id on the following line.
var clientId = '549821307845-9ef2xotqflhcqbv10.apps.googleusercontent.com';
var scopes = 'https://www.googleapis.com/auth/spreadsheets';

function init() {
  gapi.auth.authorize(
      {client_id: clientId, scope: scopes, immediate: true},
      handleAuthResult);
}

function handleAuthResult(authResult) {
  var authorizeButton = document.getElementById('authorize-button');
  if (authResult && !authResult.error) {
    authorizeButton.style.visibility = 'hidden';
    makeApiCall();
  } else {
    authorizeButton.style.visibility = '';
    authorizeButton.onclick = handleAuthClick;
  }
}

function handleAuthClick(event) {
  gapi.auth.authorize(
      {client_id: clientId, scope: scopes, immediate: false},
      handleAuthResult);
  return false;
}

function makeApiCall() {
  // Note: The below spreadsheet is "Public on the web" and will work
  // with or without an OAuth token.  For a better test, replace this
  // URL with a private spreadsheet.
  var tqUrl = 'https://docs.google.com/spreadsheets' +
      '/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq' +
      '?tqx=responseHandler:handleTqResponse' +
      '&access_token=' + encodeURIComponent(gapi.auth.getToken().access_token);

  document.write('<script src="' + tqUrl +'" type="text/javascript"></script>');
}

function handleTqResponse(resp) {
  document.write(JSON.stringify(resp));
}

授權成功後，gapi.auth.getToken() 會傳回所有憑證詳細資料，包括可附加至 /gviz/tq 要求的 access_token。

如要進一步瞭解如何使用 gapi 程式庫進行驗證，請參閱：

使用 drive.file 範圍

上述範例使用 Google Sheets API 範圍，授予使用者所有試算表內容的讀取和寫入權限。視應用程式而定，這一權限可能比必要更高。如需唯讀存取權，請使用 spreadsheets.readonly 範圍，授予使用者工作表及其屬性的唯讀存取權。

drive.file 範圍 (https://www.googleapis.com/auth/drive.file) 只會授予使用者透過 Picker API 啟動的 Google 雲端硬碟檔案選擇器，明確開啟的檔案存取權。

使用 Picker 可變更應用程式的流程。使用者不必像上例，貼上網址或採用硬式編碼的試算表，而是必須使用 Picker 對話方塊來選擇要讓頁面存取的試算表。遵循挑選器「Hello World」範例，使用 google.picker.ViewId.SPREADSHEETS 取代 google.picker.ViewId.PHOTOS。