استخدام "أوراق البيانات المرتبطة"

أوراق البيانات المرتبطة هي ميزة في "جداول بيانات Google" تتيح لك تحليل بيانات BigQuery مباشرةً في "جداول بيانات Google". يمكنك الوصول إلى "أوراق البيانات المرتبطة" آليًا باستخدام خدمة "جداول البيانات".

الإجراءات الشائعة في "أوراق البيانات المرتبطة"

استخدِم الفئات والكائنات DataSource للربط بأداة BigQuery وتحليل البيانات. يسرد الجدول أدناه إجراءات "DataSource" الأكثر شيوعًا وكيفية إنشائها في "برمجة التطبيقات":

الإجراء	صف برمجة تطبيقات Google	طريقة الاستخدام
ربط جدول بيانات بأداة 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". تضيف هذه الطريقة نطاق OAuth bigquery.readonly المطلوب إلى مشروع "برمجة تطبيقات Google".

يوضح المثال التالي طريقة SpreadsheetApp.enableBigQueryExecution() التي يتم استدعاءها داخل دالة:

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

إضافة نطاقات OAuth إضافية إلى ملف البيان

تتم إضافة معظم نطاقات OAuth تلقائيًا إلى ملف البيان بناءً على الوظائف المستخدمة في الرمز البرمجي الخاص بك. إذا كنت بحاجة إلى نطاقات إضافية للوصول إلى بيانات معينة في BigQuery، يمكنك تعيين نطاقات صريحة.

على سبيل المثال، لإجراء طلب بحث عن بيانات BigQuery المُستضافة ضمن Google Drive، عليك إضافة نطاق Drive OAuth إلى ملف البيان.

يعرض النموذج التالي الجزء oauthScopes من ملف البيان. يضيف هذا الإصدار نطاق OAuth إلى Drive بالإضافة إلى الحد الأدنى المطلوب لنطاقَي 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 وإنشاء عنصر مصدر بيانات من مصدر البيانات وإعادة تحميل عنصر مصدر البيانات والحصول على حالة التنفيذ. في هذا المثال، يتمّ تنفيذ مقتطفات الرمز بالتسلسل.

إضافة مصدر بيانات BigQuery

لإضافة مصدر بيانات 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();

إضافة عنصر مصدر بيانات

بمجرد إضافة مصدر البيانات إلى جدول البيانات، يمكن إنشاء كائنات مصدر البيانات من مصدر البيانات. في هذا المثال، يتم إنشاء جدول محوري باستخدام 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());
}

استخدام المشغّلات مع "أوراق البيانات المرتبطة"

يمكنك برمجة وظائف مصدر بيانات "أوراق البيانات المرتبطة" باستخدام العوامل المُشغِّلة والأحداث. على سبيل المثال، استخدِم العوامل المُشغِّلة المستندة إلى الوقت لإعادة تحميل كائنات مصدر البيانات بشكلٍ متكرّر في وقت محدَّد، واستخدِم عوامل تشغيل الأحداث في جدول البيانات لبدء تنفيذ البيانات في حدث محدَّد مسبقًا.

يضيف النموذج التالي مصدر بيانات بمَعلمة طلب بحث، ويُعيد تحميل ورقة مصدر البيانات عند تعديل معلَمة طلب البحث.

يمكنك استبدال <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()، أنشئ عامل تشغيل حدث في محرِّر "برمجة تطبيقات Google". للتعرّف على كيفية إنشاء عامل تشغيل حدث، اطّلِع على مقالة المشغّلات القابلة للتثبيت.

حدِّد الخيارات التالية للمشغِّل:

• مصدر الحدث: من جدول البيانات
• نوع الحدث: عند التعديل
• دالة قيد التشغيل: refreshOnParameterEdit

بعد إنشاء المشغل، تتم إعادة تحميل ورقة مصدر البيانات تلقائيًا في كل مرة يتم فيها تعديل خلية المعلمة.

تحديد المشاكل وحلّها

رسالة الخطأ	درجة الدقّة
استخدِم "enableBigQuery()" لتفعيل عمليات تنفيذ البيانات لمصادر بيانات BIGQUERY.	يشير هذا الخطأ إلى أنّه لم يتم استدعاء "SpreadsheetApp.enableBigQueryExecution()" قبل جلب بيانات BigQuery. استدع "SpreadsheetApp.enableBigQueryExecution()" في الدوال التي تستخدم طرقًا لتنفيذ BigQuery. مثل refreshData() في كائنات مصدر البيانات وSpreadsheet.insertDataSourceTable() وDataSource.updateSpec(). تتطلب هذه الطرق نطاق OAuth إضافيًا bigquery.readonly لكي تعمل.
غير مسموح باتخاذ إجراءات بشأن مصادر البيانات. يُرجى الاتصال بالمشرف لتفعيل الميزة.	يشير هذا الخطأ إلى أنّه لم يتم تفعيل "أوراق البيانات المرتبطة" في الحساب. لا تتوفّر ميزة "أوراق البيانات المرتبطة" إلا Google Workspace للمستخدمين الذين لديهم اشتراكات معيّنة. يُرجى التواصل مع المشرف لتفعيل الميزة.