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

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

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

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

การดำเนินการ	คลาส 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() ในโค้ด Apps Script เมธอดนี้จะเพิ่มขอบเขต OAuth bigquery.readonly ที่จำเป็นลงในโปรเจ็กต์ Apps Script

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

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

หากต้องการเข้าถึงข้อมูล Looker ให้ใส่เมธอด enableLookerExecution() ในโค้ด 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, ชื่อโมเดล และชื่อ Explore ที่ถูกต้อง ตามลำดับ

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