שימוש בגיליונות מקושרים

גיליונות מקושרים היא תכונה ב-Google Sheets שמאפשרת לנתח נתוני BigQuery ישירות מתוך Sheets. אפשר לגשת לגיליונות מקושרים באופן פרוגרמטי דרך שירות 'גיליונות אלקטרוניים'.

פעולות נפוצות בגיליונות מקושרים

ניתן להשתמש במחלקות ובאובייקטים של DataSource כדי להתחבר ל-BigQuery ולנתח נתונים. בטבלה הבאה מפורטות הפעולות הנפוצות ביותר מסוג 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, צריך לכלול את ה-method enableBigQueryExecution() בקוד של הסקריפט של Google Apps. השיטה הזו מוסיפה את היקף ה-OAuth הנדרש bigquery.readonly לפרויקט הסקריפט של Google Apps.

בדוגמה הבאה מוצגת השיטה SpreadsheetApp.enableBigQueryExecution() שנקראת בתוך פונקציה:

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

הוספת היקפי הרשאות של OAuth לקובץ המניפסט

רוב ההיקפים של OAuth נוספים באופן אוטומטי לקובץ המניפסט בהתאם לפונקציות המשמשות בקוד שלכם. אם אתם צריכים היקפים נוספים כדי לגשת לנתונים מסוימים ב-BigQuery, אתם יכולים להגדיר היקפים מפורשים.

לדוגמה, כדי לבצע שאילתה לגבי נתוני BigQuery שמתארחים ב-Google Drive, צריך להוסיף היקף של OAuth ל-Drive לקובץ המניפסט.

בדוגמה הבאה מוצג החלק oauthScopes של קובץ מניפסט. הוא מוסיף היקף של OAuth ב-Drive, בנוסף להיקפי ההרשאות המינימליים הנדרשים של spreadsheet ו-bigquery.readonly של 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

אחרי שיוצרים טריגר, הגיליון של מקור הנתונים מתרענן באופן אוטומטי בכל פעם שעורכים את תא הפרמטר.

פתרון בעיות

הודעת השגיאה	רזולוציה
יש להשתמש ב-enableBigQuery() כדי לאפשר הפעלות של נתונים למקורות נתונים מסוג BIGQUERY.	השגיאה הזו מציינת שהפונקציה SpreadsheetApp.enableBigQueryExecution() לא מופעלת לפני אחזור נתוני BigQuery. אפשר להפעיל את SpreadsheetApp.enableBigQueryExecution() בפונקציות שמשתמשות בשיטות לביצוע ב-BigQuery. כמו refreshData() באובייקטים של מקור נתונים, Spreadsheet.insertDataSourceTable() ו-DataSource.updateSpec(). השיטות האלה דורשות היקף OAuth נוסף של bigquery.readonly כדי שהן יפעלו.
לא מורשים לפעול במקורות נתונים. כדי להפעיל את התכונה, עליך לפנות לאדמין.	השגיאה הזו מציינת ש'גיליונות מקושרים' לא מופעלים בחשבון. גיליונות מקושרים זמינים רק Google Workspace למשתמשים עם מינויים מסוימים. כדי להפעיל את התכונה עליך לפנות לאדמין.