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

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