המסמך הזה מיועד לאדמינים. כדי לנהל את הקבצים שלכם, אתם יכולים להיכנס למרכז הלמידה.
"גיליונות מקושרים" היא תכונה שבעזרתה אתם יכולים לגשת למיליארדי שורות של נתונים בגיליונות אלקטרוניים, לנתח אותם, ליצור מהם תרשימים ולשתף אותם. זו התכונה ב-Sheets שמקבילה למחבר הנתונים. אתם יכולים גם להשתמש בגיליונות מקושרים כדי:
- לעבוד עם שותפים, אנליסטים או בעלי תפקידים אחרים בממשק מוכר של גיליון אלקטרוני.
- לאפשר למשתמשים להעניק גישה לשותפי עריכה.
- הבטחת מקור אמת יחיד (SSOT) לניתוח נתונים בלי ייצוא נוסף של קובצי CSV.
- לנתח נתונים בתוך מתחם שמגביל את הגישה על סמך מאפיינים, כמו כתובת ה-IP של המשתמש ופרטי המכשיר.
אתם יכולים להריץ שאילתות מגיליונות מקושרים ב-BigQuery או ב-Looker באופן ידני או לפי לוח זמנים מוגדר. אפליקציית Sheets שומרת את תוצאות השאילתות האלה בגיליון האלקטרוני כדי שתוכלו לנתח ולשתף אותן. בסרטוני ההדרכה האלה אפשר ללמוד איך להשתמש בגיליונות מקושרים עם BigQuery.
אתם יכולים לראות אירועי שאילתות של גיליונות מקושרים באירועים ביומן של Drive.
הגדרת BigQuery לניתוח נתונים
שלב 1: מפעילים את Google Cloud
מוודאים ש-Google Cloud מופעל בארגון. הוראות מופיעות במאמר בדיקה אילו אפליקציות מופעלות אצל משתמש, קבוצה או יחידה ארגונית. אם אתם צריכים להפעיל את Google Cloud, קראו את המאמר הפעלה או השבתה של Google Cloud למשתמשים.
הוראות לשימוש בגיליונות מקושרים עם BigQuery זמינות במאמר איך מתחילים לעבוד עם נתוני BigQuery ב-Google Sheets.
שלב 2: בדיקת התפקידים ב-IAM
אתם משתמשים בתפקידים ב-IAM (ניהול זהויות והרשאות גישה) כדי להקצות הרשאות לנתונים שהמשתמשים יכולים לגשת אליהם. כדי להוסיף פרויקט BigQuery ל-Sheets או להשתמש בו, תפקיד ה-IAM של המשתמש ב-BigQuery צריך להיות bigquery.user או bigquery.jobUser ו-bigquery.dataViewer.
מידע נוסף על התפקידים האלה זמין במאמר תפקידי IAM מוגדרים מראש ב-BigQuery.
הפעולות שהמשתמשים יכולים לבצע תלויות בתפקיד שלהם ב-IAM ובהרשאות שלהם בגיליון האלקטרוני, ולא בהרשאות של הבעלים של הגיליון האלקטרוני. אנשים מחוץ לארגון יכולים לבצע פעולות בגיליונות של הארגון רק אם אתם מאפשרים זאת.
מגבלות בגיליונות אלקטרוניים ושגיאות בחישובים
כדי למנוע בעיות בביצועים של הנתונים, חשוב לזכור את המגבלות הבאות על תאים ונוסחאות:
- מגבלת תאים: ב-Google Sheets אפשר להוסיף עד 10,000,000 תאים בכל הכרטיסיות. אם חורגים מהמגבלה הזו כשמחלצים מערכי נתונים גדולים, מופיעה השגיאה
\#NUM\!. - מגבלות על נוסחאות: נוסחאות מורכבות שחורגות ממספר שלבי ההערכה מחזירות את הערך
Calculation limit was reached while trying to compute this formula.
כדי לפתור את המגבלות האלה, מחקו שורות ועמודות שלא בשימוש או צברו נתונים נצברים ב-BigQuery לפני הייבוא. מידע על מגבלות האחסון הכלליות זמין במאמר קבצים שאפשר לאחסן ב-Google Drive.
| פעולות ב-Sheets | תפקיד IAM נדרש ב-BigQuery | הרשאות נדרשות ב-Sheets |
|---|---|---|
| יצירת תרשימים, טבלאות צירים, נוסחאות או חילוצי נתונים באמצעות טבלאות או תצוגות מפורטות של BigQuery |
bigquery.user אוbigquery.jobUser ו-bigquery.dataViewer |
הרשאת עריכה |
| לצפות בתרשימים, בטבלאות צירים, בנוסחאות, בחילוצי נתונים או בתצוגות מקדימות שנוצרו מנתוני BigQuery | ללא | עורך או צופה |
| יצירה או עריכה של שאילתה מותאמת אישית ב-BigQuery |
bigquery.user או bigquery.jobUser ו-bigquery.dataViewer |
הרשאת עריכה |
| הצגת שאילתה מותאמת אישית ב-BigQuery | ללא | עורך או צופה |
| רענון נתונים מ-BigQuery |
bigquery.user או bigquery.jobUser ו-bigquery.dataViewer |
הרשאת עריכה |
שלב 3: הקצאת תפקידי IAM
אתם מקצים תפקידי IAM למערכי הנתונים במסוף BigQuery. פרטים נוספים זמינים במאמר שליטה בגישה למשאבים באמצעות IAM.
שלב 4: (אופציונלי) הגדרת VPC Service Controls כדי לאפשר גיליונות מקושרים
בנוסף לשימוש ב-IAM כדי לאשר לאילו משתמשים יש גישה לנתוני BigQuery, אתם יכולים להשתמש ב-VPC Service Controls כדי ליצור גבולות גזרה לשירות שמגבילים את הגישה על סמך מאפיינים כמו כתובת ה-IP של המשתמש ופרטי המכשיר. המשתמשים יכולים להשתמש בגיליונות מקושרים כדי לגשת לנתוני BigQuery שמוגנים על ידי VPC Service Controls רק אם מגדירים את ההיקף כך שיאפשר ל-Sheets להעתיק את תוצאות השאילתה לגיליונות האלקטרוניים של המשתמשים. פרטים נוספים זמינים במאמר בנושא בקרת גישה.
הגדרת Looker לניתוח נתונים
כדי להשתמש בגיליונות מקושרים עם Looker, צריך להפעיל את הגישה לשירותים שלא נשלטים בנפרד במסוף Google Admin. מידע נוסף זמין במאמר איך קובעים את הרשאות הגישה לשירות בלי מתג נפרד. בנוסף, אדמין ב-Looker צריך להפעיל את הגיליונות המקושרים בממשק המשתמש לניהול של Looker. הוראות מפורטות יותר זמינות במאמר שימוש בגיליונות מקושרים ל-Looker.
המשתמשים יכולים להעניק גישה לגיליונות מקושרים ב-BigQuery
התכונה הזו נתמכת במהדורות הבאות: Enterprise Standard ו-Enterprise Plus, Education Standard ו-Education Plus, Enterprise Essentials ו-Enterprise Essentials Plus. השוואה בין המהדורותאתם יכולים לאפשר למשתמשים להעניק גישה לגיליונות מקושרים ל-BigQuery, כדי שהם יוכלו לשתף פעולה עם משתמשים אחרים בניתוח נתונים ובהרצת שאילתות.
כדי להעניק גישה, המשתמשים צריכים לשתף את הגיליון עם המשתמש האחר. עם זאת, הם לא יכולים להקצות גישה לגיליון שמשותף באופן ציבורי באמצעות קישור. אתם יכולים לבדוק מי המשתמש שהעניק גישה ומי המשתמש שמריץ שאילתה באירועים ביומן של Drive או ביומני הביקורת של Cloud.
הפעלה או השבתה של הענקת גישה
לפני שמתחילים: אם זה רלוונטי, קראו את המאמר איך מפעילים הגדרה למחלקה או לקבוצה.
-
במסוף Google Admin, נכנסים לתפריט
אפליקציות
Google Workspace
Drive ו-Docs
תכונות ואפליקציות.כדי לעשות את זה צריך הרשאות אדמין להגדרות השירות.
- בקטע הענקת גישה בגיליונות מקושרים, לוחצים על סמל העריכה
. -
(אופציונלי) כדי להחיל את ההגדרה רק על חלק מהמשתמשים, בצד, בוחרים יחידה ארגונית (בדרך כלל מדובר במחלקה) או הגדרות לקבוצת משתמשים (מתקדם).
הגדרות של קבוצות מבטלות את ההגדרות של היחידות הארגוניות. מידע נוסף
- בהגדרות של הענקת גישה, מסמנים או מבטלים את הסימון של משתמשים עם גישת עריכה בגיליון אלקטרוני יכולים להפעיל אפשרות הענקת גישה לגיליונות מקושרים.
- אם אתם מגדירים יחידה ארגונית או קבוצה, בחרו באפשרות רק משתמשים ביחידה ארגונית או בקבוצה ספציפית יכולים להשתמש בהענקת גישה.
- אם רוצים לאפשר לכל משתמש עם גישה לגיליון להקצות גישה, בוחרים באפשרות כל משתמש יכול להקצות הרשאות. האפשרות הזו כוללת משתמשים מחוץ לארגון אם יש להם גישה לגיליון.
-
לוחצים על שמירה. אפשר גם ללחוץ על שינוי מברירת המחדל לגבי יחידה ארגונית מסוימת.
אם רוצים לשחזר אחר כך את הערך שעבר בירושה, לוחצים על ירושה (או על ביטול הגדרות כשמדובר בקבוצה).
אם אתם מפעילים את האפשרות להעניק גישה, אתם יכולים להפנות את המשתמשים שלכם להוראות להענקת גישה לגיליון.
צפייה באירועים ביומן של גיליונות מקושרים
כשגיליונות מקושרים ניגשים לנתונים של BigQuery ו-Looker, הרשומות מתועדות באירועים ביומן של Drive. הרשומות מתועדות גם ביומני הביקורת של Cloud לגבי הגישה ל-BigQuery, וב-System Activity History Explore לגבי הגישה ל-Looker. ביומנים אפשר לראות מי נכנס לנתונים ומתי.
ניתוח אירועים ביומן של Drive באמצעות Reporting API
במאמר בנושא גישה לנתוני אירועים ביומן של Drive מוסבר איך לנתח אירועים ביומן של Drive דרך מסוף Google Admin.
אתם יכולים להשתמש ב-Reports API כדי לראות את אירועי השאילתות בגיליונות מקושרים. בדוגמה הבאה מאחזרים את כל האירועים ב-Drive לפי סוג האירוע "שאילתה בגיליונות מקושרים":
תגובת ה-JSON המלאה לקריאה הזו ל-API מוצגת בקטע "תגובת JSON מלאה" שבהמשך הדף.
המשתמש שהתחיל את השאילתה מוצג בתור הגורם המבצע.
ב-Sheets מוצג מידע נוסף על השאילתה שהופעלה כפרמטרים.
השדה execution_trigger מוגדר על סמך האופן שבו השאילתה מופעלת מ-Sheets:
| תווית | איך השאילתה מופעלת |
|---|---|
| sheets_ui | באופן ידני דרך ממשק המשתמש של Sheets |
| לוח זמנים | באמצעות התכונה "רענון מתוזמן" ב-Sheets |
| api | באמצעות Sheets API |
| apps-script | באמצעות Apps Script |
השדה
query_type מוגדר על סמך מחבר הנתונים.| תווית | מחבר נתונים |
|---|---|
| big_query | BigQuery |
| looker | Looker |
השדה data_connection_id מוגדר על סמך המזהה של חיבור הנתונים. ב-BigQuery, זהו מזהה הפרויקט לחיוב. ב-Looker, זו כתובת ה-URL של המכונה.
הערך של execution_id מוגדר על סמך המזהה של השאילתה שהופעלה.
| מבנה הערך | ישות של שאילתה |
|---|---|
| jobs/<JOB_ID> | משימה ב-BigQuery |
| datasets/<DATASET_NAME>/tables/<TABLE_NAME> | טבלה ב-BigQuery |
| query_tasks/<QUERY_TASK_ID> | שאילתת Looker |
כתובת האימייל של המשתמש שפרטי הכניסה שלו שימשו לביצוע הפעולה מופיעה ביומנים בשדה delegating_principal.
תגובת JSON מלאה
ניתוח של יומני הביקורת ב-Cloud באמצעות Logs Explorer לחיבורים של BigQuery
לכל גיליון אלקטרוני יש מזהה גיליון ייחודי שמופיע בכתובת ה-URL של הגיליון האלקטרוני. רשומות ביומן בפורמט BigQueryAuditMetadata מכילות את המזהה של הגיליון האלקטרוני שממנו נשלחה הבקשה לגישה לנתונים ב-BigQuery.
אתם יכולים ליצור שאילתות כדי לאחזר ולנתח יומנים באמצעות Logs Explorer במסוף Google Cloud. ב-Logs Explorer, מזינים:
כך מוצגות רשומות עם מזהה גיליון אלקטרוני לא ריק, כמו בדוגמה הבאה:
ב-Sheets נוסף מידע למשימות של שאילתות באמצעות תוויות של משימות. הם יכולים לספק לכם יותר נתונים לניתוח, כמו בדוגמה הזו:
הערך של השדה sheets_trigger מוגדר לפי האופן שבו השאילתה מופעלת מ-Sheets:
| תווית | איך השאילתה מופעלת |
|---|---|
| משתמש | באופן ידני דרך ממשק המשתמש של Sheets |
| לוח זמנים | באמצעות התכונה "רענון מתוזמן" ב-Sheets |
| api | באמצעות Sheets API |
| apps-script | באמצעות Apps Script |
לדוגמה, כדי למצוא רשומות שמתאימות לרענונים מתוזמנים של גיליונות מקושרים, משתמשים בשאילתה הבאה ב-Logs Explorer:
אם הגישה שהוקצתה הופעלה, אתם יכולים למצוא ביומנים את כתובת האימייל של המשתמש שפרטי הכניסה שלו שימשו להרצת השאילתה. אתם יכולים גם למצוא את כתובת האימייל של המשתמש שהפעיל את השאילתה, כמו בדוגמה הבאה:
הערה: השדה serviceAccountDelegationInfo מופיע רק אם נעשה שימוש בגישה שהוקצתה לשאילתה. במקרה כזה, האדם שמופיע בקטע principalEmail הוא זה שהעניק את הגישה.
למידע נוסף, קראו את המאמרים שימוש ב-Logs Explorer ויצירת שאילתות ב-Logs Explorer.
מידע נוסף על יומני ביקורת של BigQuery, מזהי גיליונות אלקטרוניים, פורמט BigQueryAuditMetadata, SheetsMetadata, שיתוף גיליונות אלקטרוניים ו-Google Sheets API.
ניתוח פעילות המערכת ב-Looker
- במכונה של Looker, בצד ימין לוחצים על Explore History.
- בקטע חיפוש שדה, מזינים שם לקוח API ולוחצים על סמל הסינון
כדי להוסיף את השדה הזה למערך הנתונים. - בקטע Filters, בוחרים באפשרות is equal to ובשדה שליד מזינים Connected Sheets.
- בקטע Find a Field, מזינים את הערך Connected Sheets Spreadsheets ID כדי להוסיף את השדה הזה למערך הנתונים.
- בקטע Find a Field, מזינים Connected Sheets Trigger כדי להוסיף את השדה הזה למערך הנתונים.
- בקטע Find a Field, מזינים History Slug כדי להוסיף את השדה הזה למערך הנתונים.
- ההיסטוריה של Slug מקבילה ל-QUERY_TASK_ID שמתועד באירועים ביומן של Drive. אם רוצים למצוא שאילתה ספציפית ביומן של Drive, מוסיפים מסנן בשדה הזה.
- (אופציונלי) כדי להוסיף שדות נוספים, כמו שם משתמש ותאריך יצירת ההיסטוריה, למערך הנתונים, בוחרים אותם.
- (אופציונלי) כדי להוסיף פילטרים, בוחרים אותם.
לדוגמה, אפשר לסנן את History Created Date ל-is in the past 7 days, או לסנן לפי Spreadsheet ID ספציפי כדי לבדוק רק את שאילתות Looker שהופעלו ממזהה גיליון אלקטרוני ספציפי. - לוחצים על הפעלה.
פתרון בעיות
אם Sheets קורס
בחלק העליון של הגיליון, לוחצים על שליחת משוב.
עדכונים של BigQuery לא מופיעים בגיליונות מקושרים
בקובצי Sheets, לוחצים על רענון כדי לייצא את העדכונים של נתוני BigQuery לגיליונות מקושרים. כדי לרענן את כל הפריטים בגיליונות מקושרים, לוחצים על נתונים מחברי נתונים רענון נתונים רענון של הכל.
המשתמשים לא יכולים לפתוח קובץ של גיליונות מקושרים
אם הגדרתם הרשאות מסוימות בקובצי Sheets בארגון, כמו הגבלת הגישה של משתמשים מחוץ לארגון לקובצי Sheets, המשתמשים האלה לא יכולים לפתוח קובצי גיליונות מקושרים. כדי לשנות את ההרשאות, אפשר לעבור למאמר בנושא הגדרת הרשאות שיתוף של משתמשי Drive.
אם אתם עדיין נתקלים בבעיות, אתם יכולים לעבור אל פתרון בעיות שקשורות לנתוני BigQuery ב-Google Sheets ופתרון בעיות בגיליונות מקושרים ל-Looker.