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

גיליונות מקושרים היא תכונה של Google Sheets שמאפשרת לנתח נתונים מ-BigQuery ומ-Looker ישירות ב-Sheets. אפשר לגשת ל-Connected 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. השיטה הזו מוסיפה את היקף ההרשאות הנדרש bigquery.readonly ל-OAuth לפרויקט שלכם ב-Google Apps Script.

בדוגמה הבאה מוצגת הקריאה ל-method‏ SpreadsheetApp.enableBigQueryExecution() בתוך פונקציה:

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

כדי לגשת לנתונים של Looker, צריך לכלול את השיטה enableLookerExecution() בקוד של Google Apps Script. הגישה ל-Looker ב-Apps Script תתבצע באמצעות הקישור הקיים של חשבון Google ל-Looker.

בדוגמה הבאה מוצגת ה-method 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, שם המודל ושם ה-Explore, בהתאמה.

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