Halaman ini diterjemahkan oleh Cloud Translation API.

Menggunakan Sheet yang Terhubung

Sheet yang Terhubung adalah fitur Google Spreadsheet yang memungkinkan Anda menganalisis data BigQuery langsung dalam Spreadsheet. Anda dapat mengakses Sheet yang Terhubung secara terprogram dengan layanan Spreadsheet.

Tindakan umum Sheet yang Terhubung

Gunakan class dan objek DataSource untuk terhubung ke BigQuery dan menganalisis data. Tabel di bawah mencantumkan tindakan DataSource yang paling umum dan cara membuatnya di Apps Script:

Tindakan	Kelas Google Apps Script	Metode yang digunakan
Menghubungkan sheet ke BigQuery	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Memilih sumber data BigQuery	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Menambahkan sheet sumber data BigQuery	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Menambahkan tabel pivot	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Mengambil data ke ekstrak	`DataSourceTable`	`Range.insertDataSourceTable()`
Menggunakan formula	`DataSourceFormula`	`Range.setFormula()`
Tambahkan diagram	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Tambahkan cakupan otorisasi yang diperlukan

Untuk mengakses data BigQuery, Anda harus menyertakan metode enableBigQueryExecution() dalam kode Google Apps Script. Metode ini menambahkan cakupan OAuth bigquery.readonly yang diperlukan ke project Google Apps Script Anda.

Contoh berikut menunjukkan metode SpreadsheetApp.enableBigQueryExecution() yang dipanggil dalam suatu fungsi:

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

Tambahkan cakupan OAuth tambahan ke file manifes

Sebagian besar cakupan OAuth otomatis ditambahkan ke file manifes berdasarkan fungsi yang digunakan dalam kode Anda. Jika memerlukan cakupan tambahan untuk mengakses data BigQuery tertentu, Anda dapat menetapkan cakupan eksplisit.

Misalnya, untuk melakukan kueri data BigQuery yang dihosting dalam Google Drive, Anda harus menambahkan cakupan OAuth Drive ke file manifes.

Contoh berikut menunjukkan bagian oauthScopes dari file manifes. Versi ini menambahkan cakupan OAuth drive selain cakupan OAuth spreadsheet dan bigquery.readonly minimum yang diperlukan:

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

Contoh: Membuat dan memuat ulang objek sumber data

Contoh berikut menunjukkan cara menambahkan sumber data BigQuery, membuat objek sumber data dari sumber data, memuat ulang objek sumber data, dan mendapatkan status eksekusi. Dalam contoh ini, cuplikan kode dieksekusi secara berurutan.

Menambahkan sumber data BigQuery

Untuk menambahkan sumber data BigQuery ke spreadsheet, sisipkan sheet sumber data dengan spesifikasi sumber data. Sheet sumber data akan otomatis dimuat ulang untuk mengambil data pratinjau.

Ganti <YOUR_PROJECT_ID> di bawah dengan ID project Google Cloud yang valid.

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

Menambahkan objek sumber data

Setelah sumber data ditambahkan ke spreadsheet, objek sumber data dapat dibuat dari sumber data tersebut. Dalam contoh ini, tabel pivot dibuat menggunakan DataSourcePivotTable.

Tidak seperti data reguler dalam sheet petak yang direferensikan oleh indeks sel atau notasi A1, data dari sumber data biasanya direferensikan oleh nama kolom. Oleh karena itu, sebagian besar penyetel properti pada objek sumber data menggunakan nama kolom sebagai input.

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

Memuat ulang objek sumber data

Anda dapat memuat ulang objek sumber data untuk mengambil data terbaru dari BigQuery berdasarkan spesifikasi sumber data dan konfigurasi objek.

Proses memuat ulang data bersifat asinkron. Untuk memuat ulang objek sumber data, gunakan metode berikut:

refreshData() memulai eksekusi refresh data.
waitForCompletion() menampilkan status akhir setelah eksekusi data selesai. Dengan demikian, Anda tidak perlu terus melakukan polling status eksekusi.
DataExecutionStatus.getErrorCode() mendapatkan kode error jika eksekusi data gagal.

Contoh di bawah ini mengilustrasikan pemuatan ulang data tabel 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());
}

Menggunakan pemicu dengan Sheet yang Terhubung

Otomatiskan fungsi sumber data Sheet yang Terhubung dengan pemicu dan peristiwa. Misalnya, gunakan pemicu berdasarkan waktu untuk me-refresh objek sumber data berulang kali pada waktu tertentu, dan gunakan pemicu peristiwa spreadsheet untuk memicu eksekusi data pada peristiwa yang telah ditentukan.

Contoh di bawah ini menambahkan sumber data dengan parameter kueri dan memuat ulang sheet sumber data saat parameter kueri diedit.

Ganti <YOUR_PROJECT_ID> di bawah dengan ID project Google Cloud yang valid.

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

Pada contoh di atas, fungsi addDataSource() menambahkan sumber data ke spreadsheet. Setelah menjalankan addDataSource(), buat pemicu peristiwa di editor Apps Script. Untuk mempelajari cara membuat pemicu peristiwa, lihat Pemicu yang dapat diinstal.

Pilih opsi berikut untuk pemicu Anda:

Sumber acara: Dari spreadsheet
Jenis acara: Saat diedit
Fungsi yang akan dijalankan: refreshOnParameterEdit

Setelah pemicu dibuat, sheet sumber data akan otomatis dimuat ulang setiap kali sel parameter diedit.

Memecahkan masalah

Pesan error Resolusi

Gunakan enableBigQuery() untuk mengaktifkan eksekusi data bagi sumber data BIGQUERY. Error ini menunjukkan bahwa SpreadsheetApp.enableBigQueryExecution() tidak dipanggil sebelum mengambil data BigQuery.
Panggil SpreadsheetApp.enableBigQueryExecution() dalam fungsi yang menggunakan metode untuk eksekusi BigQuery.
Seperti, refreshData() pada objek sumber data, Spreadsheet.insertDataSourceTable(), dan DataSource.updateSpec().
Metode ini memerlukan cakupan OAuth bigquery.readonly tambahan agar dapat berfungsi.

Tidak diizinkan untuk menindaklanjuti sumber data.
Hubungi administrator Anda untuk mengaktifkan fitur tersebut. Error ini menunjukkan bahwa akun tidak mengaktifkan Sheet yang Terhubung.
Sheet yang Terhubung hanya tersedia untuk Google Workspace pengguna dengan langganan tertentu.
Hubungi administrator Anda untuk mengaktifkan fitur tersebut.

Pesan error	Resolusi
Gunakan `enableBigQuery()` untuk mengaktifkan eksekusi data bagi sumber data BIGQUERY.	Error ini menunjukkan bahwa `SpreadsheetApp.enableBigQueryExecution()` tidak dipanggil sebelum mengambil data BigQuery. Panggil `SpreadsheetApp.enableBigQueryExecution()` dalam fungsi yang menggunakan metode untuk eksekusi BigQuery. Seperti, `refreshData()` pada objek sumber data, `Spreadsheet.insertDataSourceTable()`, dan `DataSource.updateSpec()`. Metode ini memerlukan cakupan OAuth bigquery.readonly tambahan agar dapat berfungsi.
Tidak diizinkan untuk menindaklanjuti sumber data. Hubungi administrator Anda untuk mengaktifkan fitur tersebut.	Error ini menunjukkan bahwa akun tidak mengaktifkan Sheet yang Terhubung. Sheet yang Terhubung hanya tersedia untuk Google Workspace pengguna dengan langganan tertentu. Hubungi administrator Anda untuk mengaktifkan fitur tersebut.