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

דף זה מתאר כיצד להשתמש בתרשימים של Google עם Google Sheets.

הקדמה

התרשימים של Google והגיליונות האלקטרוניים של Google משולבים היטב. ניתן למקם תרשים Google בגיליון אלקטרוני של Google, ותרשימי 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 Sheets היא מקור למיון וסינון של נתונים, ואפשר להשתמש בכל מקור שתומך בשפת השאילתות כמקור נתונים.

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

הרשאה

כדי לגשת לגיליונות אלקטרוניים פרטיים, משתמשים ב-Google Sheets כדי לקבל גישה לגיליונות אלקטרוניים פרטיים באמצעות Google API Vision (בקשות tq).

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

במקרים שבהם השיתוף באמצעות קישור אינו פתרון ישים, המפתחים יצטרכו לשנות את הקוד כדי להעביר פרטי כניסה בפרוטוקול 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

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

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

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

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

צריך לעדכן את האתר שלך כדי לרכוש פרטי כניסה ל-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 לאימות:

שימוש בהיקף האחסון של drive.file

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

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

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