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

גיליונות מקושרים היא תכונה של 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, בשם הדגם ובשם הניתוח, בהתאמה.

// 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.
אין הרשאה לפעול במקורות נתונים. כדי להפעיל את התכונה, צריך לפנות לאדמין.	השגיאה הזו מעידה על כך שהחשבון לא מוגדר לשימוש ב-Connected Sheets. התכונה Connected Sheets זמינה רק למשתמשים עם מינויים מסוימים. צריך לפנות לאדמין כדי להפעיל את התכונה.