Come utilizzare Google Sheets come database (Responsabilmente)

Mentre la tecnologia di database e altri strumenti hanno percorso una lunga strada, è ancora difficile battere la versatilità e l’intuitività del foglio di calcolo umile. Mentre li utilizzano per applicazioni sensibili e critiche come la memorizzazione dei dati dei pazienti COVID-19 è sconsigliato, il fatto che tutti sappiano come utilizzare un foglio di calcolo significa che sono ottimi per progetti interfunzionali più piccoli in cui un non sviluppatore potrebbe aver bisogno di esaminare o modificare i dati.

In questa guida, ti mostrerò come utilizzare Google Sheets come database, completo di un’interfaccia API accessibile su HTTP. Useremo Autocode, un Nodo.piattaforma di sviluppo js API ed editor con completamento automatico integrato, per distribuire una semplice app e gestire il processo di autenticazione di Google. Esplorerò anche i limiti dei fogli Google, inclusa la scalabilità, e dove ha senso iniziare a guardare alternative più complesse.

# Returns all people in the database whose names start # with "bil", case-insensitive$ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'

non Hai bisogno di niente altro che un account Google gratuito e Autocode account per iniziare. Andiamo!

In primo luogo, è necessario clonare la propria copia del modello di foglio di Google cliccando qui, quindi premendo Usa modello in alto a destra. Questo aggiungerà il foglio di calcolo di esempio al tuo account Google personale.

Una volta fatto ciò, fai clic qui per aprire l’app starter in Autocode. Poke intorno al codice sorgente, se vuoi, quindi installare l’applicazione per il tuo account Autocode premendo il pulsante verde. Quando viene richiesto di collegare un foglio Google, seguire le istruzioni nel flusso per collegare il tuo account Google, quindi selezionare il foglio di calcolo appena clonato.

Dopo di che, la vostra applicazione dovrebbe essere pronto ad andare! Prova ad accedere ad alcuni degli endpoint tramite i loro URL e vedi cosa viene restituito / cosa succede al tuo nuovo database di fogli di calcolo. È possibile controllare la sezioneEndpoints sotto per esempio le chiamate.

Limitazioni

Che tl; dr era facile, giusto? Allora perché non è tutto costruito su fogli Google?

Mentre un backend che richiede 30 secondi per essere configurato e con cui tutti possono interagire è estremamente attraente, ci sono alcune ovvie limitazioni. Un foglio di calcolo come database come descritto sopra non supporta naturalmente più tabelle o relazioni tra righe. Non esiste inoltre alcun concetto di applicazione dei tipi per una determinata colonna, transazioni, backup incorporati o crittografia, quindi i dati sensibili/critici (come i dati del paziente COVID-19) sono probabilmente meglio archiviati altrove.

In termini di scalabilità, Google Sheets ha un limite rigido di 5.000.000 di celle (incluse le celle vuote). Quando ho provato a verificarlo creando un foglio di calcolo con così tanti valori, tuttavia, ho riscontrato problemi significativi nelle prestazioni prima di quella soglia:

Le operazioni di massa come incollare un gran numero di celle hanno rallentato, quindi hanno iniziato a fallire a circa 1m celle. La navigazione era generalmente lenta.

I miei esperimenti su come effettuare chiamate API hanno prodotto risultati simili. La velocità di query sembrava scalare linearmente con il numero di celle:

Le query sono diventate poco pratiche intorno al segno di 500.000 celle, ma erano ancora inferiori a 2 secondi per una query di 100.000 celle. Pertanto, se si prevede un set di dati più grande di poche centinaia di migliaia di celle, sarebbe probabilmente intelligente scegliere un’opzione più scalabile.

Come funziona

Quando colleghi il tuo foglio Google clonato alla tua app e lo installi sul tuo account, Autocode gestisce automaticamente l’autenticazione tra la tua app e il tuo account Google utilizzando il token della tua app (vedi la rigaconst lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN}) nella parte superiore di tutti gli endpoint).

Per le query effettive, ogni endpoint contiene il Nodo.codice js che chiama un metodo da googlesheets.api di query. Queste API prendono un parametro chiamato range formattato in notazione A1 che corrisponde alla parte del foglio di calcolo che la chiamata API dovrebbe considerare come parte del database.

let queryResult = await lib.googlesheets.query.select({ range: `A:E`, bounds: 'FULL_RANGE', where: });

A range il valore di A:E è essenzialmente una scorciatoia per “usa tutte le righe nelle colonne da A a E nel foglio di calcolo come mio database”. La query interpreta la prima riga di ogni colonna in quell’intervallo come il nome del campo dei valori in quella colonna. Dato il modello clonato, la query sopra controllerà tutti i valori nella colonna A (denominata Name) per le righe con un valore corrispondente alla query.

Queste chiamate API utilizzano il linguaggio di query KeyQL. Se sei interessato a un’immersione profonda, puoi verificarlo per ulteriori esempi.

Chiamare gli endpoint

Come accennato in precedenza, questi endpoint sono accessibili tramite HTTP, quindi è possibile effettuare chiamate a loro tramitefetchcURL, o qualsiasi altro client HTTP che si preferisce. È possibile utilizzare il browser web direttamente:

E puoi persino utilizzare lo stesso pacchetto di nodi lib-node utilizzato dagli endpoint per chiamare le API di Google Sheets:

I tuoi endpoint risponderanno alle richieste GET o POST. I parametri vengono analizzati dalla querystring per le richieste GET e dal corpo della richiesta per le richieste POST. Ogni endpoint ha parametri predefiniti impostati per motivi di chiarezza. Di seguito sono riportati esempi per ciascun endpoint.

Endpoint

funzioni/seleziona/lavoro / contiene.js

Questo endpoint è un esempio di una query KeyQLcontains. Cerca le righe nel foglio Google collegato in cui il campoJob contiene una sottostringa (sensibile alle maiuscole e minuscole) che corrisponde al parametroquery. Dal foglio di esempio, restituisce:

$ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/job/contains/?query=ist'

$ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/born_on/date_gt/?query=2000/01/01'

$ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'

$ curl --request POST \ --header "Content-Type: application/json" \ --data '{"name":"Bill Gates","job":"CEO","fictional":false,"bornOn":"10/28/1955"}' \ --url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/insert/'

$ curl --request POST \ --header "Content-Type: application/json" \ --data '{"name":"Bilbo Baggins","job":"Ring Bearer"}' \ --url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/update/'

$ curl --request GET --url \ 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/delete/?name=Bilbo%20Baggins'