apps2sd使用_如何使用Google表格和Google Apps脚本构建自己的博客CMS

apps2sd使用

by Daniel Ireson

丹尼尔·埃里森(Daniel Ireson)

如何使用Google表格和Google Apps脚本构建自己的博客CMS (How to use Google Sheets and Google Apps Script to build your own blog CMS)

I recently stumbled across Google Apps Scripts, a platform that allows users to extend Google’s G Suite of online products through a scripting language derived from JavaScript. It’s analogous to VBA, which is built into most of Microsoft Office products.

最近，我偶然发现了Google Apps Scripts这个平台，该平台允许用户通过从JavaScript衍生的脚本语言来扩展Google的G Suite在线产品。 它类似于大多数Microsoft Office产品中内置的VBA 。

Google Apps Scripts is incredibly powerful and enables complex systems to be built on top of Google services. It can be a great choice when you need to quickly prototype an idea or design a solution that’s customizable by non-technical users. A great way to make an accessible solution is to build on top of products that users are already familiar with.

Google Apps脚本功能强大，可在Google服务之上构建复杂的系统。 当您需要快速构思想法原型或设计非技术用户可定制的解决方案时，这是一个不错的选择。 制作可访问的解决方案的一种好方法是在用户已经熟悉的产品之上构建。

In this article, I will walk through a simple yet novel example of building a “Content Management System” (CMS) for an online blog using Google Sheets, Google Forms, and Google Apps Script.

在本文中，我将介绍一个简单而新颖的示例，该示例使用Google表格，Google表单和Google Apps脚本为在线博客构建“内容管理系统”(CMS)。

The blog will be designed as a single page application with pagination and the ability to filter by post category. Blog posts will be stored in a Google Sheets spreadsheet. New posts will be added through Google Forms since it provides a user-friendly interface. Google Apps Script will be used to build an API to make the spreadsheet content available in an easy-to-use format.

该博客将被设计为具有分页和按帖子类别进行过滤的单页应用程序。 博客文章将存储在Google表格电子表格中。 新帖子将通过Google表单添加，因为它提供了易于使用的界面。 Google Apps脚本将用于构建API，以易于使用的格式提供电子表格内容。

免责声明 (Disclaimer)

I’m not using this in production, and I have no idea if it will scale. Think of it as a proof of concept to show what’s possible. You should do your own research if you want to use it in a production environment. I suspect that traffic will get throttled if you get near the upper limits of the service quotas. There’s a hard limit of 20,000 URL fetches per day on scripts for free Google accounts, and there may also be other limits in place.

我没有在生产中使用它，也不知道它是否可以扩展。 可以将其视为概念证明，以证明一切皆有可能。 如果要在生产环境中使用它，则应自己进行研究。 我怀疑如果您接近服务配额的上限，流量将受到限制。 对于免费的Google帐户，每天对脚本的网址抓取有20,000个硬限制，并且可能还有其他限制。

储存资料 (Storing the data)

Google Sheets will be used as a flat-file database to store the blog posts. A flat-file database stores data in plain-text in a single table. In contrast, a relational database captures relationships across tables and enforces the structure of those relationships to minimize duplication and maximize data integrity.

Google表格将用作平面文件数据库来存储博客文章。 平面文件数据库将纯文本数据存储在单个表中。 相反，关系数据库捕获跨表的关系并强制执行这些关系的结构，以最大程度地减少重复并最大化数据完整性。

Although more limited, a flat-file structure is easy to get started and is suitable for our use case of a small blog.

平面文件结构虽然受到更多限制，但易于入门，适合我们的小博客使用案例。

电子表格结构 (Spreadsheet structure)

Each row will represent a new blog post, and columns will be used to capture individual blog post fields. In a flat-file structure, there’s no concept of primary and foreign keys like in the relational model. Information that is captured in columns, such as category and author, will be duplicated across blog posts when common.

每行将代表一个新的博客文章，而各列将用于捕获各个博客文章字段。 在平面文件结构中，没有关系模型中的主键和外键的概念。 列中捕获的信息(例如类别和作者)在常见时会在整个博客文章中重复。

入门 (Getting started)

Create a new Google Sheets spreadsheet and connect this to Google Forms by going to Tools > Create a form in the menu bar. After selecting this option, you’ll be presented with an editor to define the form questions. These get mapped to spreadsheet columns.

创建一个新的Google表格电子表格，然后通过菜单栏中的工具>创建图片将其连接到Google表单。 选择此选项后，将为您提供编辑器以定义表单问题。 这些被映射到电子表格列。

For my demo, I added four questions for Title, Category, Author, and Content.

对于我的演示，我为Title ， Category ， Author和Content添加了四个问题。

Each field had a text type apart from Category, which was a radio type with four hypothetical categories: general, marketing, financial, technology.

除了Category之外，每个字段都有一个文本类型， Category是具有四个假设类别的无线电类型：常规，市场营销，财务，技术。

When a form submission is made, a row is appended to the Google Sheets spreadsheet. A Timestamp field is automatically added for each row, which we’ll use to calculate the post date.

提交表单后，会在Google表格电子表格中添加一行。 每行都会自动添加一个时间戳字段，我们将使用该字段来计算发布日期。

To allow for draft posts, I also added a boolean Published? field as the first column. The API should only return posts with a value of true. This allows posts to be reviewed and edited before they are published.

为了允许草稿发布，我还添加了布尔发布？ 字段作为第一列。 API应该只返回值为true的帖子。 这样可以在发布之前对其进行审阅和编辑。

构建API (Building the API)

Google Apps Script is built on top of the ECMAScript 5 (ES5) JavaScript standard. When building the API, we can’t use ES6 features like scoped variables, arrow functions, or default parameters. If you’re unsure of what’s available in ES5, I’d recommend consulting the MDN docs compatibility tables.

Google Apps脚本基于ECMAScript 5(ES5)JavaScript标准构建。 构建API时，我们不能使用范围变量，箭头函数或默认参数之类的ES6功能。 如果您不确定ES5中提供了哪些功能，建议您参考MDN文档兼容性表。

Despite the lack of ES6, Google Apps Scripts can still be used to build reasonably complex applications on top of G Suite products.

尽管缺少ES6，但Google Apps脚本仍可用于在G Suite产品之上构建相当复杂的应用程序。

入门 (Getting started)

You can access the Google Apps Script online editor by going to Tools > Script Editor in the menu bar from the Google Sheets spreadsheet. A script editor will open with an empty file named Code.gs. Since this is a simple application, we’ll put our logic in this one script, but you can also easily break up your application into separate scripts.

您可以转到“ 工具”>“脚本编辑”或在Google表格电子表格的菜单栏中访问Google Apps脚本在线编辑器。 脚本编辑器将打开，并带有一个名为Code .gs的空文件。 由于这是一个简单的应用程序，因此我们将逻辑放在该脚本中，但是您也可以轻松地将应用程序分解为单独的脚本。

返回回应 (Returning a response)

We can make use of doGet and doPost callback functions to respond to HTTP requests. These are just ordinary functions that Google Apps Script looks to invoke when a GET or POST request is respectively made to the API.

我们可以利用doGet和doPost回调函数来响应HTTP请求。 这些只是在分别向API发出GET或POST请求时Google Apps脚本希望调用的普通函数。

To generate a response, we’ll use the ContentService. A JavaScript object can be passed to JSON.stringify and then to createTextOutput on this service to build a JSON response. If the mime type is set to ContentService.MimeType.JSON this will appropriately set the content type to application/json.

为了生成响应，我们将使用ContentService 。 可以将JavaScript对象传递给JSON.stringify ，然后传递给此服务上的createTextOutput以构建JSON响应。 如果将mime类型设置为ContentService.MimeType.JSON则会将内容类型适当地设置为application/json 。

Generating a JSON response is as easy as the following:

生成JSON响应非常简单，如下所示：

function doGet(e) {  var output = JSON.stringify({    status: 'success',    message: 'It worked',  });    return ContentService.createTextOutput(output)    .setMimeType(ContentService.MimeType.JSON);}

解析请求 (Parsing requests)

The doGet callback is always invoked with an event generated from the request. From this event, we can access the query string parameters, which we’ll use to support various API options. Simple stateless authentication will be implemented through a key parameter. This will simply check that the key parameter value matches a hardcoded key value. Requests that don’t match will be shown an unauthorized response.

始终使用从请求生成的事件来调用doGet回调。 从此事件中，我们可以访问查询字符串参数，我们将使用该参数来支持各种API选项。 简单的无状态认证将通过key参数实现。 这将仅检查key参数值是否与硬编码键值匹配。 不匹配的请求将显示未经授权的响应。

A category parameter will be used so that users can request posts from a single category. This saves them from having to filter by category on the front-end. Pagination will also be implemented through a page parameter.

将使用category参数，以便用户可以从单个类别请求帖子。 这使他们不必在前端按类别进行筛选。 分页也将通过page参数实现。

These options should be appended to the URL when making the API request.

发出API请求时，应将这些选项附加到URL。

GET https://apiurl?key=abcdef&category=general&page=1

This request would generate the following event:

该请求将生成以下事件：

{  "queryString": "key=abcdef&category=general&page=1",  "parameter": {},  "contextPath": "",  "parameters": {    "key": [      "abcdef"    ],    "category": [      "general"    ],    "page": [      "1"    ]  },  "contentLength": -1}

Let’s first authenticate the event. We’ll do this by checking that key has been provided and that it matches the defined API key.

首先让我们验证事件。 我们将通过检查是否提供了key以及它是否与定义的API密钥相匹配来完成此任务。

var API_KEY = 'abcdef';

function doGet(e) {  if (!isAuthorized(e)) {    return buildErrorResponse('not authorized');  }      return buildSuccessResponse('authorized');}

function isAuthorized(e) {  return 'key' in e.parameters && e.parameters.key[0] === API_KEY;}

function buildSuccessResponse(message) {  var output = JSON.stringify({    status: 'success',    message: message  });    return ContentService.createTextOutput(output)   .setMimeType(ContentService.MimeType.JSON);}

function buildErrorResponse(message) {  var output = JSON.stringify({    status: 'error',    message: message  });    return ContentService.createTextOutput(output)   .setMimeType(ContentService.MimeType.JSON);}

The API key is defined as abcdef at the top of the file. The isAuthorizedfunction returns a boolean value for authentication. If this returns false a not authorized message is returned through the buildErrorResponse helper. If isAuthorized returns true, the function is allowed to continue until a successful response is returned through buildSuccessResponse.

API密钥在文件顶部定义为abcdef 。 isAuthorized函数返回用于验证的布尔值。 如果返回false，则通过buildErrorResponse帮助程序返回not authorized消息。 如果isAuthorized返回true，则该函数将继续运行，直到通过buildSuccessResponse返回成功的响应buildSuccessResponse 。

A drawback I’ve found when building applications on Google Apps Script is that you don’t have the capability to set status codes for responses. These can be used to indicate whether the response was successful and if not, why.

在Google Apps脚本上构建应用程序时，我发现一个缺点是您没有设置响应状态码的功能。 这些可以用来指示响应是否成功，如果失败，为什么。

For example, a 401 Unauthenticated status code implies that the user credentials didn’t match and that they should try again using different credentials. Responses always have a 200 OK status code when using doGet, even for handled unsuccessful responses. I get around this by adding a status value to all the API responses. For this simple example, the status can either be success or error, but it’s easy to see how this pattern could be extended for other more granular statuses if required.

例如，一个401未经身份验证的状态代码表示用户凭据不匹配，因此他们应该使用其他凭据重试。 使用doGet ，响应始终具有200 OK状态代码，即使对于处理失败的响应也是如此。 通过向所有API响应添加status值来解决此问题。 对于这个简单的示例，状态可以是success或error ，但是很容易看出如何将这种模式扩展到其他更精细的状态(如果需要)。

Let’s create two functions to parse the category and page parameters. If a valid numerical page isn’t supplied, its default value should be 1. Likewise if a category isn’t provided, the default value should be set to null, in which case posts from all categories should be returned.

让我们创建两个函数来解析category和page参数。 如果未提供有效的数字page ，则其默认值应为1 。 同样，如果未提供类别，则默认值应设置为null ，在这种情况下，应返回所有类别的帖子。

function getPageParam(e) {  if ('page' in e.parameters) {    var page = parseInt(e.parameters['page'][0]);    if (!isNaN(page) && page > 0) {      return page;    }  }    return 1}

function getCategoryParam(e) {  if ('category' in e.parameters) {    return e.parameters['category'][0];  }    return null}

从电子表格中读取 (Reading from the spreadsheet)

Google Apps Script makes various global objects available that can be used to interact with G Suite products. We’ll use the SpreadsheetService to load our spreadsheet by ID and read the blog posts. The easiest way to look up a spreadsheet ID is by checking the Google Sheets URL for it.

Google Apps脚本提供了各种可用的全局对象，这些对象可用于与G Suite产品进行交互。 我们将使用SpreadsheetService通过ID加载电子表格并阅读博客文章。 查找电子表格ID的最简单方法是检查Google表格URL。

https://docs.google.com/spreadsheets/d/{id}/edit

After loading the spreadsheet through the openById method on the global SpreadsheetService, we need to get the active data range from the first worksheet. To return the most recent posts first, we should sort on the Timestamp column, which is the second column.

通过全局SpreadsheetService上的openById方法加载电子SpreadsheetService ，我们需要从第一个工作表中获取活动数据范围。 要首先返回最新的帖子，我们应该在“ 时间戳”列(第二列)上进行排序。

var SPREADSHEET_ID = '12345';var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);var worksheet = spreadsheet.getSheets()[0];var rows = worksheet.getDataRange() .sort({column: 2, ascending: false}) .getValues();

The rows array from getDataRange contains both the columns headings as the first array item and the blog post rows as subsequent array items. Headings can be mapped to blog posts so that the API can return full blog post objects rather than just the column values.

getDataRange中的rows数组getDataRange包含列标题作为第一个数组项，又包含博客帖子行作为后续数组项。 可以将标题映射到博客文章，以便API可以返回完整的博客文章对象，而不仅仅是返回列值。

var headings = rows[0].map(String.toLowerCase);var posts = rows.slice(1);var postsWithHeadings = addHeadings(posts, headings);

function addHeadings(posts, headings) {  return posts.map(function(postAsArray) {    var postAsObj = {};        headings.forEach(function(heading, i) {      postAsObj[heading] = postAsArray[i];    });        return postAsObj;  });}

过滤不相关的帖子 (Filtering irrelevant posts)

Blog posts should only be returned if their category matches the one requested, and posts from all categories should be returned if one wasn’t requested. Blog posts should also further only be returned if they have a Published value of true.

仅当博客文章的类别与请求的类别相匹配时，才应返回博客文章；如果未请求任何类别的博客，则应返回所有类别的文章。 如果博客帖子的Published值为true，则还应该进一步返回它们。

Let’s create a function to remove draft posts by an array filter:

让我们创建一个通过数组过滤器删除草稿的函数：

var postsPublic = removeDrafts(postsWithHeadings);

function removeDrafts(posts, category) {  return posts.filter(function(post) {    return post['published'] === true;  });}

And another function to filter on the post category:

另一个filter帖子类别的功能：

var category = getCategoryParam(e);var postsFiltered = filter(postsPublic, category);

function filter(posts, category) {  return posts.filter(function(post) {    if (category !== null) {      var c1 = post['category'].toLowerCase()      var c2 = category.toLowerCase()      return c1 === c2;    } else {      return true;    }  });}

分页回应 (Paginating responses)

For performance reasons, we should limit the maximum number of posts returned by a single API response. The client should be able request the next page of posts by increasing the page query parameter.

出于性能原因，我们应限制单个API响应返回的最大帖子数。 客户应该能够通过增加page查询参数来请求下一页的帖子。

Let’s implement this through a pagination function that returns an object containing the filtered blog posts under posts and pagination links under pages. If there are more or previous results, pages contains the appropriate page number under next and previous respectively.

让我们通过分页功能实现此功能，该功能返回一个对象，该对象包含帖子下的已过滤博客posts和pages下的分页链接。 如果有更多或上一个结果，则pages next和previous分别包含适当的页码。

var RESULTS_PER_PAGE = 5;var page = getPageParam(e)var paginated = paginate(postsFiltered, page);

function paginate(posts, page) {  var postsCopy = posts.slice();  var postsChunked = [];  var postsPaginated = {    posts: [],    pages: {      previous: null,      next: null    }  };    while (postsCopy.length > 0) {    postsChunked.push(postsCopy.splice(0, RESULTS_PER_PAGE));  }    if (page - 1 in postsChunked) {    postsPaginated.posts = postsChunked[page - 1];  } else {    postsPaginated.posts = [];  }

if (page > 1 && page <= postsChunked.length) {    postsPaginated.pages.previous = page - 1;  }    if (page >= 1 && page < postsChunked.length) {    postsPaginated.pages.next = page + 1;  }    return postsPaginated;}

Our buildSuccessResponse helper from earlier can be updated to handle posts and pages. The API should then ready for deployment.

我们之前的buildSuccessResponse帮助器可以进行更新以处理posts和pages 。 然后，API应该已准备好进行部署。

function buildSuccessResponse(posts, pages) {  var output = JSON.stringify({    status: 'success',    data: posts,    pages: pages  });    return ContentService.createTextOutput(output)    .setMimeType(ContentService.MimeType.JSON);}

部署API (Deploying the API)

With the script finalized, the API can be made publicly available by going to Publish > Deploy as webapp from the script editor menu bar. Ensure the app is being executed as me and that anyone, even anonymous has access.

脚本完成后，可以通过从脚本编辑器菜单栏转到“ 发布”>“作为weba pp 部署”来公开使用该API。 确保该应用程序正在以我本人的身份执行，并且任何人都可以执行，即使匿名用户也可以访问。

Deploying will return a URL that will look like the one below:

部署将返回一个如下所示的URL：

https://script.google.com/macros/s/{id}/exec

Append the API key to the URL and then enter it into your web browser to check that the API is working correctly. Hopefully you should see a JSON response with three top level keys: status, posts, pages.

将API密钥附加到URL，然后将其输入到Web浏览器中以检查API是否正常运行。 希望您会看到带有三个顶级键的JSON响应： status ， posts ， pages 。

https://script.google.com/macros/s/{id}/exec?key=abcdef

摘要 (Summary)

If you followed along you should now have a functional CMS built on Google Sheets, Google Forms, and Google Apps Script. It’s not advanced, but it was easy to get started and delivers the core requirements of a CMS. Connecting it to a front-end was outside the scope of this article, but if you want to see how that is done, you should check out the demo I put together on GitHub.

如果您按照上述步骤进行操作，那么现在应该可以在Google表格，Google表单和Google Apps脚本的基础上构建功能强大的CMS。 它不是高级的，但是很容易上手并满足CMS的核心要求。 将其连接到前端不在本文的讨论范围内，但是如果您想了解如何完成此操作，则应查看我在GitHub上放在一起的演示。

Next time you’re about to reach for the technology flavor of the day, I challenge you to take a few moments to think about whether there’s an easier solution that can be built using existing software. That solution might not be fully featured, but it will often get you 80% of the way there for 20% of the effort, which in many cases will be good enough. I hope this blog post demonstrated that and that you learned a thing or two about Google Apps Script along the way.

下次您将要讨论当今的技术风格时，我挑战您花一些时间考虑是否可以使用现有软件构建更简单的解决方案。 该解决方案可能无法完全发挥作用，但是通常可以为您提供80％的解决方案，而无需花费20％的精力 ，这在很多情况下已经足够了。 我希望这篇博客文章能够证明这一点，并且您在此过程中对Google Apps脚本学到了一两个知识。

观看演示 (View the demo)

在GitHub上查看项目 (View the project on GitHub)

翻译自: https://www.freecodecamp.org/news/use-google-sheets-and-google-apps-script-to-build-a-blog-cms-c2eab3fb0b2b/

apps2sd使用