גיליונות מקושרים מאפשרים לנתח נתונים כמו פטה-בייט ישירות מתוך Google Sheets. אפשר לחבר את הגיליונות האלקטרוניים למחסן נתונים של BigQuery ולבצע את הניתוח באמצעות כלים מוכרים של Sheets, כמו טבלאות צירים, תרשימים ונוסחאות.
במדריך הזה השתמשנו במערך הנתונים הציבורי Shakespeare
כדי ללמוד איך משתמשים בגיליונות מקושרים. מערך הנתונים מכיל את המידע הבא:
שדה | סוג | תיאור |
---|---|---|
מילים | STRING |
מילה ייחודית אחת (כאשר רווח לבן הוא התו המפריד) שחולפה מתוך מאגר הנתונים. |
word_count | INTEGER |
מספר הפעמים שהמילה הזו מופיעה בקורפוס הזה. |
קורפוס | STRING |
היצירה שממנה המילה הזו חולצה. |
corpus_date | INTEGER |
השנה שבה מקור המידע הזה פורסם. |
אם האפליקציה שלכם מבקשת נתונים בגיליונות מקושרים, היא צריכה לספק אסימון OAuth 2.0 שמעניק את ההיקף של bigquery.readonly
, בנוסף להיקפי ההרשאות האחרים שנדרשים לבקשה רגילה של Google Sheets API. מידע נוסף זמין במאמר בחירת היקפים של Google Sheets API.
ניהול של מקור נתונים
מקור נתונים מציין מיקום חיצוני שבו נמצאו נתונים. לאחר מכן מקור הנתונים מחובר לגיליון האלקטרוני.
הוספת מקור נתונים ל-BigQuery
כדי להוסיף מקור נתונים, מספקים AddDataSourceRequest
באמצעות method spreadsheets.batchUpdate
. גוף הבקשה צריך לציין שדה dataSource
מסוג אובייקט DataSource
.
"addDataSource":{
"dataSource":{
"spec":{
"bigQuery":{
"projectId":"PROJECT_ID",
"tableSpec":{
"tableProjectId":"bigquery-public-data",
"datasetId":"samples",
"tableId":"shakespeare"
}
}
}
}
}
מחליפים את PROJECT_ID במזהה פרויקט תקין ב-Google Cloud.
אחרי שיוצרים מקור נתונים, נוצר גיליון DATA_SOURCE
משויך שמספק תצוגה מקדימה של עד 500 שורות. התצוגה המקדימה לא
זמינה מיד. מופעלת הפעלה אסינכרונית כדי לייבא את הנתונים מ-BigQuery.
השדה AddDataSourceResponse
מכיל את השדות הבאים:
dataSource
: האובייקטDataSource
שנוצר. הערךdataSourceId
הוא מזהה ייחודי ברמת הגיליון האלקטרוני. הוא מאוכלס ומתבצעת אליו הפניה כדי ליצור כל אובייקטDataSource
ממקור הנתונים.dataExecutionStatus
: הסטטוס של הפעלה שמייבאת נתונים מ-BigQuery לגיליון התצוגה המקדימה. מידע נוסף זמין בקטע סטטוס הפעלת הנתונים.
עדכון או מחיקה של מקור נתונים
משתמשים בשיטה spreadsheets.batchUpdate
ומציינים בקשת UpdateDataSourceRequest
או DeleteDataSourceRequest
בהתאם.
ניהול אובייקטים במקור הנתונים
אחרי שמוסיפים מקור נתונים לגיליון האלקטרוני, אפשר ליצור ממנו אובייקט של מקור נתונים. אובייקט של מקור נתונים הוא כלי רגיל ב-Sheets, כמו טבלאות צירים, תרשימים ונוסחאות, שמשולב עם 'גיליונות מקושרים' כדי לשפר את ניתוח הנתונים.
יש ארבעה סוגי אובייקטים:
- טבלה אחת (
DataSource
) DataSource
pivotTable- תרשים של
DataSource
- נוסחה אחת (
DataSource
)
הוספת טבלה של מקור נתונים
אובייקט הטבלה, שנקרא 'חילוץ' בעורך Sheets, מייבא קובץ נתונים סטטי של נתונים ממקור הנתונים אל Sheets. בדומה לטבלת צירים, הטבלה מצוינת ומעוגנת לתא השמאלי העליון.
דוגמת הקוד הבאה ממחישה איך להשתמש ב-method spreadsheets.batchUpdate
וב-UpdateCellsRequest
כדי ליצור טבלת מקור נתונים עד 1,000 שורות עם שתי עמודות (word
ו-word_count
).
"updateCells":{
"rows":{
"values":[
{
"dataSourceTable":{
"dataSourceId":"DATA_SOURCE_ID",
"columns":[
{
"name":"word"
},
{
"name":"word_count"
}
],
"rowLimit":{
"value":1000
},
"columnSelectionType":"SELECTED"
}
}
]
},
"fields":"dataSourceTable"
}
מחליפים את DATA_SOURCE_ID במזהה ייחודי ברמת הגיליון האלקטרוני שמזהה את מקור הנתונים.
אחרי שיוצרים טבלה של מקור נתונים, הנתונים לא זמינים באופן מיידי. העורך של Sheets מוצג כתצוגה מקדימה. כדי לאחזר את הנתונים מ-BigQuery, צריך לרענן את הטבלה של מקור הנתונים. אפשר לציין RefreshDataSourceRequest
בתוך אותו batchUpdate
. שימו לב שכל האובייקטים של מקור הנתונים פועלים באופן דומה.
מידע נוסף זמין במאמר רענון אובייקט של מקור נתונים.
אחרי שהרענון מסתיים ולאחר אחזור הנתונים של BigQuery, הטבלה של מקור הנתונים מאוכלסת באופן הבא:
הוספת טבלת צירים של מקור נתונים
בניגוד לטבלת צירים רגילה, טבלת צירים של מקור נתונים מגובה על ידי מקור נתונים ומפנה לנתונים לפי שם העמודה. דוגמת הקוד הבאה ממחישה איך להשתמש בשיטה spreadsheets.batchUpdate
וב-UpdateCellsRequest
כדי ליצור טבלת צירים שמציגה את ספירת המילים הכוללת לפי קורפוס.
"updateCells":{
"rows":{
"values":[
{
"pivotTable":{
"dataSourceId":"DATA_SOURCE_ID",
"rows":{
"dataSourceColumnReference":{
"name":"corpus"
},
"sortOrder":"ASCENDING"
},
"values":{
"summarizeFunction":"SUM",
"dataSourceColumnReference":{
"name":"word_count"
}
}
}
}
]
},
"fields":"pivotTable"
}
מחליפים את DATA_SOURCE_ID במזהה ייחודי ברמת הגיליון האלקטרוני שמזהה את מקור הנתונים.
אחרי אחזור נתוני BigQuery, טבלת הצירים של מקור הנתונים מאוכלסת באופן הבא:
הוספת תרשים של מקור נתונים
דוגמת הקוד הבאה ממחישה איך להשתמש בשיטה spreadsheets.batchUpdate
וב-AddChartRequest
כדי ליצור תרשים של מקור נתונים עם chartType
של COLUMN, שמציג את מספר המילים הכולל לפי קורפוס.
"addChart":{
"chart":{
"spec":{
"title":"Corpus by word count",
"basicChart":{
"chartType":"COLUMN",
"domains":[
{
"domain":{
"columnReference":{
"name":"corpus"
}
}
}
],
"series":[
{
"series":{
"columnReference":{
"name":"word_count"
},
"aggregateType":"SUM"
}
}
]
}
},
"dataSourceChartProperties":{
"dataSourceId":"DATA_SOURCE_ID"
}
}
}
מחליפים את DATA_SOURCE_ID במזהה ייחודי ברמת הגיליון האלקטרוני שמזהה את מקור הנתונים.
אחרי אחזור נתוני BigQuery, התרשים של מקור הנתונים מעובד באופן הבא:
הוספת נוסחה של מקור נתונים
דוגמת הקוד הבאה ממחישה איך להשתמש בשיטה spreadsheets.batchUpdate
וב-UpdateCellsRequest
כדי ליצור נוסחה של מקור נתונים לחישוב מספר המילים הממוצע.
"updateCells":{
"rows":[
{
"values":[
{
"userEnteredValue":{
"formulaValue":"=AVERAGE(shakespeare!word_count)"
}
}
]
}
],
"fields":"userEnteredValue"
}
אחרי אחזור נתוני BigQuery, הנוסחה של מקור הנתונים מאוכלסת באופן הבא:
רענון אובייקט של מקור נתונים
אפשר לרענן אובייקט של מקור נתונים כדי לאחזר את הנתונים העדכניים ביותר מ-BigQuery, על סמך המפרטים הנוכחיים של מקור הנתונים והגדרות ההגדרות שלו. אפשר להשתמש ב-method spreadsheets.batchUpdate
כדי לקרוא לפונקציהRefreshDataSourceRequest
.
לאחר מכן מציינים הפניה אחת או יותר לאובייקט לרענון באמצעות האובייקט DataSourceObjectReferences
.
שימו לב שאפשר ליצור אובייקטים של מקור נתונים וגם לרענן אותם בבקשת batchUpdate
אחת.
סטטוס ביצוע הנתונים
כשיוצרים מקורות נתונים או מרעננים אובייקטים של מקור נתונים, נוצרת הפעלה ברקע כדי לאחזר את הנתונים מ-BigQuery ומחזירים תגובה שמכילה את ה-DataExecutionStatus
.
אם הביצוע מתחיל בהצלחה, DataExecutionState
נמצא בדרך כלל במצב RUNNING
.
התהליך הוא אסינכרוני, ולכן האפליקציה שלכם צריכה להטמיע מודל של תשאול כדי לאחזר מדי פעם את הסטטוס של האובייקטים במקור הנתונים. משתמשים בשיטה spreadsheets.get
עד שהסטטוס מחזיר את המצב SUCCEEDED
או FAILED
. ברוב המקרים, הביצוע מסתיים במהירות, אבל הוא תלוי במורכבות של מקור הנתונים. בדרך כלל, ההפעלה לא נמשכת יותר מ-10 דקות.
נושאים קשורים
- בחירת היקפים של Google Sheets API
- איך מתחילים לעבוד עם נתוני BigQuery ב-Google Sheets
- מסמכי תיעוד של BigQuery
- BigQuery: שימוש בגיליונות מקושרים
- סרטון הדרכה לגיליונות מקושרים