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

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

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

אפשר להשתמש במחלקות ובאובייקטים של DataSource כדי להתחבר ל-BigQuery או ל-Looker ולנתח נתונים. בטבלה הבאה מפורטות הפעולות הנפוצות ביותר של DataSource והסבר איך ליצור אותן ב-Apps Script:

פעולה	מחלקת Google 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 לפרויקט Google Apps Script.bigquery.readonly

בדוגמה הבאה מוצגת השיטה 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 בנוסף להיקפי ההרשאות המינימליים הנדרשים 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 ב-BigQuery dataSource שנוצר בדוגמת הקוד שמוסיפה מקור נתונים של 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

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

פתרון בעיות

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