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

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