Arkusz kalkulacyjny to CMS? - Google sheets jako JSON api

Za pomocą Google Apps Script i odrobiny Java Scriptu można bardzo ułatwić sobie życie. Jedną z takich rzeczy jest przepisywanie informacji z arkusza kalkulacyjnego na stronę internetową. Ludzie “nietechniczni” dostarczają nam tam-w-tabelce-masz-wszystko.xlsx, które to potem musi się znaleźć w odpowiednim miejscu. Pomińmy na moment fakt, że umieszczanie i przenoszenie danych w plikach excela potrafi narobić sporo problemów i należy tego unikać. Jeżeli to 5 linijek damy radę, ale w momencie gdy robi się tego kilkadziesiąt linijek po kilka-kilkanaście komórek zaczyna boleć głowa, gdy trzeba będzie jeszcze wprowadzać ciągłe poprawki. Rozwiązaniem takich problemów jest CMS i w większości przypadków mamy z nim do czynienia. Jednak jeżeli dostaliśmy dane w Excelu to nasz CMS nie został przewidziany do tego zadania. Możemy zatem stworzyć jakąś mikro aplikację, która na zapytanie GET odpowie nam jakimś zrozumiałym dla przeglądatki obiektem(np. JSON), który potem możemy w przeglądarce przerobić na HTML i wyświetlić użytkownikowi. Taka aplikacja potrzebowałaby bazy danych, serwera, trzeba zrobić panel, zadbać o jego bezpieczeństwo i mnóstwo innych rzeczy, o których musielibyśmy dbać tylko dlatego, ponieważ nie chce się nam poprawić tabelek w htmlu. Więcej zajęłoby szkolenie z obsługi naszej cudownej aplikacji niż ręczne ustawianie znaczników.

Jeżeli połączymy ze sobą ludzką skłonność do umieszczania danych w arkuszach kalkulacyjnych z naszym lenistwem dodając do tego Google Apps Script możemy szybko stworzyć maleńki CMS, który będzie:

darmowy
jego zbudowanie zajmie nam max 15 minut
nie musimy się martwić o hosting, konfigurację, nie musimy wiedzieć nic o serwerach
będzie miał przyjazny interface (wszyscy kochają tabelki)
każda zmiana danych w tabelce automatycznie pojawi się na stronie
zarządzanie użytkownikami zrobi za nas Google (a utworzenie konta jest proste)
Dodatkowo, odkąd Google Apps Script jest uruchamiane na silniku V8 możemy kożystać ze wszystkich udogodnień najnowszych wersji ECMAScript :)

Tworzymy arkusz i aplikację

Tworzymy nowy arkusz Google Sheets (np. wpisując w przeglądarkę sheet.new) i wklejamy tam jakieś dane testowe. Mogą być np. popularne dane nt. pasażerów Titanica. Następnie wybieramy “Narzędzia” i “Edytor skryptów” - w tym edytorze umieścimy kod JavaScript.

Zaczniemy od pobierania danych z arkusza kalkulacyjnego. Stworzymy funkcję getSheetData, która zwróci dane, które wkleiliśmy do tego arkusza. Do interakcji z Arkuszami Google za pomocą skryptów służy obiekt SpreadsheetApp, o którym szerzej znajdziemy w dokumentacji. Jeżeli chcemy uzyskać pierwszy arkusz, cały jego zakres danych oraz interesują nas tylko wartości z tego zakresu to nasza funkcja będzie wyglądała tak:

1
2
3


function getSheetData() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getDataRange().getValues()
}

Aby zobaczyć jak wyglądają teraz te dane wpiszemy je w konsoli. W Google Script za konsole odpowiada obiekt Logger. Stwórzmy więc funkcję do testowania

1
2
3


function test(){
  Logger.log(getSheetData());
}

Następnie możemy zapisać nasz projekt, z wybieraka wybrać funkcję test uruchomić i w Dzienniku (Widok -> Dzienniki) i zobaczyć co “wypluła” nasza funkcja.

Jest to tablica, która zawiera tablice - wiersze z naszego arkusza. Jeżeli chcemy aby nasze wiersze były obiektami klucz-wartość musimy je przerobić. Do tego stworzymy funkcję makeArray.Nie wdając się w szczegóły funkcja weźmie pierwszy obiekt z obiektu data jako klucze, następnie połączy je z wartościami wszystkich następnych tablic. i zwróci jako tablicę:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


function makeArray(data) {
  const header = data[0]
 
  let arr = []
  for(let i = 0; i < data.length; i++) {
    if (i===0){
      // nic ;P
    } else {
      let obj = {};
      header.forEach((key, x) => obj[key] = data[i][x])
      arr.push(obj);
    }
  }
  return arr;
}

nasz nowy test Logger.log(makeArray(getSheetData())); pokaże w konsoli coś takiego:

Teraz możemy zadbać o to co zrobi aplikacja po otrzymaniu zapytania GET. W tym celu musimy utworzyć funkcję doGet(). Wartość, którą zwraca ta funkcja będzie tym co otrzyma przeglądarka po wysłaniu zapytania pod adresem naszej aplikacji. W dokumentacji znajdziemy interesujący nas przykład z odpoweidzą JSON. W wersji uproszczonej będzie wyglądała tak:

1
2
3
4
5


function doGet() {
  const data = getSheetData();
  const content = makeArray(data);
  return ContentService.createTextOutput(JSON.stringify(content)).setMimeType(ContentService.MimeType.JSON);
}

Ostatnim zadaniem będzie opublikowanie naszej aplikacji i nadanie jej uprawnień do odczytywania zawartości Arkuszy Google. Klikami Opublikuj -> Wdróż jako aplikację internetową i określamy kto i jako kto może wykonywać naszą aplikację.

Chcemy aby naszą aplikację mógł uruchomić każdy, nawet niezalogowany do konta Google użytkownik oraz aby aplikacja została uruchomiona jako Me ponieważ tylko nasze konto uprawnienia odczytywania i zapisywania danych w tym arkuszu. Wyskoczy nam wyskakujące okienko ostrzegające nas o niebezpieczeństwie wynikającym ze złośliwych skryptów. Ten napisaliśmy my, więc chyba takim nie jest. Zgadzamy się.

Nadajemy uprawnienia aplikacji do naszego konta Google

I otrzymujemy link, pod którym nasza aplikacja jest dostępna dla świata.

Przykład wykorzystania naszego cms’a:

Wady

Jak wszystko co fajne i darmowe ma to swoje wady. Przede wszystkim czas odpowiedzi wynoszący ponad sekundę. Drugą przeszkodą są limity, które nie są jakoś specjalnie restrykcyjne, ale są. Obu przeciwnościom można łatwo zapobiec np pobierając zawartość pliku na nasz hosting za pomocą kilku linijek PHP.

Wszystko byleby tylko nie przepisywać tabelek ręcznie, a jeżeli mamy tylko dodać ostylowaną listę linków na końcu landing page (np. taką jak na tej stronie), która będzie miała mniej niż kilka tysięcy odsłon dziennie, to dlaczego nie spróbować? :)

Podsumowanie

Jest to zdecydowanie ciekawostka, ale potrafi bardzo ułatwić pracę, w której mamy po zaprezentowania dużo danych, które wymagają wielu aktualizacji czy po prostu do szybkiego prototypowania. Nie musimy od razu rzucać się w wir serwerów jeżeli tylko chcemy wyświetlić niezbyt skomplikowaną listą. Aplikację możemy również rozbudować o parametry, zapytania POST czy bardziej rozbudowanie filtrowanie. Możliwości jest naprawdę sporo. Ten przykład pokazuje też jak wiele możliwości ma do zaoferowania Google Apps Script.

Kompletny kod:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


function getSheetData() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getDataRange().getValues()
}

function makeArray(data) {
  const header = data[0]
 
  let arr = []
  for(let i = 0; i < data.length; i++) {
    if (i===0){
      // nic ;P
    } else {
      let obj = {};
      header.forEach((key, x) => obj[key] = data[i][x])
      arr.push(obj);
    }
  }
  return arr;
}

function doGet() {
  const data = getSheetData();
  const content = makeArray(data);
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

function test(){
  Logger.log(makeArray(getSheetData()));
}