Halaman ini diterjemahkan oleh Cloud Translation API.

Menggunakan Sheet yang Terhubung

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

Tindakan Sheet yang Terhubung umum

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

Tindakan	Class Google Apps Script	Metode yang akan digunakan
Menghubungkan sheet ke sumber data yang didukung	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Pilih sumber data	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Menambahkan sheet sumber data	`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()`

Menambahkan 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 fungsi:

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

Untuk mengakses data Looker, Anda harus menyertakan metode enableLookerExecution() dalam kode Google Apps Script. Mengakses Looker di Apps Script akan menggunakan kembali Penautan Akun Google yang ada dengan Looker.

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

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

Menambahkan cakupan OAuth tambahan ke file manifes

Saat terhubung dengan BigQuery, sebagian besar cakupan OAuth akan 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 mengkueri data BigQuery yang dihosting dalam Google Drive, Anda harus menambahkan cakupan OAuth Drive ke file manifes.

Contoh berikut menunjukkan bagian oauthScopes dari file manifes. Tindakan ini akan 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, membuat objek sumber data dari sumber data, memuat ulang objek sumber data, dan mendapatkan status eksekusi.

Tambahkan sumber data

Contoh berikut menunjukkan cara menambahkan sumber data BigQuery dan Looker.

BigQuery

Untuk menambahkan sumber data BigQuery ke spreadsheet, masukkan sheet sumber data dengan spec sumber data. Sheet sumber data akan otomatis diperbarui untuk mengambil data pratinjau.

Ganti <YOUR_PROJECT_ID> di bawah dengan project ID 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();

Looker

Untuk menambahkan sumber data Looker ke spreadsheet, masukkan sheet sumber data dengan spec sumber data. Sheet sumber data akan otomatis dimuat ulang untuk mengambil data pratinjau.

Ganti <INSTANCE_URL>,<MODEL_NAME>, <EXPLORE_NAME> dalam contoh berikut dengan URL instance Looker, nama model, dan nama eksplorasi yang valid.

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

Menambahkan objek sumber data

Setelah sumber data ditambahkan ke spreadsheet, objek sumber data dapat dibuat dari sumber data. Dalam contoh ini, tabel pivot dibuat menggunakan DataSourcePivotTable di dataSource BigQuery yang dibuat di contoh kode yang menambahkan sumber data BigQuery.

Tidak seperti data reguler di 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 untuk memuat ulang data bersifat asinkron. Untuk memuat ulang objek sumber data, gunakan metode berikut:

refreshData() memulai eksekusi pembaruan 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 menggambarkan pembaruan 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 Spreadsheet Terhubung dengan pemicu dan peristiwa. Misalnya, gunakan pemicu berbasis waktu untuk memuat ulang objek sumber data berulang kali pada waktu tertentu, dan gunakan pemicu peristiwa spreadsheet untuk memicu eksekusi data pada peristiwa yang telah ditentukan.

Contoh berikut menambahkan sumber data BigQuery dengan parameter kueri dan memuat ulang sheet sumber data saat parameter kueri diedit.

Ganti <YOUR_PROJECT_ID> di bawah dengan project ID 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 Anda mengeksekusi 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 peristiwa: Dari spreadsheet
Jenis peristiwa: 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.
Misalnya, refreshData() pada objek sumber data, Spreadsheet.insertDataSourceTable(), dan DataSource.updateSpec().
Metode ini memerlukan cakupan OAuth bigquery.readonly tambahan agar dapat berfungsi.

Tidak diizinkan untuk melakukan tindakan terhadap sumber data.
Hubungi administrator Anda untuk mengaktifkan fitur ini. Error ini menunjukkan bahwa akun tidak mengaktifkan Spreadsheet yang Terhubung.
Spreadsheet yang Terhubung hanya tersedia untuk pengguna dengan langganan tertentu.
Hubungi administrator Anda untuk mengaktifkan fitur ini.

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. Misalnya, `refreshData()` pada objek sumber data, `Spreadsheet.insertDataSourceTable()`, dan `DataSource.updateSpec()`. Metode ini memerlukan cakupan OAuth bigquery.readonly tambahan agar dapat berfungsi.
Tidak diizinkan untuk melakukan tindakan terhadap sumber data. Hubungi administrator Anda untuk mengaktifkan fitur ini.	Error ini menunjukkan bahwa akun tidak mengaktifkan Spreadsheet yang Terhubung. Spreadsheet yang Terhubung hanya tersedia untuk pengguna dengan langganan tertentu. Hubungi administrator Anda untuk mengaktifkan fitur ini.