Trang tính liên kết là một tính năng của Google Trang tính giúp bạn phân tích dữ liệu BigQuery và Looker ngay trong Trang tính. Bạn có thể truy cập vào Các 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 thường gặp trên 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 phổ biến nhất với DataSource
và
cách tạo chúng trong Apps Script:
Hành động | Lớp Google Apps Script | Phương thức sử dụng |
---|---|---|
Kết nối trang tính với một 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() |
Lấy dữ liệu vào 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 phạm vi uỷ quyền bắt buộc
Để truy cập vào dữ liệu BigQuery, bạn phải sử dụng phương thức enableBigQueryExecution()
trong mã Google Apps Script. Phương thức này sẽ thêm bigquery.readonly
bắt buộc
Phạm vi OAuth cho dự án Google Apps Script.
Mẫu sau đây cho thấy phương thức SpreadsheetApp.enableBigQueryExecution()
được gọi trong hàm:
function addDataSource() { SpreadsheetApp.enableBigQueryExecution(); var spreadsheet = SpreadsheetApp.getActive(); }
Để truy cập vào dữ liệu trên Looker, bạn phải thêm phương thức enableLookerExecution()
vào
mã Google Apps Script. Truy cập vào Looker trong Apps Script
sẽ sử dụng lại đường liên kết Tài khoản Google hiện có của bạn với Looker.
Mẫu sau đây cho thấy phương thức SpreadsheetApp.enableLookerExecution()
được gọi trong 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 đề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 bạn cần thêm để truy cập 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 minh hoạ phần oauthScopes
của tệp kê khai. Chiến dịch này thêm
phạm vi OAuth trên Drive ngoài spreadsheet
bắt buộc tối thiểu và
bigquery.readonly
phạm vi OAuth:
{ ... "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
Các ví dụ sau đây minh hoạ cách thêm nguồn dữ liệu, tạo dữ liệu đối tượng nguồn từ nguồn dữ liệu, làm mới đối tượng nguồn dữ liệu và tải trạng thái thực thi.
Thêm nguồn dữ liệu
Các ví dụ sau đây minh hoạ cách thêm BigQuery và nguồn dữ liệu Looker tương ứng.
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 xem trước dữ liệu.
Thay thế <YOUR_PROJECT_ID>
bên dưới bằng một 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();
Looker
Để thêm nguồn dữ liệu Looker 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 xem trước dữ liệu.
Thay thế <INSTANCE_URL>
,<MODEL_NAME>
, <EXPLORE_NAME>
trong đoạn mã sau
mẫu có URL hợp lệ của thực thể Looker, tên mô hình và tên khám phá
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 bảng tính, các đối tượng nguồn dữ liệu có thể được
được tạo từ nguồn dữ liệu. Trong ví dụ này, bảng tổng hợp được tạo bằng cách sử dụng
DataSourcePivotTable
trên BigQuery dataSource
được tạo trong
mã mẫu dùng để thêm nguồn dữ liệu BigQuery.
Không giống như dữ liệu thông thường trong trang tính lưới được tham chiếu theo chỉ mục ô hoặc A1 ký hiệu, 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 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 một đối tượng nguồn dữ liệu, sử dụng các phương thức sau:
refreshData()
bắt đầu quá trình thực thi làm mới dữ liệu.waitForCompletion()
trả về trạng thái kết thúc sau khi thực thi dữ liệu đã hoàn tất. Việc này giúp bạn không cần tiếp tục thăm dò trạng thái thực thi.DataExecutionStatus.getErrorCode()
sẽ nhận mã lỗi trong trường hợp dữ liệu thực thi không thành công.
Mẫu dưới đây minh hoạ quá 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 chức năng nguồn dữ liệu trong 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 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 điều kiện kích hoạt sự kiện trong 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 sau đây thêm một nguồn dữ liệu BigQuery với 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ên dưới bằng một 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 bạn thực thi addDataSource()
, hãy tạo một 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 một đ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 lựa 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 để chạy:
refreshOnParameterEdit
Sau khi bạn tạo điều kiện kích hoạt, trang tính nguồn dữ liệu sẽ tự động làm mới mỗi khi ô thông 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() . Những 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 tài khoản chưa bật tính năng Trang tính liên kết. Trang tính liên kết chỉ dành 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. |