از صفحات متصل استفاده کنید

کاربرگ‌نگار متصل یک ویژگی Google Sheets است که به شما امکان می‌دهد داده‌های BigQuery را مستقیماً در کاربرگ‌نگار تجزیه و تحلیل کنید. می‌توانید با استفاده از سرویس صفحه‌گسترده به صورت برنامه‌نویسی به «کاربرگ‌های متصل» دسترسی داشته باشید.

کنش‌های Common Connected Sheets

از کلاس ها و اشیاء DataSource برای اتصال به BigQuery و تجزیه و تحلیل داده ها استفاده کنید. جدول زیر متداول ترین اقدامات DataSource و نحوه ایجاد آنها در Apps Script را فهرست می کند:

عمل	کلاس Google Apps Script	روش استفاده
یک برگه را به BigQuery متصل کنید	DataSourceSpec	SpreadsheetApp.newDataSourceSpec()
یک منبع داده BigQuery را انتخاب کنید	DataSource	Spreadsheet.insertDataSourceSheet().getDataSource()
یک برگه منبع داده BigQuery اضافه کنید	DataSourceSheet	Spreadsheet.insertDataSourceSheet()
یک جدول محوری اضافه کنید	DataSourcePivotTable	Range.insertDataSourcePivotTable()
داده ها را به یک عصاره بکشید	DataSourceTable	Range.insertDataSourceTable()
از یک فرمول استفاده کنید	DataSourceFormula	Range.setFormula()
یک نمودار اضافه کنید	DataSourceChart	Sheet.insertDataSourceChart()

دامنه های مجوز لازم را اضافه کنید

برای دسترسی به داده‌های BigQuery، باید متد enableBigQueryExecution() را در کد Google Apps Script خود قرار دهید. این روش دامنه bigquery.readonly OAuth مورد نیاز را به پروژه Google Apps Script شما اضافه می کند.

نمونه زیر متد SpreadsheetApp.enableBigQueryExecution() را نشان می دهد که در یک تابع فراخوانی شده است:

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

دامنه های OAuth اضافی را به فایل مانیفست اضافه کنید

اکثر دامنه های OAuth به طور خودکار بر اساس توابع استفاده شده در کد شما به فایل مانیفست اضافه می شوند. اگر برای دسترسی به برخی از داده‌های BigQuery به دامنه‌های اضافی نیاز دارید، می‌توانید محدوده‌های صریح را تنظیم کنید .

به عنوان مثال، برای پرس و جو از داده های BigQuery میزبانی شده در Google Drive ، باید یک محدوده Drive OAuth به فایل مانیفست خود اضافه کنید.

نمونه زیر بخش oauthScopes یک فایل مانیفست را نشان می دهد. علاوه بر حداقل spreadsheet مورد نیاز و دامنه های bigquery.readonly OAuth، یک محدوده درایو OAuth اضافه می کند:

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

مثال: یک شی منبع داده را ایجاد و بازخوانی کنید

مثال زیر نحوه اضافه کردن یک منبع داده BigQuery، ایجاد یک شی منبع داده از منبع داده، بازخوانی شی منبع داده و دریافت وضعیت اجرا را نشان می دهد. در این مثال، قطعات کد به ترتیب اجرا می شوند.

یک منبع داده BigQuery اضافه کنید

برای افزودن یک منبع داده BigQuery به یک صفحه گسترده، یک صفحه منبع داده با مشخصات منبع داده وارد کنید. برگه منبع داده به طور خودکار برای واکشی داده های پیش نمایش به روز می شود.

شناسه پروژه Google Cloud معتبر را جایگزین <YOUR_PROJECT_ID> زیر کنید.

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

یک شی منبع داده اضافه کنید

هنگامی که منبع داده به صفحه گسترده اضافه شد، اشیاء منبع داده را می توان از منبع داده ایجاد کرد. در این مثال، یک جدول محوری با استفاده از DataSourcePivotTable ایجاد می شود.

برخلاف داده‌های معمولی در صفحات شبکه که با نمایه سلولی یا نمادهای A1 ارجاع می‌شوند، داده‌های منابع داده معمولاً با نام ستون‌ها ارجاع می‌شوند. بنابراین، بیشتر تنظیم‌کننده‌های ویژگی در اشیاء منبع داده از نام ستون به عنوان ورودی استفاده می‌کنند.

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

یک شی منبع داده را بازخوانی کنید

می‌توانید اشیاء منبع داده را برای واکشی آخرین داده‌ها از BigQuery بر اساس مشخصات منبع داده و پیکربندی‌های شی، بازخوانی کنید.

فرآیند به‌روزرسانی داده‌ها ناهمزمان است. برای تازه کردن یک شی منبع داده، از روش های زیر استفاده کنید:

1. refreshData() اجرای تازه سازی داده ها را شروع می کند.
2. waitForCompletion() وضعیت پایانی را پس از اتمام اجرای داده ها برمی گرداند. این امر نیاز به ادامه نظرسنجی وضعیت اجرا را از بین می برد.
3. DataExecutionStatus.getErrorCode() کد خطا را در صورت عدم موفقیت اجرای داده دریافت می کند.

نمونه زیر به‌روزرسانی داده‌های جدول محوری را نشان می‌دهد:

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

از تریگرها با صفحات متصل استفاده کنید

عملکردهای منبع داده «کاربرگ‌های متصل» را با راه‌اندازها و رویدادها خودکار کنید. به عنوان مثال، از محرک‌های زمان‌محور برای بازخوانی مکرر اشیاء منبع داده در یک زمان خاص، و استفاده از محرک‌های رویداد صفحه‌گسترده برای راه‌اندازی اجرای داده در یک رویداد از پیش تعریف‌شده استفاده کنید.

نمونه زیر یک منبع داده با پارامتر پرس و جو اضافه می کند و هنگام ویرایش پارامتر پرس و جو، صفحه منبع داده را تازه می کند.

شناسه پروژه Google Cloud معتبر را جایگزین <YOUR_PROJECT_ID> زیر کنید.

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

در نمونه بالا، تابع addDataSource() یک منبع داده را به صفحه گسترده اضافه می کند. پس از اجرای addDataSource() ، یک تریگر رویداد در ویرایشگر Apps Script ایجاد کنید. برای آشنایی با نحوه ایجاد یک راه‌انداز رویداد، به محرک‌های قابل نصب مراجعه کنید.

گزینه های زیر را برای محرک خود انتخاب کنید:

• منبع رویداد : از صفحه گسترده
• نوع رویداد : در حال ویرایش
• تابع برای اجرا : refreshOnParameterEdit

پس از ایجاد تریگر، هر بار که سلول پارامتر ویرایش می شود، برگه منبع داده به طور خودکار تازه می شود.

عیب یابی

پیغام خطا	وضوح
از enableBigQuery() برای فعال کردن اجرای داده برای منابع داده BIGQUERY استفاده کنید.	این خطا نشان می دهد که SpreadsheetApp.enableBigQueryExecution() قبل از واکشی داده های BigQuery فراخوانی نشده است. SpreadsheetApp.enableBigQueryExecution() را در توابعی که از متدهایی برای اجرای BigQuery استفاده می کنند فراخوانی کنید. مانند refreshData() در اشیاء منبع داده، Spreadsheet.insertDataSourceTable() و DataSource.updateSpec() . این روش‌ها برای کار کردن به یک bigquery.readonly OAuth scope نیاز دارند.
مجاز به اقدام بر روی منابع داده نیست. لطفاً برای فعال کردن این ویژگی با سرپرست خود تماس بگیرید.	این خطا نشان می‌دهد که کاربرگ‌نگار متصل در حساب فعال نیست. کاربرگ‌نگار متصل فقط برای کاربران Google Workspace با اشتراک‌های خاص در دسترس است. برای فعال کردن این ویژگی با سرپرست خود تماس بگیرید.