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 và Looker ngay trong Trang tính. 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ụ Trang tính.

Các thao tác thường dùng 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 hoặc Looker và phân tích dữ liệu. Bảng sau đâ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 Google Apps Script:

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

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

Để truy cập vào dữ liệu BigQuery, hãy thêm phương thức enableBigQueryExecution() vào mã Apps Script. Phương thức này sẽ thêm phạm vi OAuth bigquery.readonly bắt buộc vào dự án 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();
  }

Để truy cập vào dữ liệu Looker, hãy thêm phương thức enableLookerExecution() vào mã Apps Script. Việc truy cập vào Looker trong Apps Script sẽ sử dụng lại Liên kết tài khoản Google hiện có với Looker.

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

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

Thêm các phạm vi OAuth khác vào tệp kê khai

Khi kết nối với BigQuery, hầu hết các phạm vi OAuth sẽ tự động được thêm vào tệp kê khai dựa trên các hàm được sử 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, 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. Mẫu này thêm phạm vi OAuth của Drive ngoài các phạm vi OAuth spreadsheet và bigquery.readonly tối thiểu bắt buộc:

{ ...
  "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 đối tượng nguồn dữ liệu

Ví dụ sau đây cho thấy cách thêm nguồn dữ liệu, 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à nhận trạng thái thực thi.

Thêm nguồn dữ liệu

Các ví dụ sau đây cho thấy cách thêm nguồn dữ liệu BigQuery và Looker tương ứng.

BigQuery

Để thêm nguồn dữ liệu BigQuery vào trang tính, hãy chèn trang tính nguồn dữ liệu bằng một đặc tả nguồn dữ liệu. Trang tính nguồn dữ liệu sẽ tự động được 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 đám mây của Google.

// 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

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

Thay thế <INSTANCE_URL>,<MODEL_NAME>, <EXPLORE_NAME> trong mẫu sau đây bằng URL thực thể Looker, tên mô hình và tên khám phá hợp lệ tương ứng.

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

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

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

Không giống như dữ liệu thông thường trong trang tính dạng lưới được tham chiếu theo chỉ mục ô hoặc ký hiệu A1, dữ liệu từ nguồn dữ liệu thường được tham chiếu theo tên cột. Do đó, hầu hết các trình thiết lập thuộc tính trên đối tượng nguồn dữ liệu đều sử dụng tên cột làm dữ liệu đầ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

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 đặc tả nguồn dữ liệu và cấu hình đối tượng.

Quá trình làm mới dữ liệu là 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:

refreshData() bắt đầu thực thi làm mới dữ liệu.
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. Nhờ đó, bạn không cần phải liên tục thăm dò trạng thái thực thi.
DataExecutionStatus.getErrorCode() nhận mã lỗi trong trường hợp quá trình thực thi dữ liệu không thành công.

Mẫu sau đây minh hoạ quá trình làm mới dữ liệu 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 trình 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 trình kích hoạt và sự kiện. Ví dụ: sử dụng trình kích hoạt theo 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 trình kích hoạt sự kiện trang 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 sau đây thêm nguồn dữ liệu BigQuery bằng một 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ằng mã dự án trên đám mây 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ước đó, hàm addDataSource() sẽ thêm nguồn dữ liệu vào trang tính. Sau khi thực thi addDataSource(), hãy tạo trình 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 Trình kích hoạt có thể cài đặt.

Chọn các lựa chọn sau cho điều kiện kích hoạt:

Nguồn sự kiện: Từ trang 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 đối với 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.
Chẳng hạn như 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 đối với 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 rằng tài khoản không bật Trang tính liên kết.
Trang tính liên kết chỉ dành cho người dùng Google Workspace có một số gói thuê bao.
Liên hệ với quản trị viên của bạn để bật tính năng này.

Thông báo lỗi	Độ phân giải
Sử dụng `enableBigQuery()` để cho phép thực thi dữ liệu đối với 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. Chẳng hạn như `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 đối với 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 rằng tài khoản không bật Trang tính liên kết. Trang tính liên kết chỉ dành cho người dùng Google Workspace có một số gói thuê bao. Liên hệ với quản trị viên của bạn để bật tính năng này.

Sử dụng Trang tính liên kết Sử dụng bộ sưu tập để sắp xếp ngăn nắp các trang Lưu và phân loại nội dung dựa trên lựa chọn ưu tiên của bạn.