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

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

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

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

الإجراء	فئة "برمجة تطبيقات Google"	طريقة الاستخدام
ربط ورقة بيانات بمصدر بيانات متوافق	DataSourceSpec	SpreadsheetApp.newDataSourceSpec()
اختيار مصدر بيانات	DataSource	Spreadsheet.insertDataSourceSheet().getDataSource()
إضافة ورقة مصدر بيانات	DataSourceSheet	Spreadsheet.insertDataSourceSheet()
إضافة جدول محوري	DataSourcePivotTable	Range.insertDataSourcePivotTable()
سحب البيانات إلى استخراج	DataSourceTable	Range.insertDataSourceTable()
استخدام صيغة	DataSourceFormula	Range.setFormula()
إضافة رسم بياني	DataSourceChart	Sheet.insertDataSourceChart()

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

للوصول إلى بيانات BigQuery، يجب تضمين enableBigQueryExecution() method في رمز Google Apps Script. تُضيف هذه الطريقة نطاق bigquery.readonly OAuth المطلوب إلى مشروعك على "برمجة تطبيقات Google".

يعرض المثال التالي طريقة SpreadsheetApp.enableBigQueryExecution() التي يتمّ استدعاؤها ضمن دالة:

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

للوصول إلى بيانات Looker، يجب تضمين طريقة enableLookerExecution() في رمز Google Apps Script. سيؤدي الوصول إلى Looker في Apps Script إلى إعادة استخدام رابط حسابك الحالي على Google مع Looker.

يعرض المثال التالي طريقة SpreadsheetApp.enableLookerExecution() التي يتمّ استدعاؤها ضمن دالة:

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

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

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

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

يعرض العيّنة التالية الجزء 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 ومصدر بيانات Looker على التوالي.

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

Looker

لإضافة مصدر بيانات 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 على dataSource في BigQuery تم إنشاؤه في نموذج الرمز الذي يضيف مصدر بيانات BigQuery.

على عكس البيانات العادية في جداول الشبكة التي تتم الإشارة إليها من خلال فهرس الخلية أو علامات 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());
}

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

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

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

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

• مصدر الحدث: من جدول البيانات
• نوع الحدث: عند التعديل
• الوظيفة المطلوب تنفيذها: refreshOnParameterEdit

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

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

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