Como usar o Google Sheets como um banco de dados (responsavelmente)

embora a tecnologia de banco de dados e outras ferramentas tenham percorrido um longo caminho, ainda é difícil superar a versatilidade e a intuitividade da humilde planilha. Ao usá-los para aplicações sensíveis e críticas como armazenar dados de pacientes COVID-19 é imprudente, o fato de que todos sabem como usar uma planilha significa que eles são grandes para projetos horizontais menores, onde um não-desenvolvedor pode precisar de examinar ou editar dados.

neste guia, vou mostrar-lhe como usar o Google Sheets como um banco de dados, completo com uma interface API acessível através de HTTP. Usaremos o código automático, Um Nó.plataforma de desenvolvimento da API js e editor com autocomplete incorporado, para implantar um aplicativo simples e lidar com o processo de autenticação do Google. Também vou explorar as limitações das folhas do Google, incluindo escalabilidade, e onde faz sentido começar a olhar para alternativas mais complexas.

# 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'

Você não precisa de nada além do que uma conta do Google e um Autocode conta para começar. Vamos!

Em primeiro lugar, terá de clonar a sua própria cópia do Modelo Da Folha do Google clicando aqui, Carregando depois em usar o modelo no canto superior direito. Isto irá adicionar a planilha de exemplo à sua conta pessoal do Google.

Depois de ter feito isso, carregue aqui para abrir a aplicação inicial em código automático. Procurar em torno do código fonte, se você quiser, em seguida, instalar o aplicativo para a sua conta de Autocode, pressionando o botão verde. Quando solicitado a ligar uma folha do Google, siga as instruções no fluxo para ligar a sua conta Google, em seguida, selecione a planilha que você acabou de clonar.

depois disso, o seu aplicativo deve estar pronto para ir! Tente aceder a alguns dos pontos finais através dos seus URLs e veja o que é devolvido/o que acontece à sua nova base de dados de folhas de cálculo. Você pode verificar a seção Endpoints abaixo, por exemplo chamadas.

limitações

que tl;dr foi fácil, certo? Então, porque é que nem tudo é construído no Google Sheets?

enquanto uma infra-estrutura que leva 30 segundos para configurar e que todos podem interagir é extremamente atraente, existem algumas limitações óbvias. Uma planilha como um banco de dados, como descrito acima, não suporta naturalmente múltiplas tabelas ou relações entre linhas. Também não há nenhum conceito de fazer valer tipos para uma determinada coluna, transações, backups incorporados, ou criptografia, de modo que dados sensíveis/críticos (como os dados COVID-19 pacientes) é provavelmente melhor armazenado em outro lugar.

em termos de escalabilidade, o Google Sheets tem um limite rígido de 5.000.000 células (incluindo células em branco). Quando eu tentei verificar isso, criando uma planilha com que muitos valores, no entanto, eu encontrei problemas significativos no desempenho antes desse limiar:

Mass-operations como colar um grande número de células abrandadas, então começou a falhar em cerca de 1m células. A navegação era geralmente lenta.

minhas experiências em torno de fazer chamadas API renderam resultados semelhantes. A velocidade da pesquisa pareceu escalar linearmente com o número de células:

consultas tornaram-se impraticavelmente lentas em torno da marca de 500.000 células, mas ainda estavam abaixo de 2 segundos para uma consulta de 100.000 células. Portanto, se você antecipar um conjunto de dados maior que algumas centenas de milhares de células, provavelmente seria inteligente escolher uma opção mais escalável.

como funciona

quando liga a sua folha de Google clonada à sua aplicação e a instala na sua conta, o Autocode lida automaticamente com a autenticação entre a sua aplicação e a sua conta Google utilizando o token da sua aplicação (ver a linha const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN}) no topo de todos os endpoints).

para as consultas reais, cada endpoint contém nó.código js que chama método aos googlesheets.consulta a API. Estas APIs têm um parâmetro chamado range formatado em notação A1 que corresponde à parte da folha de cálculo que a chamada API deve considerar como parte da base de dados.

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

Uma range valor A:E é, essencialmente, uma abreviação para “usar todas as linhas em colunas de A até E na folha de cálculo como o meu banco de dados”. A consulta interpreta a primeira linha de cada coluna nesse intervalo como o nome do campo dos valores nessa coluna. Dado o modelo que clonou, a consulta acima irá verificar todos os valores na coluna A (chamado Name) para linhas com um valor correspondente à consulta.

estas chamadas de API usam a linguagem de consulta KeyQL. Se você está interessado em um mergulho profundo, você pode conferir para mais exemplos.

Chamando pontos de extremidade

Como mencionado anteriormente, estes parâmetros são acessíveis através de HTTP, assim você pode fazer chamadas para eles através de fetchcURL, ou qualquer outro cliente HTTP que você preferir. Você pode usar o seu navegador web diretamente:

e você pode até usar o mesmo pacote de nó lib-node que os endpoints usam para chamar as API do Google Sheets:

seus endpoints responderão a pedidos de GET ou POST. Os parâmetros são analisados a partir do querystring para pedidos de obtenção e do corpo de solicitação para pedidos de POST. Cada endpoint tem parâmetros padrão definidos por uma questão de clareza. Você pode encontrar exemplos para cada endpoint abaixo.

Endpoints

functions/select/job / contains.js

este endpoint é um exemplo de umcontains pesquisa KeyQL. Ele procura por linhas na folha de Google ligada onde o campo Job contém um sub-texto (sensível à capitalização) correspondente ao parâmetro. A partir do exemplo de folha, ele retorna:

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

funções/select/born_on/date_gt.js

este endpoint é um exemplo de umdate_gt keyql query. Ele procura por linhas na folha do Google vinculada onde o parâmetro Born On campo é após o parâmetro, formatado como2000/01/01. Da folha de amostras, retorna:

$ 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'