Google Spreadsheets

หน้านี้จะอธิบายวิธีใช้ Google แผนภูมิกับ Google สเปรดชีต

  1. บทนำ
  2. การฝังแผนภูมิในสเปรดชีต
  3. การสร้างแผนภูมิจากสเปรดชีตแยกต่างหาก

บทนำ

มีการรวม Google แผนภูมิและ Google สเปรดชีตเข้าด้วยกัน คุณสามารถวาง Google Chart ภายในสเปรดชีตของ Google และ Google Charts สามารถดึงข้อมูลออกจาก Google สเปรดชีต เอกสารประกอบฉบับนี้ จะแสดงวิธีทำทั้ง 2 อย่าง

ไม่ว่าคุณจะเลือกใช้วิธีใด แผนภูมิของคุณจะเปลี่ยนไปทุกครั้งที่สเปรดชีตด้านล่างนี้มีการเปลี่ยนแปลง

การฝังแผนภูมิ ในสเปรดชีต

การรวมแผนภูมิไว้ในสเปรดชีตนั้นทำได้ง่าย จากแถบเครื่องมือสเปรดชีต ให้เลือก "แทรก" แล้วเลือก "แผนภูมิ" จากนั้นคุณจะสามารถเลือกประเภทของแผนภูมิและเลือกตัวเลือกได้มากมาย ดังนี้

การสร้างแผนภูมิจากสเปรดชีตแยกต่างหาก

โดยทั่วไป ผู้คนสร้าง 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 Charts สำหรับการจัดเรียงและกรองข้อมูล ระบบใดๆ ที่รองรับภาษาคำค้นหาสามารถใช้เป็นแหล่งข้อมูลได้

โปรดทราบว่าแผนภูมิไม่สามารถใช้สิทธิ์ของบุคคลที่ดูโดยไม่ได้รับอนุญาตอย่างชัดเจน ทุกคนต้องเห็นสเปรดชีต หรือหน้าเว็บต้องได้รับข้อมูลเข้าสู่ระบบของผู้ใช้ปลายทางอย่างชัดเจนตามที่ระบุไว้ในส่วนการให้สิทธิ์ของหน้านี้

หากต้องการใช้สเปรดชีตของ Google เป็นแหล่งข้อมูล คุณจะต้องมี URL ของสเปรดชีตดังนี้

  1. เปิดสเปรดชีตที่มีอยู่ สเปรดชีตนี้ควรมีรูปแบบตามที่การแสดงผลของคุณต้องการ และควรตั้งค่าสิทธิ์ในการดูไว้อย่างถูกต้อง (สิทธิ์การดูของ "สาธารณะบนเว็บ" หรือ "ทุกคนที่มีลิงก์" จะใช้วิธีที่ง่ายที่สุด และวิธีการในส่วนนี้จะถือว่ามีสเปรดชีตที่มีการตั้งค่าด้วยวิธีนี้ คุณจำกัดได้โดยคงสเปรดชีตให้ "ส่วนตัว" และให้สิทธิ์เข้าถึงบัญชี Google แต่ละบัญชี แต่ต้องปฏิบัติตามวิธีการให้สิทธิ์ด้านล่าง)
  2. คัดลอก URL จากเบราว์เซอร์ ดูช่วงแหล่งที่มาของคำค้นหาสำหรับรายละเอียดเกี่ยวกับการเลือกช่วงที่เจาะจง
  3. ระบุ URL ไปยัง google.visualization.Query() การค้นหารองรับพารามิเตอร์ที่ไม่บังคับต่อไปนี้
    • headers=N: ระบุจำนวนแถวที่เป็นแถวส่วนหัว โดยที่ N เป็นจำนวนเต็ม 0 ขึ้นไป รายการเหล่านี้จะไม่รวมอยู่ในข้อมูล และกำหนดเป็นป้ายกำกับคอลัมน์ในตารางข้อมูล หากคุณไม่ระบุพารามิเตอร์นี้ สเปรดชีตจะเดาจำนวนแถวที่เป็นแถวส่วนหัว โปรดทราบว่าหากคอลัมน์ทั้งหมดเป็นข้อมูลสตริง สเปรดชีตอาจพบปัญหาในการระบุว่าแถวใดเป็นแถวส่วนหัวที่ไม่มีพารามิเตอร์นี้
    • gid=N: ระบุชีตในเอกสารแบบหลายชีตที่ต้องการลิงก์ หากไม่ได้ลิงก์กับชีตแรก N คือหมายเลขรหัสของชีต ดูหมายเลขรหัสได้โดยไปที่ชีตเวอร์ชันที่เผยแพร่แล้วมองหาพารามิเตอร์ gid=N ใน URL คุณยังใช้พารามิเตอร์ sheet แทนพารามิเตอร์นี้ได้ด้วย Gotcha: Google สเปรดชีตอาจจัดเรียงพารามิเตอร์ Gid ใน URL ใหม่เมื่อดูในเบราว์เซอร์ หากคัดลอกจากเบราว์เซอร์ โปรดตรวจสอบว่าพารามิเตอร์ทั้งหมดอยู่ก่อนเครื่องหมาย # ของ URL ตัวอย่างเช่น gid=1545912003
    • sheet=sheet_name: ระบุชีตในเอกสารแบบหลายชีตที่คุณลิงก์ไว้ หากไม่ได้ลิงก์กับชีตแรก sheet_name คือชื่อที่แสดงของชีต ตัวอย่างเช่น sheet=Sheet5

ตัวอย่างที่สมบูรณ์มีดังต่อไปนี้

ด้านล่างนี้เป็น 2 วิธีในการวาดแผนภูมินี้ โดยวิธีแรกใช้พารามิเตอร์ gid และอีกวิธีคือใช้พารามิเตอร์ sheet การป้อน URL ใด URL หนึ่งในเบราว์เซอร์จะสร้างผลลัพธ์/ข้อมูลเดียวกันสำหรับแผนภูมิ

รหัส 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 });
    }

ช่วงแหล่งที่มาของคำค้นหา

URL แหล่งที่มาของการค้นหาจะระบุส่วนของสเปรดชีตที่จะใช้ในการค้นหา ซึ่งได้แก่ เซลล์ ช่วงของเซลล์ แถว หรือคอลัมน์ หรือทั้งสเปรดชีต ระบุช่วงโดยใช้ไวยากรณ์ "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 - 70 เซลล์แรกในคอลัมน์ A
  • A70:A - คอลัมน์ A จากแถว 70 ถึงจุดสิ้นสุด
  • B5:5 - B5 ถึงท้ายแถว 5
  • D3:D - D3 จนถึงจุดสิ้นสุดของคอลัมน์ D
  • C:C10 - จากจุดเริ่มต้นของคอลัมน์ C ถึง C10

การให้สิทธิ์

Google ชีตต้องใช้ข้อมูลเข้าสู่ระบบของผู้ใช้ปลายทางเพื่อเข้าถึงสเปรดชีตส่วนตัวผ่าน Google Visualization API ("คำขอ /tq")

หมายเหตุ: สเปรดชีตที่แชร์กับ "ทุกคนที่มีลิงก์สามารถดู" ไม่จำเป็นต้องใช้ข้อมูลเข้าสู่ระบบ การเปลี่ยนการตั้งค่าการแชร์ของสเปรดชีตนั้นทำได้ง่ายกว่าการใช้การให้สิทธิ์

ในกรณีที่ไม่สามารถแก้ปัญหาการแชร์ลิงก์ได้ นักพัฒนาซอฟต์แวร์จะต้องเปลี่ยนโค้ดเพื่อส่งผ่านข้อมูลเข้าสู่ระบบ OAuth 2.0 ที่ได้รับอนุญาตให้ใช้ในขอบเขต Google Sheets API (https://www.googleapis.com/auth/spreadsheets)

ดูพื้นหลังเพิ่มเติมเกี่ยวกับ OAuth 2.0 ได้ในการใช้ OAuth 2.0 เพื่อเข้าถึง Google APIs

ตัวอย่าง: การใช้ OAuth เพื่อเข้าถึง /gviz/tq

สิ่งที่ต้องทำก่อน: รับรหัสไคลเอ็นต์จาก Google Developer Console

ดูวิธีการโดยละเอียดเพิ่มเติมสำหรับการผสานรวมกับ Identity Platform ของ Google ได้ที่ Google Sign-In และการสร้างโปรเจ็กต์คอนโซล Google API และรหัสไคลเอ็นต์

ในการรับโทเค็น OAuth สำหรับผู้ใช้ปลายทาง ก่อนอื่นคุณต้องลงทะเบียนโปรเจ็กต์กับ Google Developer Console และรับรหัสไคลเอ็นต์

  1. สร้างรหัสไคลเอ็นต์ OAuth ใหม่จาก Developer Console
  2. เลือกประเภทแอปพลิเคชันเป็นเว็บแอปพลิเคชัน
  3. เลือกชื่อใดก็ได้ เพราะมีไว้สำหรับข้อมูลของคุณเท่านั้น
  4. เพิ่มชื่อโดเมน (และโดเมนทดสอบใดก็ได้) เป็นต้นทาง JavaScript ที่ได้รับอนุญาต
  5. เว้น URI การเปลี่ยนเส้นทางที่ได้รับอนุญาตว่างไว้

หลังจากคลิกสร้างแล้ว ให้คัดลอกรหัสไคลเอ็นต์ไว้ใช้อ้างอิงในอนาคต ไม่จำเป็นต้องใช้รหัสลับไคลเอ็นต์ สำหรับแบบฝึกหัดนี้

อัปเดตเว็บไซต์เพื่อรับข้อมูลเข้าสู่ระบบ 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() จะส่งคืนรายละเอียดข้อมูลเข้าสู่ระบบทั้งหมด รวมถึง access_token ที่นำไปต่อท้ายคำขอ /gviz/tq ได้

ดูข้อมูลเพิ่มเติมเกี่ยวกับการใช้ไลบรารี gapi สำหรับการตรวจสอบสิทธิ์ได้ที่

การใช้ขอบเขต drive.file

ตัวอย่างก่อนหน้านี้ใช้ขอบเขต Google Sheets API ซึ่งให้สิทธิ์อ่านและเขียนเนื้อหาสเปรดชีตทั้งหมดของผู้ใช้ การดำเนินการนี้อาจอนุญาตมากกว่าที่จำเป็น ทั้งนี้ขึ้นอยู่กับแอปพลิเคชัน สำหรับสิทธิ์การเข้าถึงระดับอ่านอย่างเดียว ให้ใช้ขอบเขต spreadsheets.readonly ซึ่งจะให้สิทธิ์การเข้าถึงระดับอ่านอย่างเดียวสำหรับชีตและพร็อพเพอร์ตี้ของผู้ใช้

ขอบเขต drive.file (https://www.googleapis.com/auth/drive.file) จะให้สิทธิ์เข้าถึงเฉพาะไฟล์ที่ผู้ใช้เปิดด้วยตนเองด้วยเครื่องมือเลือกไฟล์ของ Google ไดรฟ์ ซึ่งเปิดใช้งานผ่าน Picker API

การใช้เครื่องมือเลือกจะเปลี่ยนการทำงานของแอปพลิเคชัน แทนที่จะวางใน URL หรือมีสเปรดชีตแบบฮาร์ดโค้ดดังตัวอย่างข้างต้น ผู้ใช้ต้องใช้กล่องโต้ตอบเครื่องมือเลือกเพื่อเลือกสเปรดชีตที่ต้องการให้หน้าเว็บของคุณเข้าถึง ทำตามตัวอย่างเครื่องมือเลือก "สวัสดีโลก" โดยใช้ google.picker.ViewId.SPREADSHEETS แทน google.picker.ViewId.PHOTOS