ใช้ชีตที่เชื่อมต่อ

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

การทำงานทั่วไปของชีตที่เชื่อมต่อ

ใช้คลาสและออบเจ็กต์ DataSource เพื่อเชื่อมต่อกับ BigQuery และวิเคราะห์ข้อมูล ตารางด้านล่างแสดงการดำเนินการ DataSource ที่พบบ่อยที่สุดและวิธีสร้างใน Apps Script

การดำเนินการ	คลาสของ Google Apps Script	วิธีใช้
เชื่อมต่อชีตกับ BigQuery	DataSourceSpec	SpreadsheetApp.newDataSourceSpec()
เลือกแหล่งข้อมูล BigQuery	DataSource	Spreadsheet.insertDataSourceSheet().getDataSource()
เพิ่มชีตแหล่งข้อมูล BigQuery	DataSourceSheet	Spreadsheet.insertDataSourceSheet()
เพิ่มตาราง Pivot	DataSourcePivotTable	Range.insertDataSourcePivotTable()
ดึงข้อมูลเข้ามาในการแตกข้อมูล	DataSourceTable	Range.insertDataSourceTable()
ใช้สูตร	DataSourceFormula	Range.setFormula()
เพิ่มแผนภูมิ	DataSourceChart	Sheet.insertDataSourceChart()

เพิ่มขอบเขตการให้สิทธิ์ที่จำเป็น

หากต้องการเข้าถึงข้อมูล BigQuery คุณต้องใส่เมธอด enableBigQueryExecution() ไว้ในโค้ดของ Google Apps Script วิธีนี้จะเพิ่มขอบเขต OAuth bigquery.readonly ที่จําเป็นไปยังโปรเจ็กต์ Google Apps Script

ตัวอย่างต่อไปนี้แสดงเมธอด SpreadsheetApp.enableBigQueryExecution() ที่เรียกใช้ภายในฟังก์ชัน

function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();
  }

เพิ่มขอบเขต OAuth เพิ่มเติมไปยังไฟล์ Manifest

ระบบจะเพิ่มขอบเขต OAuth ส่วนใหญ่ลงในไฟล์ Manifest โดยอัตโนมัติโดยอิงตามฟังก์ชันที่ใช้ในโค้ด หากต้องการขอบเขตเพิ่มเติมเพื่อเข้าถึงข้อมูล BigQuery บางอย่าง คุณก็ตั้งค่าขอบเขตที่ชัดเจนได้

ตัวอย่างเช่น หากต้องการค้นหาข้อมูล BigQuery ที่โฮสต์ภายใน Google ไดรฟ์ คุณต้องเพิ่มขอบเขต OAuth ของไดรฟ์ลงในไฟล์ Manifest

ตัวอย่างต่อไปนี้แสดงส่วน oauthScopes ของไฟล์ Manifest โดยจะเพิ่มขอบเขต OAuth ของไดรฟ์เพิ่มเติมจากขอบเขต OAuth spreadsheet และ bigquery.readonly ขั้นต่ำต่อไปนี้

{ ...
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery.readonly",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive" ],
... }

ตัวอย่าง: สร้างและรีเฟรชออบเจ็กต์แหล่งข้อมูล

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

เพิ่มแหล่งข้อมูล BigQuery

หากต้องการเพิ่มแหล่งข้อมูล BigQuery ในสเปรดชีต ให้แทรกชีตแหล่งข้อมูลที่มีข้อกำหนดของแหล่งข้อมูล ระบบจะรีเฟรชแผ่นงานแหล่งข้อมูลโดยอัตโนมัติเพื่อดึงข้อมูลตัวอย่าง

แทนที่ <YOUR_PROJECT_ID> ด้านล่างด้วยรหัสโปรเจ็กต์ Google Cloud ที่ถูกต้อง

// For operations that fetch data from BigQuery, enableBigQueryExecution() must be called.
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());

// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
    .asBigQuery()
    .setProjectId('<YOUR_PROJECT_ID>')
    .setTableProjectId('bigquery-public-data')
    .setDatasetId('ncaa_basketball')
    .setTableId('mbb_historical_tournament_games')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

เพิ่มออบเจ็กต์แหล่งข้อมูล

เมื่อเพิ่มแหล่งข้อมูลลงในสเปรดชีตแล้ว คุณจะสร้างออบเจ็กต์แหล่งข้อมูลได้จากแหล่งข้อมูลดังกล่าว ในตัวอย่างนี้ ตาราง Pivot สร้างขึ้นโดยใช้ DataSourcePivotTable

ข้อมูลจากแหล่งข้อมูลมักจะอ้างอิงตามชื่อคอลัมน์ ซึ่งแตกต่างจากข้อมูลปกติในชีตตารางกริดที่ดัชนีเซลล์หรือหมายเหตุ A1 อ้างอิง ดังนั้นตัวตั้งค่าพร็อพเพอร์ตี้ส่วนใหญ่ในออบเจ็กต์แหล่งข้อมูลจะใช้ชื่อคอลัมน์เป็นอินพุต

var rootCell = spreadsheet.insertSheet('pivotTableSheet').getRange('A1');

// Add data source pivot table and set data source specific configurations.
var dataSourcePivotTable = rootCell.createDataSourcePivotTable(dataSource);
var rowGroup = dataSourcePivotTable.addRowGroup('season');
rowGroup.sortDescending().setGroupLimit(5);
dataSourcePivotTable.addColumnGroup('win_school_ncaa');
dataSourcePivotTable.addPivotValue('win_pts', SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
dataSourcePivotTable.addPivotValue('game_date', SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
var filterCriteria = SpreadsheetApp.newFilterCriteria()
    .whenTextEqualToAny(['Duke', 'North Carolina'])
    .build();
dataSourcePivotTable.addFilter('win_school_ncaa', filterCriteria);

// Get a regular pivot table instance and set shared configurations.
var pivotTable = dataSourcePivotTable.asPivotTable();
pivotTable.setValuesDisplayOrientation(SpreadsheetApp.Dimension.ROWS);

รีเฟรชออบเจ็กต์แหล่งข้อมูล

คุณรีเฟรชออบเจ็กต์แหล่งข้อมูลเพื่อดึงข้อมูลล่าสุดจาก BigQuery ได้ตามข้อกำหนดของแหล่งข้อมูลและการกำหนดค่าออบเจ็กต์

กระบวนการรีเฟรชข้อมูลเป็นกระบวนการที่ไม่พร้อมกัน หากต้องการรีเฟรชออบเจ็กต์แหล่งข้อมูล ให้ใช้วิธีการต่อไปนี้

1. refreshData() เริ่มการดำเนินการรีเฟรชข้อมูล
2. waitForCompletion() จะแสดงสถานะสิ้นสุดเมื่อการดำเนินการกับข้อมูลเสร็จสมบูรณ์แล้ว ทำให้ไม่ต้องทำการสำรวจสถานะการดำเนินการ
3. DataExecutionStatus.getErrorCode() จะได้รับรหัสข้อผิดพลาดในกรณีที่การดำเนินการกับข้อมูลไม่สำเร็จ

ตัวอย่างด้านล่างแสดงการรีเฟรชข้อมูลตาราง Pivot

var status = dataSourcePivotTable.getStatus();
Logger.log('Initial state: %s', status.getExecutionState());

dataSourcePivotTable.refreshData();

status = dataSourcePivotTable.waitForCompletion(/* timeoutInSeconds= */ 60);
Logger.log('Ending state: %s', status.getExecutionState());
if (status.getExecutionState() == SpreadsheetApp.DataExecutionState.ERROR) {
  Logger.log('Error: %s (%s)', status.getErrorCode(), status.getErrorMessage());
}

ใช้ทริกเกอร์กับชีตที่เชื่อมต่อ

ทำให้ฟังก์ชันแหล่งข้อมูลของชีตที่เชื่อมต่อทำงานอัตโนมัติด้วยทริกเกอร์และเหตุการณ์ ตัวอย่างเช่น ใช้ทริกเกอร์ที่ขึ้นกับเวลาเพื่อรีเฟรชออบเจ็กต์แหล่งข้อมูลซ้ำๆ ในเวลาที่เฉพาะเจาะจง และใช้ทริกเกอร์เหตุการณ์สเปรดชีตเพื่อทริกเกอร์การดำเนินการกับข้อมูลในเหตุการณ์ที่กำหนดไว้ล่วงหน้า

ตัวอย่างด้านล่างเพิ่มแหล่งข้อมูลที่มีพารามิเตอร์การค้นหา และรีเฟรชชีตแหล่งข้อมูลเมื่อมีการแก้ไขพารามิเตอร์การค้นหา

แทนที่ <YOUR_PROJECT_ID> ด้านล่างด้วยรหัสโปรเจ็กต์ Google Cloud ที่ถูกต้อง

// Add data source with query parameter.
function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();

  // Add a new sheet and use A1 cell as the parameter cell.
  var parameterCell = spreadsheet.insertSheet('parameterSheet').getRange('A1');
parameterCell.setValue('Duke');

  // Add data source with query parameter.
  var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
      .asBigQuery()
      .setProjectId('<YOUR_PROJECT_ID>')
      .setRawQuery('select * from `bigquery-public-data`.`ncaa_basketball`.`mbb_historical_tournament_games` WHERE win_school_ncaa = @SCHOOL')
      .setParameterFromCell('SCHOOL', 'parameterSheet!A1')
      .build();
  var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
  dataSourceSheet.asSheet().setName('ncaa_data');
}

// Function used to configure event trigger to refresh data source sheet.
function refreshOnParameterEdit(e) {
  var editedRange = e.range;
if (editedRange.getSheet().getName() != 'parameterSheet') {
  return;
}
// Check that the edited range includes A1.
if (editedRange.getRow() > 1 || editedRange.getColumn() > 1) {
   return;
}

  var spreadsheet = e.source;
  SpreadsheetApp.enableBigQueryExecution();
  spreadsheet.getSheetByName('ncaa_data').asDataSourceSheet().refreshData();
}

ในตัวอย่างข้างต้น ฟังก์ชัน addDataSource() จะเพิ่มแหล่งข้อมูลลงในสเปรดชีต หลังจากเรียกใช้ addDataSource() ให้สร้างทริกเกอร์เหตุการณ์ในเครื่องมือแก้ไข Apps Script หากต้องการดูวิธีสร้างทริกเกอร์เหตุการณ์ โปรดดูทริกเกอร์ที่ติดตั้งได้

เลือกตัวเลือกต่อไปนี้สำหรับทริกเกอร์

• แหล่งที่มาของเหตุการณ์: จากสเปรดชีต
• ประเภทกิจกรรม: เมื่อแก้ไข
• ฟังก์ชันที่จะเรียกใช้: refreshOnParameterEdit

เมื่อสร้างทริกเกอร์แล้ว ชีตแหล่งข้อมูลจะรีเฟรชโดยอัตโนมัติทุกครั้งที่มีการแก้ไขเซลล์พารามิเตอร์

แก้ปัญหา

ข้อความแสดงข้อผิดพลาด	ความละเอียด
ใช้ enableBigQuery() เพื่อเปิดใช้การดำเนินการกับข้อมูลสำหรับแหล่งข้อมูล BIGQUERY	ข้อผิดพลาดนี้บ่งบอกว่าไม่มีการเรียกใช้ SpreadsheetApp.enableBigQueryExecution() ก่อนดึงข้อมูล BigQuery เรียก SpreadsheetApp.enableBigQueryExecution() ในฟังก์ชันที่ใช้เมธอดสําหรับการดําเนินการ BigQuery เช่น refreshData() ในออบเจ็กต์แหล่งข้อมูล, Spreadsheet.insertDataSourceTable() และ DataSource.updateSpec() วิธีการเหล่านี้ต้องใช้ขอบเขต OAuth BigQuery.readonly เพิ่มเติมจึงจะใช้งานได้
ไม่ได้รับอนุญาตให้ดำเนินการกับแหล่งข้อมูล โปรดติดต่อผู้ดูแลระบบเพื่อเปิดใช้ฟีเจอร์นี้	ข้อผิดพลาดนี้บ่งบอกว่าบัญชีไม่ได้เปิดใช้ชีตที่เชื่อมต่อ ชีตที่เชื่อมต่อพร้อมให้บริการแก่ผู้ใช้ Google Workspace ที่สมัครใช้บริการบางรายการเท่านั้น โปรดติดต่อผู้ดูแลระบบเพื่อเปิดใช้ฟีเจอร์นี้