Sử dụng Trang tính liên kết

Trang tính liên kết là một tính năng của Google Trang tính cho phép bạn phân tích dữ liệu BigQuery ngay trong Trang tính. Bạn có thể truy cập vào Trang tính liên kết theo phương thức lập trình bằng dịch vụ Bảng tính.

Các thao tác phổ biến trong Trang tính liên kết

Sử dụng các lớp và đối tượng DataSource để kết nối với BigQuery và phân tích dữ liệu. Bảng dưới đây liệt kê các thao tác DataSource phổ biến nhất và cách tạo các thao tác đó trong Apps Script:

Hành động	Lớp Google Apps Script	Phương pháp sử dụng
Kết nối trang tính với BigQuery	DataSourceSpec	SpreadsheetApp.newDataSourceSpec()
Chọn một nguồn dữ liệu BigQuery	DataSource	Spreadsheet.insertDataSourceSheet().getDataSource()
Thêm trang tính nguồn dữ liệu BigQuery	DataSourceSheet	Spreadsheet.insertDataSourceSheet()
Thêm bảng tổng hợp	DataSourcePivotTable	Range.insertDataSourcePivotTable()
Kéo dữ liệu vào bản trích	DataSourceTable	Range.insertDataSourceTable()
Sử dụng một công thức	DataSourceFormula	Range.setFormula()
Thêm biểu đồ	DataSourceChart	Sheet.insertDataSourceChart()

Thêm phạm vi uỷ quyền bắt buộc

Để truy cập dữ liệu BigQuery, bạn phải đưa phương thức enableBigQueryExecution() vào mã Google Apps Script. Phương thức này thêm phạm vi bigquery.readonly OAuth bắt buộc vào dự án Google Apps Script.

Mẫu sau đây cho thấy phương thức SpreadsheetApp.enableBigQueryExecution() được gọi trong một hàm:

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

Thêm phạm vi OAuth bổ sung vào tệp kê khai

Hầu hết các phạm vi OAuth đều tự động được thêm vào tệp kê khai dựa trên các hàm dùng trong mã của bạn. Nếu cần thêm phạm vi để truy cập vào một số dữ liệu BigQuery nhất định, bạn có thể đặt phạm vi rõ ràng.

Ví dụ: để truy vấn dữ liệu BigQuery được lưu trữ trong Google Drive, bạn phải thêm phạm vi OAuth của Drive vào tệp kê khai.

Mẫu sau đây cho thấy phần oauthScopes của tệp kê khai. Thao tác này còn thêm phạm vi OAuth ổ đĩa ngoài các phạm vi OAuth spreadsheet và bigquery.readonly tối thiểu cần thiết:

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

Ví dụ: Tạo và làm mới một đối tượng nguồn dữ liệu

Ví dụ sau đây cho biết cách thêm nguồn dữ liệu BigQuery, tạo đối tượng nguồn dữ liệu từ nguồn dữ liệu, làm mới đối tượng nguồn dữ liệu và xem trạng thái thực thi. Trong ví dụ này, các đoạn mã được thực thi theo trình tự.

Thêm nguồn dữ liệu BigQuery

Để thêm nguồn dữ liệu BigQuery vào bảng tính, hãy chèn trang tính nguồn dữ liệu có thông số nguồn dữ liệu. Trang tính nguồn dữ liệu được tự động làm mới để tìm nạp dữ liệu xem trước.

Thay thế <YOUR_PROJECT_ID> bên dưới bằng mã dự án hợp lệ trên 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();

Thêm đối tượng nguồn dữ liệu

Sau khi thêm nguồn dữ liệu vào bảng tính, các đối tượng nguồn dữ liệu có thể được tạo từ nguồn dữ liệu. Trong ví dụ này, một bảng tổng hợp được tạo bằng cách sử dụng DataSourcePivotTable.

Không giống như dữ liệu thông thường trong trang tính lưới được tham chiếu bằng chỉ mục ô hoặc ký hiệu A1, dữ liệu từ các nguồn dữ liệu thường được tham chiếu theo tên cột. Do đó, hầu hết các phương thức setter thuộc tính trên các đối tượng nguồn dữ liệu đều sử dụng tên cột làm đầu vào.

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

Làm mới đối tượng nguồn dữ liệu

Bạn có thể làm mới các đối tượng nguồn dữ liệu để tìm nạp dữ liệu mới nhất từ BigQuery dựa trên thông số kỹ thuật của nguồn dữ liệu và cấu hình đối tượng.

Quá trình làm mới dữ liệu không đồng bộ. Để làm mới đối tượng nguồn dữ liệu, hãy sử dụng các phương thức sau:

1. refreshData() bắt đầu quá trình làm mới dữ liệu.
2. waitForCompletion() trả về trạng thái kết thúc sau khi quá trình thực thi dữ liệu hoàn tất. Điều này giúp bạn không cần phải tiếp tục thăm dò trạng thái thực thi.
3. DataExecutionStatus.getErrorCode() sẽ nhận mã lỗi trong trường hợp không thực thi được dữ liệu.

Mẫu dưới đây minh hoạ quy trình làm mới dữ liệu trong bảng tổng hợp:

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

Sử dụng điều kiện kích hoạt với Trang tính liên kết

Tự động hoá các hàm nguồn dữ liệu của Trang tính liên kết bằng điều kiện kích hoạt và sự kiện. Ví dụ: sử dụng điều kiện kích hoạt dựa trên thời gian để làm mới các đối tượng nguồn dữ liệu nhiều lần vào một thời điểm cụ thể và sử dụng điều kiện kích hoạt sự kiện của bảng tính để kích hoạt quá trình thực thi dữ liệu trên một sự kiện được xác định trước.

Mẫu dưới đây sẽ thêm một nguồn dữ liệu có tham số truy vấn và làm mới trang tính nguồn dữ liệu khi tham số truy vấn được chỉnh sửa.

Thay thế <YOUR_PROJECT_ID> bên dưới bằng mã dự án hợp lệ trên 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();
}

Trong mẫu trên, hàm addDataSource() thêm một nguồn dữ liệu vào bảng tính. Sau khi thực thi addDataSource(), hãy tạo một điều kiện kích hoạt sự kiện trong trình chỉnh sửa Apps Script. Để tìm hiểu cách tạo điều kiện kích hoạt sự kiện, hãy xem bài viết Điều kiện kích hoạt có thể cài đặt.

Chọn các tuỳ chọn sau cho điều kiện kích hoạt của bạn:

• Nguồn sự kiện: Từ bảng tính
• Loại sự kiện: Khi chỉnh sửa
• Hàm cần chạy: refreshOnParameterEdit

Sau khi trình kích hoạt được tạo, trang tính nguồn dữ liệu sẽ tự động làm mới mỗi khi ô tham số được chỉnh sửa.

Khắc phục sự cố

Thông báo lỗi	Độ phân giải
Sử dụng enableBigQuery() để cho phép thực thi dữ liệu cho nguồn dữ liệu BIGQUERY.	Lỗi này cho biết rằng SpreadsheetApp.enableBigQueryExecution() không được gọi trước khi tìm nạp dữ liệu BigQuery. Gọi SpreadsheetApp.enableBigQueryExecution() trong các hàm sử dụng phương thức để thực thi BigQuery. Ví dụ: refreshData() trên các đối tượng nguồn dữ liệu, Spreadsheet.insertDataSourceTable() và DataSource.updateSpec(). Các phương thức này yêu cầu thêm phạm vi OAuth bigquery.readonly để hoạt động.
Không được phép thao tác trên nguồn dữ liệu. Vui lòng liên hệ với quản trị viên của bạn để bật tính năng này.	Lỗi này cho biết tài khoản chưa bật Trang tính liên kết. Trang tính liên kết chỉ được cung cấp cho Google Workspace người dùng có một số gói thuê bao nhất định. Hãy liên hệ với quản trị viên để bật tính năng này.