कनेक्टेड शीट की सुविधा का इस्तेमाल करना

कनेक्टेड शीट Google Sheets की एक सुविधा है. इसकी मदद से, सीधे Sheets में BigQuery डेटा का विश्लेषण किया जा सकता है. कनेक्टेड शीट को स्प्रेडशीट सेवा का इस्तेमाल करके, प्रोग्राम के हिसाब से ऐक्सेस किया जा सकता है.

कनेक्टेड शीट की सामान्य कार्रवाइयां

BigQuery से कनेक्ट करने और डेटा का विश्लेषण करने के लिए, DataSource क्लास और ऑब्जेक्ट का इस्तेमाल करें. नीचे दी गई टेबल में, DataSource की सबसे आम कार्रवाइयों और उन्हें Apps Script में बनाने का तरीका बताया गया है:

कार्रवाई 'Google Apps स्क्रिप्ट' की क्लास इस्तेमाल करने का तरीका
किसी शीट को 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 डेटा को ऐक्सेस करने के लिए, आपको अपने Google Apps Script कोड में enableBigQueryExecution() तरीका शामिल करना होगा. इस तरीके से, आपके Google Apps Script प्रोजेक्ट में bigquery.readonly OAuth का ज़रूरी स्कोप जुड़ जाता है.

नीचे दिया गया सैंपल, फ़ंक्शन में कॉल किया गया SpreadsheetApp.enableBigQueryExecution() तरीका दिखाता है:

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

मेनिफ़ेस्ट फ़ाइल में अतिरिक्त OAuth स्कोप जोड़ें

आपके कोड में इस्तेमाल किए गए फ़ंक्शन के आधार पर, ज़्यादातर OAuth स्कोप, मेनिफ़ेस्ट फ़ाइल में अपने-आप जुड़ जाते हैं. अगर आपको किसी BigQuery डेटा को ऐक्सेस करने के लिए अतिरिक्त स्कोप की ज़रूरत है, तो साफ़ तौर पर जानकारी देने वाले स्कोप सेट किए जा सकते हैं.

उदाहरण के लिए, Google Drive में होस्ट किए गए BigQuery डेटा के लिए क्वेरी करने के लिए, आपको अपनी मेनिफ़ेस्ट फ़ाइल में 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 डेटा सोर्स जोड़ने के लिए, डेटा सोर्स की खास जानकारी वाली डेटा सोर्स शीट डालें. झलक वाला डेटा फ़ेच करने के लिए, डेटा सोर्स शीट अपने-आप रीफ़्रेश हो जाती है.

नीचे <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() चलाने के बाद, Apps Script एडिटर में इवेंट ट्रिगर बनाएं. इवेंट ट्रिगर बनाने का तरीका जानने के लिए, इंस्टॉल किए जा सकने वाले ट्रिगर देखें.

अपने ट्रिगर के लिए इन विकल्पों को चुनें:

  • इवेंट सोर्स: स्प्रेडशीट से
  • इवेंट का टाइप: बदलाव करने पर
  • चलाने के लिए फ़ंक्शन: refreshOnParameterEdit

ट्रिगर बनाने के बाद, पैरामीटर सेल में हर बार बदलाव किए जाने पर डेटा सोर्स शीट अपने-आप रीफ़्रेश हो जाती है.

समस्या हल करें

गड़बड़ी का मैसेज रिज़ॉल्यूशन
BIGQUERY डेटा सोर्स को लागू करने के लिए, enableBigQuery() का इस्तेमाल करें. इस गड़बड़ी से पता चलता है कि BigQuery डेटा फ़ेच करने से पहले SpreadsheetApp.enableBigQueryExecution() को कॉल नहीं किया जाता है.
BigQuery एक्ज़ीक्यूशन के लिए तरीकों का इस्तेमाल करने वाले फ़ंक्शन में SpreadsheetApp.enableBigQueryExecution() को कॉल करें.
इस तरह, डेटा सोर्स ऑब्जेक्ट पर refreshData(), Spreadsheet.insertDataSourceTable(), और DataSource.updateSpec().
इन तरीकों का इस्तेमाल करने के लिए, अतिरिक्त bigquery.readonly OAuth स्कोप की ज़रूरत होती है.
डेटा सोर्स पर कार्रवाई करने की अनुमति नहीं है.
इस सुविधा को चालू करने के लिए, कृपया अपने एडमिन से संपर्क करें.		 इस गड़बड़ी से पता चलता है कि इस खाते में कनेक्टेड शीट की सुविधा चालू नहीं है.
कनेक्टेड शीट की सुविधा सिर्फ़ Google Workspace कुछ खास सदस्यताओं वाले उपयोगकर्ताओं के लिए उपलब्ध है.
सुविधा चालू करने के लिए अपने एडमिन से संपर्क करें.