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

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

اقدامات رایج Connected Sheets

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

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

افزودن حوزه‌های مجوز مورد نیاز

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

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

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

برای دسترسی به داده‌های Looker، باید متد enableLookerExecution() را در کد اسکریپت Google Apps خود وارد کنید. دسترسی به Looker در اسکریپت Apps، لینک حساب گوگل موجود شما را با Looker دوباره استفاده خواهد کرد.

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

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

افزودن دامنه‌های OAuth اضافی به فایل مانیفست

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

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

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

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

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

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

اضافه کردن منبع داده

مثال‌های زیر نحوه‌ی افزودن منبع داده‌ی BigQuery و Looker را به ترتیب نشان می‌دهند.

بیگ‌کوئری

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

<YOUR_PROJECT_ID> را با یک شناسه پروژه معتبر 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 به یک صفحه گسترده، یک برگه منبع داده با مشخصات منبع داده وارد کنید. برگه منبع داده به طور خودکار برای دریافت داده‌های پیش‌نمایش به‌روزرسانی می‌شود.

در نمونه زیر، <INSTANCE_URL> ، <MODEL_NAME> و <EXPLORE_NAME> را به ترتیب با یک URL نمونه Looker معتبر، نام مدل و نام کاوش جایگزین کنید.

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

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

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

برخلاف داده‌های معمولی در صفحات گرید که توسط اندیس سلول یا نمادگذاری 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());
}

استفاده از تریگرها با Connected Sheets

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

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

<YOUR_PROJECT_ID> را با یک شناسه پروژه معتبر 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();
}

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

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

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

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

عیب‌یابی

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