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

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

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

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

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

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

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

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

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

หากต้องการเข้าถึงข้อมูล Looker คุณต้องใส่เมธอด enableLookerExecution() ในโค้ด Google Apps Script การเข้าถึง Looker ใน Apps Script จะใช้ลิงก์บัญชี Google ที่มีอยู่อีกครั้งกับ Looker

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

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

เพิ่มขอบเขต OAuth เพิ่มเติมลงในไฟล์ Manifest

เมื่อเชื่อมต่อกับ BigQuery ระบบจะเพิ่มขอบเขต 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 และ Looker ตามลำดับ

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();

Looker

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

แทนที่ <INSTANCE_URL>,<MODEL_NAME>, <EXPLORE_NAME> ในตัวอย่างต่อไปนี้ด้วย URL อินสแตนซ์ Looker ที่ถูกต้อง, ชื่อโมเดล และชื่อการสำรวจตามลำดับ

// For operations that fetch data from Looker, enableLookerExecution() must be called.
SpreadsheetApp.enableLookerExecution();
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()
    .asLooker()
    .setInstanceUrl('<INSTANCE_URL>')
    .setModelName('<MODEL_NAME>')
    .setExploreName('<EXPLORE_NAME>')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

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

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

โดยทั่วไปแล้ว ข้อมูลจากแหล่งข้อมูลจะอ้างอิงตามชื่อคอลัมน์ ซึ่งแตกต่างจากข้อมูลปกติในชีตตารางกริดที่อ้างอิงตามดัชนีเซลล์หรือการเขียน 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());
}

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

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

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

แทนที่ <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 เพิ่มเติมเพื่อให้ใช้งานได้
ไม่ได้รับอนุญาตให้ดำเนินการกับแหล่งข้อมูล โปรดติดต่อผู้ดูแลระบบเพื่อเปิดใช้ฟีเจอร์นี้	ข้อผิดพลาดนี้หมายความว่าบัญชีไม่ได้เปิดใช้ชีตที่เชื่อมต่อ ชีตที่เชื่อมต่อมีให้บริการแก่ Google Workspace ผู้ใช้ที่มีการสมัครใช้บริการบางอย่างเท่านั้น โปรดติดต่อผู้ดูแลระบบเพื่อเปิดใช้ฟีเจอร์นี้