גיליונות אלקטרוניים של Google

בדף זה נסביר איך להשתמש ב-Google Charts עם גיליונות אלקטרוניים של Google.

1. מבוא
2. הטמעת תרשים בגיליון אלקטרוני
3. יצירת תרשים מגיליון אלקטרוני נפרד

מבוא

Google Charts וגיליונות אלקטרוניים של Google משולבים באופן הדוק. אפשר למקם תרשים של Google בגיליון אלקטרוני של Google, ו-Google Charts יכול לחלץ נתונים מגיליונות אלקטרוניים של Google. במאמר הזה נסביר איך לבצע את שתי הפעולות.

לא משנה באיזו שיטה תבחרו, התרשים שלכם ישתנה בכל פעם שהגיליון האלקטרוני הבסיסי ישתנה.

הטמעת תרשים בגיליון אלקטרוני

קל לכלול תרשים בגיליון אלקטרוני. בסרגל הכלים של Google Sheets, לוחצים על Insert ואז על Chart. אפשר לבחור את סוג התרשים ומגוון אפשרויות:

יצירת תרשים מגיליון אלקטרוני נפרד

בדרך כלל, אנשים יוצרים תרשימים של Google על ידי אכלוס טבלת נתונים והכנת התרשים לפי הנתונים האלה. אם תרצו לאחזר את הנתונים מגיליון אלקטרוני של Google, תוכלו לשלוח שאילתה לגיליון האלקטרוני כדי לאחזר את הנתונים לתרשים:

function drawChart() {
  var query = new google.visualization.Query(URL);
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  var data = response.getDataTable();
  var chart = new google.visualization.ColumnChart(document.getElementById('columnchart'));
  chart.draw(data, null);
}

הסיבה לכך היא שגיליונות אלקטרוניים של Google תומכים בשפת השאילתות של Google Charts לצורך מיון וסינון של נתונים. כל מערכת שתומכת בשפת השאילתות יכולה לשמש כמקור נתונים.

שימו לב שאי אפשר להשתמש בהרשאות של מי שצופה בהם בלי הרשאה מפורשת. הגיליון האלקטרוני צריך להיות גלוי לכולם, או שהדף צריך להשיג באופן מפורש פרטי כניסה של משתמש קצה, כפי שמפורט בקטע Authorization בדף הזה.

כדי להשתמש בגיליון אלקטרוני של Google כמקור נתונים, צריך את כתובת ה-URL שלו:

1. פותחים גיליון אלקטרוני קיים. הגיליון האלקטרוני צריך להיות בפורמט הצפוי על ידי התצוגה החזותית, וצריכות להיות לו הרשאות צפייה מתאימות. (הצגת ההרשאות 'גלוי לכולם באינטרנט' או 'כל מי שיש לו את הקישור' תהיה הקלה ביותר, וההוראות בקטע הזה מבוססות על גיליון אלקטרוני שהוגדר בדרך הזו. אפשר להגביל את זה על ידי השארת הגיליון האלקטרוני כ'פרטי' והענקת גישה לחשבונות Google ספציפיים, אבל תצטרכו לפעול לפי ההוראות למתן הרשאה שבהמשך.
2. מעתיקים את כתובת ה-URL מהדפדפן. לפרטים על בחירת טווחים ספציפיים, ראו טווחי מקור של שאילתות.
3. מספקים את כתובת ה-URL ל-google.visualization.Query(). השאילתה תומכת בפרמטרים האופציונליים הבאים:
   • headers=N: מציין כמה שורות הן שורות כותרת. N מייצג מספר שלם של אפס או יותר. הן יוחרגו מהנתונים ויוקצו כתוויות של עמודות בטבלת הנתונים. אם לא מציינים את הפרמטר הזה, הגיליון האלקטרוני ינחש כמה שורות הן שורות כותרת. חשוב לשים לב שאם כל העמודות הן נתוני מחרוזת, יכול להיות שלגיליון האלקטרוני יהיה קשה לקבוע אילו שורות הן שורות כותרת בלי הפרמטר הזה.
   • gid=N: מציין לאיזה גיליון במסמך שכולל כמה גיליונות לקשר אליו, אם אתם לא מקשרים לגיליון הראשון. N הוא מספר המזהה של הגיליון. כדי לבדוק מהו מספר המזהה, אפשר לעבור לגרסה שפורסמה של הגיליון ולחפש את הפרמטר gid=N בכתובת ה-URL. אפשר גם להשתמש בפרמטר sheet במקום בפרמטר הזה. Gotcha: גיליונות אלקטרוניים של Google עשויים לסדר מחדש את הפרמטר gid בכתובת ה-URL כשצופים בו בדפדפן. אם אתם מעתיקים מדפדפן, חשוב לוודא שכל הפרמטרים נמצאים לפני הסימן # של כתובת ה-URL. לדוגמה: gid=1545912003.
   • sheet=sheet_name: מציין לאיזה גיליון במסמך מרובה גיליונות רוצים לקשר, אם לא מקשרים לגיליון הראשון. sheet_name הוא השם המוצג של הגיליון. לדוגמה: sheet=Sheet5.

הנה דוגמה מלאה:

בהמשך מוצגות שתי דרכים לשרטט את התרשים: אחת באמצעות הפרמטר gid והשנייה באמצעות הפרמטר sheet. הזנת כתובת URL כלשהי בדפדפן תפיק את אותם התוצאה או הנתונים עבור התרשים.

GID

    function drawGID() {
      var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?gid=0&headers=1&tq=' + queryString);
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var data = response.getDataTable();
      var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
      chart.draw(data, { height: 400 });
    }

גיליון

    function drawSheetName() {
      var queryString = encodeURIComponent('SELECT A, H, O, Q, R, U LIMIT 5 OFFSET 8');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);
      query.send(handleSampleDataQueryResponse);
    }

    function handleSampleDataQueryResponse(response) {
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }

      var data = response.getDataTable();
      var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
      chart.draw(data, { height: 400 });
    }

טווחי מקור של שאילתות

כתובת ה-URL של מקור השאילתה מציינת באיזה חלק מהגיליון האלקטרוני להשתמש בשאילתה: תא ספציפי, טווח של תאים, שורות או עמודות או גיליון אלקטרוני שלם. מציינים את הטווח באמצעות התחביר 'range=<range_expr>', לדוגמה:

https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?range=A1:C4
   

הנה כמה דוגמאות שממחישות את התחביר:

• A1:B10 – טווח בין תאים A1 עד B10
• 5:7 – שורות 5-7
• D:F – עמודות D-F
• A:A70 - 70 התאים הראשונים בעמודה A
• A70:A – עמודה A משורה 70 עד לסוף
• B5:5 - B5 עד לסוף שורה 5
• D3:D - D3 עד לסוף עמודה D
• C:C10 – מתחילת עמודה C עד C10

הרשאה

כדי לגשת לגיליונות אלקטרוניים פרטיים דרך GoogleVisualization API (בקשות /tq), צריך להזין את פרטי הכניסה של משתמשי הקצה ל-Google Sheets.

הערה: בגיליונות אלקטרוניים ששותפו עם 'כל מי שיש לו את הקישור יכול להציג' אין צורך בפרטי כניסה. שינוי הגדרות השיתוף של הגיליון האלקטרוני קל יותר מהטמעת הרשאה.

במקרים שבהם שיתוף באמצעות קישור הוא לא פתרון מעשי, המפתחים יצטרכו לשנות את הקוד כדי להעביר פרטי כניסה ב-OAuth 2.0 שאושרו להיקף ההרשאות של Google Sheets API (https://www.googleapis.com/auth/spreadsheets).

מידע נוסף על OAuth 2.0 זמין במאמר שימוש ב-OAuth 2.0 לגישה ל-Google APIs

דוגמה: שימוש ב-OAuth כדי לגשת אל /gviz/tq

דרישה מוקדמת: קבלת מזהה לקוח מ-Google Developer Console

הוראות מפורטות יותר לשילוב עם Identity Platform של Google זמינות במאמרים כניסה באמצעות חשבון Google ויצירת פרויקט ומזהה לקוח במסוף Google API.

כדי לקבל אסימוני OAuth עבור משתמש קצה, קודם צריך לרשום את הפרויקט ב-Google Developer Console ולקבל Client-ID.

1. במסוף המפתחים, יוצרים מזהה לקוח OAuth חדש.
2. בוחרים באפשרות Web application בתור סוג האפליקציה.
3. חשוב לבחור כל שם שהוא לידיעה בלבד.
4. מוסיפים את שם הדומיין (וכל הדומיינים לבדיקה) בתור מקורות מורשים של JavaScript.
5. משאירים את השדה מזהי URI מורשים להפניה אוטומטית ריק.

אחרי שלוחצים על 'יצירה', מעתיקים את מזהה הלקוח לשימוש עתידי. לא צריך את סוד הלקוח לצורך התרגיל הזה.

מעדכנים את האתר כדי לקבל פרטי כניסה של OAuth.

Google מספקת את הספרייה gapi.auth, שמפשטת מאוד את תהליך הרכישה של פרטי כניסה ל-OAuth. דוגמת הקוד הבאה משתמשת בספרייה הזו כדי לקבל פרטי כניסה (בקשת הרשאה במקרה הצורך) ומעבירה את פרטי הכניסה שמתקבלים לנקודת הקצה /gviz/tq.

demo.html

<html>
<body>
  <button id="authorize-button" style="visibility: hidden">Authorize</button>
  <script src="./demo.js" type="text/javascript"></script>
  <script src="https://apis.google.com/js/auth.js?onload=init"></script>
</body>
</html>

demo.js

// NOTE: You must replace the client id on the following line.
var clientId = '549821307845-9ef2xotqflhcqbv10.apps.googleusercontent.com';
var scopes = 'https://www.googleapis.com/auth/spreadsheets';

function init() {
  gapi.auth.authorize(
      {client_id: clientId, scope: scopes, immediate: true},
      handleAuthResult);
}

function handleAuthResult(authResult) {
  var authorizeButton = document.getElementById('authorize-button');
  if (authResult && !authResult.error) {
    authorizeButton.style.visibility = 'hidden';
    makeApiCall();
  } else {
    authorizeButton.style.visibility = '';
    authorizeButton.onclick = handleAuthClick;
  }
}

function handleAuthClick(event) {
  gapi.auth.authorize(
      {client_id: clientId, scope: scopes, immediate: false},
      handleAuthResult);
  return false;
}

function makeApiCall() {
  // Note: The below spreadsheet is "Public on the web" and will work
  // with or without an OAuth token.  For a better test, replace this
  // URL with a private spreadsheet.
  var tqUrl = 'https://docs.google.com/spreadsheets' +
      '/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq' +
      '?tqx=responseHandler:handleTqResponse' +
      '&access_token=' + encodeURIComponent(gapi.auth.getToken().access_token);

  document.write('<script src="' + tqUrl +'" type="text/javascript"></script>');
}

function handleTqResponse(resp) {
  document.write(JSON.stringify(resp));
}

כשההרשאה תאושר, gapi.auth.getToken() יחזיר את כל הפרטים של פרטי הכניסה, כולל access_token שאפשר לצרף לבקשות /gviz/tq.

למידע נוסף על שימוש בספריית gapi לאימות:

• אימות באמצעות ספריית הלקוח של Google APIs ל-JavaScript
• איך משתמשים ב-CORS כדי לגשת לממשקי ה-API של Google

שימוש בהיקף drive.file

בדוגמה הקודמת אנחנו משתמשים בהיקף ההרשאות של Google Sheets API, שמעניק גישת קריאה וכתיבה לכל התוכן של הגיליונות האלקטרוניים של המשתמש. בהתאם לאפליקציה, יכול להיות שתהיה לכך יותר הרשאות מהנדרש. להרשאת קריאה בלבד, משתמשים בהיקף spreadsheets.readonly שמקצה הרשאת קריאה בלבד לגיליונות של המשתמש ולמאפיינים שלהם.

ההיקף של drive.file (https://www.googleapis.com/auth/drive.file) מעניק גישה רק לקבצים שהמשתמש פותח באופן מפורש באמצעות בורר הקבצים של Google Drive, שמופעל דרך Picker API.

שימוש בבורר משנה את זרימת האפליקציה. במקום להדביק כתובת URL או להשתמש בגיליון אלקטרוני בתוך הקוד כמו בדוגמה שלמעלה, המשתמש צריך להשתמש בתיבת הדו-שיח של Picker כדי לבחור לאיזה גיליון אלקטרוני רוצים לגשת לדף. פועלים לפי הדוגמה של Chooseer "Hello World", ומשתמשים ב-google.picker.ViewId.SPREADSHEETS במקום ב-google.picker.ViewId.PHOTOS.