Arkusze kalkulacyjne Google

Na tej stronie opisaliśmy, jak korzystać z Wykresów Google w Arkuszach kalkulacyjnych Google.

Wprowadzenie

Wykresy Google i Arkusze kalkulacyjne Google są ściśle zintegrowane. Wykres Google możesz umieścić w arkuszu kalkulacyjnym Google, który pozwala wyodrębnić dane z arkuszy kalkulacyjnych Google. Ta dokumentacja pokazuje, jak wykonać obie te czynności.

Bez względu na wybraną metodę wykres będzie się zmieniać za każdym razem, gdy zmieni się arkusz kalkulacyjny.

Umieszczanie wykresu w arkuszu kalkulacyjnym

Dodawanie wykresu do arkusza kalkulacyjnego jest łatwe. Na pasku narzędzi Arkuszy kalkulacyjnych kliknij „Wstaw”, a następnie „Wykres”. Możesz wybrać typ wykresu i wybrać różne opcje:

Tworzenie wykresu z osobnego arkusza kalkulacyjnego

Zwykle użytkownicy tworzą wykresy Google, wypełniając tabelę danych i rysując przy użyciu tych danych. Jeśli zamiast tego chcesz pobrać dane z arkusza kalkulacyjnego Google, możesz wykonać zapytanie w arkuszu kalkulacyjnym, aby pobrać dane z wykresu:

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);
}

Funkcja ta jest dostępna, ponieważ Arkusze Google obsługują język zapytań umożliwiający sortowanie i filtrowanie danych w Mapach Google. Jako źródła danych można użyć dowolnego systemu obsługującego język zapytania.

Pamiętaj, że wykresy nie mogą wykorzystywać uprawnień osoby wyświetlającej je bez wyraźnego upoważnienia. Arkusz kalkulacyjny musi być widoczny dla wszystkich lub strona musi wyraźnie uzyskiwać dane logowania użytkowników w sposób opisany w sekcji Autoryzacja na tej stronie.

Aby użyć arkusza kalkulacyjnego Google jako źródła danych, potrzebujesz jego adresu URL:

  1. Otwórz istniejący arkusz kalkulacyjny. Powinien on mieć format oczekiwany przez wizualizację i mieć odpowiednie uprawnienia do wyświetlania. Najłatwiej będzie wyświetlić uprawnienia typu „Publiczny w internecie” lub „Każda osoba mająca link”, a zgodnie z instrukcjami w tej sekcji przyjmie się, że arkusz kalkulacyjny został skonfigurowany w ten sposób. Możesz ograniczyć dostęp do arkusza, pozostawiając go „Prywatny” i udzielając dostępu do poszczególnych kont Google, ale konieczne będzie stosowanie się do instrukcji autoryzacji poniżej.
  2. Skopiuj adres URL z przeglądarki. Szczegółowe informacje o wyborze określonych zakresów znajdziesz w sekcji Zakresy źródłowych zapytań.
  3. Podaj adres URL google.visualization.Query(). Zapytanie obsługuje te parametry opcjonalne:
    • headers=N: określa liczbę wierszy stanowiących nagłówki, gdzie N to liczba całkowita równa zero lub większa. Zostaną one wykluczone z danych i przypisane jako etykiety kolumn w tabeli danych. Jeśli nie określisz tego parametru, arkusz kalkulacyjny zgadnie, ile wierszy jest wierszy nagłówka. Pamiętaj, że jeśli wszystkie kolumny zawierają dane w postaci ciągów, arkusz kalkulacyjny może mieć problemy z ustaleniem, które wiersze są wierszami nagłówka bez tego parametru.
    • gid=N: określa, który arkusz w dokumencie zawierającym wiele arkuszy ma być linkiem do pierwszego arkusza, jeśli nie łączy się on z pierwszym arkuszem. N to numer identyfikacyjny arkusza. Numer identyfikacyjny identyfikatora możesz sprawdzić, przechodząc do opublikowanej wersji tego arkusza i szukając parametru gid=N w adresie URL. Zamiast tego możesz też użyć parametru sheet. Gotcha: Arkusze kalkulacyjne Google mogą zmieniać kolejność parametrów gid w adresie URL w przeglądarce. Jeśli kopiujesz parametry z przeglądarki, upewnij się, że wszystkie parametry znajdują się przed znakiem # adresu URL. Przykład: gid=1545912003.
    • sheet=sheet_name: określa, który arkusz w dokumencie zawierającym wiele arkuszy ma być połączony, jeśli nie łączysz się z pierwszym arkuszem. Nazwa_arkusza to wyświetlana nazwa arkusza. Przykład: sheet=Sheet5.

Oto przykład:

Poniżej znajdziesz 2 sposoby rysowania tego wykresu: jeden za pomocą parametru gid i drugi za pomocą parametru sheet. Wpisanie dowolnego adresu URL w przeglądarce da taki sam wynik/dane dla wykresu.

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 });
    }
Arkusz
    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 });
    }

Zakresy źródeł zapytań

Źródłowy adres URL zapytania określa, której części arkusza kalkulacyjnego chcesz użyć w zapytaniu: konkretnej komórki, zakresu komórek, wierszy i kolumn albo całego arkusza kalkulacyjnego. Określ zakres za pomocą składni „zakres=<zakres_wydawcy>”, na przykład:

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

Oto kilka przykładów, które pokazują składnię:

  • A1:B10 – zakres od komórki A1 do B10
  • 5:7 – wiersze 5–7
  • D:F – kolumny D-F
  • A:A70 – pierwsze 70 komórek w kolumnie A,
  • A70:A – kolumna A od wiersza 70 do końca
  • B5:5–B5 do końca wiersza 5
  • D3:D – D3 do końca kolumny D
  • C:C10 – od początku kolumny C do C10

Autoryzacja

Arkusze Google wymagają danych logowania użytkowników, aby uzyskać dostęp do prywatnych arkuszy za pomocą interfejsu Google Visualization API („żądania / tq”).

Uwaga: arkusze kalkulacyjne udostępnione „każdej osobie mającej link mogą wyświetlać” nie wymagają danych logowania. Zmiana ustawień udostępniania arkusza kalkulacyjnego jest znacznie łatwiejsza niż implementacja autoryzacji.

Jeśli udostępnianie linków nie jest dobrym rozwiązaniem, deweloperzy będą musieli zmienić swój kod, aby przekazywać dane uwierzytelniające OAuth 2.0 do interfejsu API Arkuszy Google (https://www.googleapis.com/auth/sheets).

Więcej informacji o protokole OAuth 2.0 znajdziesz w artykule Korzystanie z OAuth 2.0 do korzystania z interfejsów API Google.

Przykład: używanie protokołu OAuth do uzyskiwania dostępu do /gviz/tq

Warunek wstępny: uzyskanie identyfikatora klienta z Google Developer Console

Szczegółowe instrukcje integracji z usługą Identity Platform Google znajdziesz w artykułach o logowaniu w Google oraz tworzeniu projektu i identyfikatora klienta w Konsoli interfejsów API Google.

Aby uzyskać tokeny OAuth dla użytkowników, musisz najpierw zarejestrować projekt w Google Developer Console i uzyskać identyfikator klienta.

  1. W konsoli programisty utwórz nowy identyfikator klienta OAuth.
  2. Jako typ aplikacji wybierz Aplikacja internetowa.
  3. Wybierz dowolną nazwę; jest ona przeznaczona tylko dla Ciebie.
  4. Dodaj nazwę swojej domeny (i wszelkich domen testowych) jako Autoryzowane źródła JavaScript.
  5. Pole Autoryzowane identyfikatory URI przekierowania pozostaw puste.

Po kliknięciu „Utwórz” skopiuj identyfikator klienta, aby skorzystać z niego w przyszłości. Tajny klucz klienta nie jest potrzebny.

Zaktualizuj witrynę, aby uzyskać dane logowania OAuth.

Google udostępnia bibliotekę gapi.auth, która znacznie ułatwia pozyskiwanie danych uwierzytelniających OAuth. Przykładowy kod poniżej wykorzystuje tę bibliotekę do uzyskania danych logowania (w razie potrzeby prosząc o autoryzację) i przekazuje uzyskane dane logowania do punktu końcowego /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));
}

Po zakończeniu autoryzacji gapi.auth.getToken() zwróci wszystkie szczegóły danych logowania, w tym access_token, które można dołączyć do żądań /gviz/tq.

Więcej informacji o używaniu biblioteki gapi do uwierzytelniania:

Korzystanie z zakresu drive.file

W poprzednim przykładzie użyto zakresu interfejsu API Arkuszy Google, który przyznaje uprawnienia do zapisu i odczytu całej zawartości arkusza kalkulacyjnego użytkownika. W zależności od aplikacji może to być mniej restrykcyjne. Aby uzyskać dostęp tylko do odczytu, użyj zakresu sheets.readonly, który zapewnia dostęp tylko do odczytu do arkuszy i ich właściwości użytkownika.

Zakres drive.file (https://www.googleapis.com/auth/drive.file) zapewnia dostęp tylko do tych plików, które użytkownik wyraźnie otworzy przy użyciu selektora plików na Dysku Google utworzonego przy użyciu interfejsu Picker API.

Użycie selektora zmienia przepływ aplikacji. Zamiast wklejać URL lub zakodować na stałe arkusz kalkulacyjny jak w przykładzie powyżej, użytkownik musi wybrać okno wyboru, aby wybrać arkusz, do którego ma mieć dostęp Twoja strona. Kieruj się przykładem wyboru „Hello World”, używając google.picker.ViewId.SPREADSHEETS zamiast google.picker.ViewId.PHOTOS.