完成工作表-使用Google Spreadsheets作为数据后端

by Gilad Dayagi

通过吉拉德·达亚吉

完成工作表-使用Google Spreadsheets作为数据后端 (Get Sheet Done — using Google Spreadsheets as your data backend)

If you want to rapidly prototype your next web apps, try using Google Spreadsheets as your data backend.

如果您想快速制作下一个Web应用程序的原型，请尝试使用Google Spreadsheets作为数据后端。

With a small library I created called get-sheet-done, you can have a free cloud database with GUI editor up and running in less than 5 minutes.

使用我创建的一个名为get-sheet-done的小型库，您可以在5分钟内启动并运行GUI编辑器的免费云数据库。

完成工作表背后的故事 (The story behind Get Sheet Done)

A while back, I needed to quickly prototype a web app that could display structured data. The catch was that these data had to be frequently edited by a non-technical person.

前一阵子，我需要快速制作一个可以显示结构化数据的Web应用程序原型。 值得注意的是，这些数据必须由非技术人员经常进行编辑。

Since this was a prototype, I was looking for a solution that would give me the most bang for the buck, when taking into account development time and maintenance costs.

由于这是一个原型，因此我在考虑开发时间和维护成本的情况下，一直在寻找一种能够最大程度地物有所值的解决方案。

I considered several solutions, including using a full backend-as-a-service approach that stored the data as a file in Dropbox. Then I chose a somewhat non-orthodox solution: I stored the data in a Google spreadsheet.

我考虑了几种解决方案，包括使用完整的后端即服务方法，将数据作为文件存储在Dropbox中。 然后，我选择了一种非传统的解决方案：我将数据存储在Google电子表格中。

基于电子表格的数据库什么时候是合适的解决方案？ (When is a spreadsheet-based database a suitable solution?)

Using Google spreadsheets as a database for web applications in not a mainstream solution, and it may or may not be suitable for your next project.

在主流解决方案中，使用Google电子表格作为Web应用程序的数据库不是主流解决方案，它可能适合也可能不适合您的下一个项目。

To help you decide whether this is a good option, I’ve put together the following list of considerations.

为了帮助您确定这是否是一个不错的选择，我整理了以下注意事项列表。

Remember: we’re talking about a spreadsheet, which works great for structured, tabular data. But doesn’t work well for a document / object store.

请记住：我们所谈论的是电子表格，该电子表格非常适合结构化的表格数据。 但是不适用于文档/对象存储。

Other than that, here are some pros and cons to consider:

除此之外，还有一些要考虑的利弊：

优点 (Pros)

• It’s free
  免费
• Very easy to set up — no need for API keys or complicated SDKs
  设置非常简单-无需API密钥或复杂的SDK
• Zero maintenance
  零维护
• You get a data editing GUI for free
  您免费获得数据编辑GUI
• You get write access management for free
  您免费获得写访问权限管理
• Can include internal calculations using spreadsheet functions
  可以包括使用电子表格功能的内部计算
• The app that uses the data can be easily upgraded in a later phase to use a real database, as data is exposed as standard JSON
  使用数据的应用程序可以在以后的阶段轻松升级以使用真实数据库，因为数据以标准JSON形式公开

• Some level of automation can be achieved by using apps-scripts in combination with time-driven triggers

  通过将应用程序脚本与时间驱动的触发器结合使用，可以实现某种程度的自动化

• It can be combined with Google Forms for data collection
  它可以与Google表单结合使用以收集数据

缺点 (Cons)

• No server-side filtering logic to talk about
  没有服务器端过滤逻辑可以谈论
• All the data you want to access has to be publicly accessible
  您要访问的所有数据必须可以公开访问

• The entire database is manually-editable, so a human error may break the application. For example, if someone accidentally changes the label of some field, it won’t be available for the application.

  整个数据库都是可手动编辑的，因此人为错误可能会破坏应用程序。 例如，如果有人不小心更改了某个字段的标签，则该标签将不适用于该应用程序。

  This can be partially remedied by protecting the critical cells

  通过保护关键细胞可以部分补救

• You can have up to 2 million cells in a spreadsheet

  电子表格中最多可以包含200万个单元格

我是如何实现的 (How I implemented this)

I couldn’t find a lot of information nor good libraries for easily reading data from a Google spreadsheet. So I decided to roll my own solution. It is now available on npm as the get-sheet-done package.

我找不到大量信息，也找不到很好的库来轻松地从Google电子表格中读取数据。 因此，我决定推出自己的解决方案。 现在可以在npm上将其作为get-sheet-done软件包使用 。

My implementation is based on the fact that once a spreadsheet is published to the web, it’s also available as a standard RSS feed, which can be fetched and parsed.

我的实现基于以下事实：将电子表格发布到网络后，它也可以作为标准的RSS提要使用，可以进行提取和解析。

One complication is you have to fetch it using JSONP or somehow handle CORS. I chose to go with JSONP and use the fetch-jsonp library to manage this, so there’s no need for special measures.

一种复杂的情况是您必须使用JSONP或以某种方式处理CORS来获取它。 我选择使用JSONP并使用fetch-jsonp库进行管理，因此无需采取特殊措施。

如何使用它 (How to use it)

Here’s what you need to do to get a simple editable database for your web app:

这是为Web应用程序获取简单的可编辑数据库所需的操作：

1. Create a google spreadsheet with some data
   创建包含一些数据的Google电子表格

2. Publish the sheet to the web: File -> Publish to the web.

   将工作表发布到Web： File -> Publish to the Web。

   Note the document ID from the URL

   记下URL中的文档ID

3. Install the package: npm install --save get-sheet-done

   安装软件包： npm install --save get-sheet-done

4. Get the data:
   获取数据：

import GetSheetDone from 'get-sheet-done'

GetSheetDone.labeledCols(DOCUMENT_ID).then(sheet => console.log(sheet))

5. Profit!

5.利润！

Note that there are three functions you can use to fetch the data, depending on how it should be parsed: raw 2d array, array of objects, object of objects.

请注意，根据应如何解析数据，可以使用三个函数来获取数据：原始2d数组，对象数组，对象对象。

Here’s a live demo you can play with.

这是您可以玩的现场演示 。

It’s worth considering using Google Spreadsheets as a data source for a web application, especially if you’re just building a quick prototype. It has some unique advantages, and implementation is easy with (or without) my library.

值得考虑使用Google Spreadsheets作为Web应用程序的数据源，尤其是在您仅构建快速原型的情况下。 它具有一些独特的优点，并且无论有没有我的库，实现起来都很容易。

Let me know in the comments if you found this library useful and whether there are missing features.

在评论中让我知道您是否发现此库有用，以及是否缺少功能。

翻译自: https://www.freecodecamp.org/news/get-sheet-done-using-google-spreadsheets-as-your-data-backend-650ba23dc6d9/