More

    Czy Google Sheets mogą być źródłem danych MongoDB?

    Jak najbardziej, jeszcze jak! Zanim pomyślisz, że jest to poroniony pomysł, daj mi chwilę, bo dane bazodanowe przechowywane w Google Sheets mogą być bardzo przydatne. I nie, nie będę argumentował tego stwierdzeniem, „bo można”. Choć w przypadku wielu rozwiązań IT jest to wystarczające.

    Wracając do brzegu, wyobraź sobie sytuację, w której pracujesz z osobami niebędącymi „za pan brat” z SQL-em (nie powinno być z tym problemu). Korzystanie wówczas z arkuszy kalkulacyjnych dla tych osób jest znacznie wygodniejsze i generuje mniejszą liczbę błędów.

    Dla naszych dywagacji przyjmijmy sobie prosty schemat danych z listą gości weselnych. Informacje na temat gości potwierdzających swoją obecność będą potrzebne w systemie sali weselnej. Nie mamy jednak zamiaru udostępniać parze weselnej dostępu do systemu, bo znacznie łatwiej udostępnić arkusz Google Sheets. W tym artykule, który został zainspirowany wpisem na blogu MongoDB odnośnie funkcjonalności Stitch pokażę jak sobie z tym poradzić. A więc zaczynamy!

    Założenia projektu

    Dla potrzeb projektu stworzymy w Google Sheets arkusz ze schematem danych oraz logikę komunikacji z bazą danych stworzoną w MongoDB za pomocą Edytora Skryptów dostępnych w Narzędziach Google Sheets. Po stronie MongoDB komunikacja będzie występowała poprzez Webhooks dostępne w MongoDB Realms. Nie martw się, bo wszystkie rozwiązania są dostępne za darmo.

    Stworzenie schematu w Google Sheets

    W pierwszym kroku stworzymy arkusz kalkulacyjny, który będzie inicjalnym źródłem danych w naszej bazie. Link do przykładowego arkusza.

    Podgląd arkusza
    Podgląd arkusza

    Na początku mapujemy kolumny dostępne w arkuszu. W tym celu wchodzimy do Narzędzia/Tools → Edytor Skryptów/Script Editor.

    var columns = {
      first_name: 0,
      last_name: 1,
      has_partner: 2,
      answered: 3,
      accepted: 4,
      updated_at: 5,
      invite_id: 6
    }

    Następnie stworzymy funkcję do eksportu danych z arkusza do bazy danych. Wysyłać będziemy tylko dane z wierszy, które mają uzupełnione kolumny Odpowiedź oraz Data aktualizacji. Zaktualizowane dane będą uzupełniane o ID, które zostanie zwrócone przez webhook.

    /****
     * Eksportowanie zmodyfikowanych elementów do MongoDB poprzez MongoDB Stitch
     ****/
    function exportToMongoDB() {
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Ładowanie arkusza kalkulacyjnego
      var sheet = spreadsheet.getActiveSheet(); // Wybieranie aktywnego arkusza
      var headerRows = 1;  // Liczba wierszy nagłówka
      var range = sheet.getDataRange(); // Zakres danych
      var numRows = range.getNumRows(); // Liczba wierszy w zakresie danych
      var data = range.getValues(); // Dane w formie tablicy = data[wiersz][kolumna]
      
      for (var i=headerRows; i<numRows; i++) {
        var inviteIdCell = range.getCell(i+1, columns.invite_id+1);
        var answered = data[i][columns.answered].toString();
        var updatedAtCell = range.getCell(i+1, columns.updated_at+1);
        var date = Utilities.formatDate(new Date(data[i][columns.updated_at]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");   
        // Stwórz zapytanie POST jako formularz 
        var formData = {
          'first_name': data[i][columns.first_name],
          'last_name': data[i][columns.last_name],
          'updated_at': date,
          'has_partner': data[i][columns.has_partner],
          'answered': data[i][columns.answered],
          'accepted': data[i][columns.accepted],
          'invite_id': data[i][columns.invite_id]
        };
        var options = {
          'method' : 'post',
          'payload' : formData
        };
        if (answered == 'true' && date.toString() != '1970-01-01T00:00:00Z') {
          var insertID = UrlFetchApp.fetch('WPROWADŹ TUTAJ WEBHOOK URL DLA EKSPORTU', options);
          inviteIdCell.setValue(insertID); // Wprowadź nowe ID dostarczone przez MongoDB
          updatedAtCell.setValue('');
        }
      }
    }

    Chcemy, aby eksportowane były tylko elementy, które zostały zmodyfikowane przez użytkownika arkusza. W tym wypadku skorzystamy z dwóch podstawowych zapalników (z ang. triggers) onOpen oraz onEdit

    Podczas uruchamiania arkusza będziemy czyścić wartości dla kolumny Data aktualizacji.

    function onOpen(e){
     var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = spreadsheet.getActiveSheet();
     var updatedColumn = sheet.getDataRange().getCell(1, columns.updated_at+1);
      if (updatedColumn.getValue() == "Data aktualizacji") {
        var headerRows = 1;
        var range = sheet.getDataRange();
        var numRows = range.getNumRows();
        var data = range.getValues();
        for (var i=headerRows; i<numRows; i++) {
          var updatedCell = range.getCell(i+1, columns.updated_at+1);
          updatedCell.setValue('');
        }
      }
    }

    W momencie edycji wiersza automatycznie będzie uzupełniana wartość Daty aktualizacji dla modyfikowanego wiersza.

    function onEdit(e){
      var range = e.range;
      var rowIndex = range.getRowIndex();
      var rowUpdatedCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getCell(rowIndex, columns.updated_at+1);
      rowUpdatedCell.setValue(new Date());
    }
    

    Jeśli chcemy usunąć wszystkie wiersze z MongoDB, będziemy potrzebować jeszcze jednej funkcji. Dane zostaną w arkuszu, jednak ID zmieni się na tekst “Usunięte z bazy”.

    
    /****
     * Usuwanie wszystkich elementów z MongoDB
     ****/
    function removeFromMongoDB() {
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = spreadsheet.getActiveSheet();
      var headerRows = 1;
      var range = sheet.getDataRange();
      var numRows = range.getNumRows();
      var data = range.getValues();
      
      for (var i=headerRows; i<numRows; i++) {
        var inviteIdCell = range.getCell(i+1, columns.invite_id+1);
        var updatedAtCell = range.getCell(i+1, columns.updated_at+1);
        var date = Utilities.formatDate(new Date(data[i][columns.updated_at]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");   
        var formData = {
          'first_name': data[i][columns.first_name],
          'last_name': data[i][columns.last_name],
          'updated_at': date,
          'has_partner': data[i][columns.has_partner],
          'answered': data[i][columns.answered],
          'accepted': data[i][columns.accepted],
          'invite_id': data[i][columns.invite_id]
        };
        var options = {
          'method' : 'post',
          'payload' : formData
        };
        var insertID = UrlFetchApp.fetch('WPROWADŹ TUTAJ WEBHOOK URL DLA USUWANIA', options);
          inviteIdCell.setValue(insertID); // Wprowadź nowe ID dostarczone przez MongoDB
      }
    }

    Tyle wystarczy nam na początek. Pewnie zostało jednak przez Ciebie dostrzeżone, że metoda  UrlFetchApp.fetch() nie miała wypełnionego adresu. Teraz przejdziemy do konfiguracji bazy danych po stronie MongoDB.

    Konfiguracja klastra w MongoDB Atlas

    Rozpoczniemy od stworzenia konta MongoDB Atlas, które będzie nam potrzebne do wygenerowania linku do aktualizacji danych z poziomu naszego arkusza Google Sheets. Wejdź na adres: https://www.mongodb.com/cloud/atlas/signup i stwórz darmowe konto. Następnie będziemy musieli stworzyć grupę (z ang. cluster), aby uruchomić bazę MongoDB w chmurze. Wystarczy nam darmowy plan zwany jako Shared Clusters.

    Podgląd MongoDB Atlas
    Podgląd MongoDB Atlas

    Moja przykładowa konfiguracja grupy:

    Konfiguracja grupy MongoDB Atlas
    Konfiguracja grupy MongoDB Atlas

    Super, mamy już zrobioną bazę danych. Jednak, by komunikować się z nią za pomocą webhooks, musimy stworzyć aplikację w zakładce Realms.

    Konfiguracja platformy MongoDB Realm
    Konfiguracja platformy MongoDB Realm

    Aplikację należy połączyć z wcześniej skonfigurowaną grupą. W moim przypadku będzie to GrupaGoogleSheets.

    Tworzenie aplikacji dla naszych funkcji w MongoDB Realm
    Tworzenie aplikacji dla naszych funkcji w MongoDB Realm

    Stworzyliśmy aplikację Realm, teraz potrzebujemy serwisu typu HTTP, w którym napiszemy nasze webhooki do komunikacji z Google Sheets.

    Tworzenie serwisu do komunikacji HTTP z zewnętrznymi serwisami (w naszym przypadku Google Sheets)
    Tworzenie serwisu do komunikacji HTTP z zewnętrznymi serwisami (w naszym przypadku Google Sheets)

    Potrzebujemy do tego dwóch funkcji. Jedna do obsługi dodawania elementów do kolekcji, a druga do obsługi usuwania elementów. Wprowadźmy funkcje w Function Editor.

    Dla stworzenia elementu:

    exports = async function(payload) {
        const mongodb = context.services.get("mongodb-atlas");
        const invitesdb = mongodb.db("GrupaGoogleSheets"); // Nazwa naszej bazy
        const invitescollection = invitesdb.collection("invites");
        const result= await invitescollection.insertOne(payload.query);
        var id = result.insertedId.toString();
        if(result) {
            return JSON.stringify(id,false,false);  
         }
        return "Błąd podczas zapisu";
    }

    Dla usunięcia elementu:

    exports = async function(payload) {
        const mongodb = context.services.get("mongodb-atlas");
        const invitesdb = mongodb.db("GrupaGoogleSheets");
        const invitescollection = invitesdb.collection("invites");
        const delresult = await invitescollection.deleteOne({first_name:payload.query.first_name, last_name: payload.query.last_name});
        return "Usunięte z bazy";
    };

    Przejdźmy do ustawień funkcji i skopiujmy Webhook URL. Jest to dokładnie to, co będziemy musieli wkleić w skrypcie Google Sheets.

    Ustawienia funkcji usuwania elementów kolekcji. Webhook URL do skopiowania.
    Ustawienia funkcji usuwania elementów kolekcji. Webhook URL do skopiowania.

    To już cała konieczna konfiguracja, więc wracamy do naszego arkusza.

    Menu w Google Sheets do synchronizacji danych

    Do synchronizacji danych z MongoDB stworzymy własne menu, w którym będziemy wywoływać stworzone wcześniej w skrypcie funkcje. Aby to zrobić, musimy zmodyfikować kod funkcji onOpen dodając na końcu kod odpowiedzialny

    function onOpen(e){
     // ...
        var entries = [{
           name : "Synchronizuj dane",
           functionName : "exportToMongoDB"
         },{
           name: "Usuń wszystkie elementy",
           functionName: "removeFromMongoDB"
         }];
        SpreadsheetApp.getActiveSpreadsheet().addMenu("Komunikacja z MongoDB", entries);
      }
    }

    Odświeżmy okno arkusza i pojawi się nowe menu.

    Wygląd menu do synchronizacji w Google Sheets
    Wygląd menu do synchronizacji w Google Sheets

    Skrypt uzupełni pole ID i usunie wartość z kolumny Data aktualizacji. A tak powinien wyglądać poprawnie wykonany wynik zapytania:

    Podgląd arkusza po synchronizacji z MongoDB
    Podgląd arkusza po synchronizacji z MongoDB

    Podsumowanie

    Udało nam się zrobić dwie rzeczy: rozszerzać funkcjonalność arkusza Google Sheets oraz skonfigurować darmowy plan bazy MongoDB Atlas działającej w chmurze. To całkiem sporo, ale najważniejsze jest to, że można to zrobić bez większego wysiłku. W wypadku, gdy nagle potrzebujemy danych od osób nietechnicznych, to rozwiązanie może nas uratować. Wystarczy niewielka konfiguracja i mamy działające rozwiązanie w ciągu pół godziny. Tutaj dostępny jest skrypt użyty w Google Sheets. Mam nadzieję, że w jakimś stopniu przyda Ci się to rozwiązanie.

    Jeśli spróbujesz stworzyć własny arkusz, nie zapomnij opisać przydatnych skryptów w swoim dzienniczku, o którym mówiłem w artykule: Drogi Pamiętniczku, … czyli dziennik programisty w Gatsby.js.

    Do następnego!

    Mateusz Korczyński
    Fullstack Developer orbitujący głównie wokół Javy i Reacta. Posiada 5-letnie doświadczenie komercyjne od mikroprzedsiębiorstwa po korporacje. Jednak swoje miejsce na ziemi znalazł w software housie. Miesiąc bez poznania nowego frameworka jest dla niego miesiącem straconym, więc stara się ciągle trzymać rękę na pulsie.

    Latest articles

    11 bibliotek, które powinien znać każdy iOS developer

    Przez dwanaście lat, App Store mocno zadomowił się na naszych urządzeniach mobilnych, oferując coraz to większą różnorodność aplikacji. W tym roku ich...

    Jak używać Web Components w Reactcie i Angularze

    Kontynuujemy temat użycia Web Components w budowaniu aplikacji. W tym wpisie przyjrzymy się ich zastosowaniu wraz z popularnymi frameworkami JS-wymi.

    Hacktoberfest – ten hackathon to małe piwo

    Hackathon to wydarzenie programistyczne polegające na zrealizowaniu w określonym czasie projektu zgodnego założeniami organizatora. Większość z was pewnie kojarzy słowo Hackathon. Najczęściej...

    #IThotStory, czyli największe wpadki programistów

    Co prawda programista to nie saper i może pomylić się więcej niż raz, ale każda wpadka niesie za sobą jakieś ryzyko. Zaliczyliście...

    2 Comments

    Leave a reply

    Please enter your comment!
    Please enter your name here

    Related articles

    X