Spreadsheets and JSON

In addition to translating Google Docs and Word documents into markdown and HTML markup, AEM also translates spreadsheets (Microsoft Excel workbooks and Google Sheets) into JSON files that can easily be consumed by your website or web application.

This enables many uses for content that is table-oriented or structured.

Sheets and Sheet structure

The simplest example of a sheet consists of a table that uses the first row as column names and the subsequent rows as data. An example might look something like this.

After a preview and publish via the sidekick, AEM translates this table to a JSON representation which is served to requests to the corresponding .json resource. The above example gets translated to:

{
  "total": 4,
  "offset": 0,
  "limit": 4,
  "data": [
    {
      "Source": "/sidekick-extension",
      "Destination": "https://chrome.google.com/webstore/detail/helix-sidekick-beta/ccfggkjabjahcjoljmgmklhpaccedipo"
    },
    {
      "Source": "/github-bot",
      "Destination": "https://github.com/apps/helix-bot"
    },
    {
      "Source": "/install-github-bot",
      "Destination": "https://github.com/apps/helix-bot/installations/new"
    },
    {
      "Source": "/tutorial",
      "Destination": "/developer/tutorial"
    }
  ],
  ":type": "sheet"
}

AEM allows you to manage workbooks with multiple sheets.

See the following section for details on how to query a specific sheet.

Query Parameters

Offset and Limit

Spreadsheets and JSON files can get very large. In such cases, AEM supports the use of limit and offset query parameters to indicate which rows of the spreadsheet are delivered.

As AEM always compresses the JSON, payloads are generally relatively small. Therefore by default AEM limits the number of rows it returns to 1000 if the limit query parameter is not specified. This is sufficient for many simple cases.

Sheet

The sheet query parameter allows an application to specify one or multiple specific sheets in the spreadsheet or workbook. As an example ?sheet=jobs will return the sheet named helix-jobs and ?sheet=jobs&sheet=articles will return the data for the sheets named helix-jobs and helix-articles.

Special Sheet Names

In certain use cases, AEM also writes to spreadsheets, where it expects specific sheet names.

See the links above for more information on those services.

Arrays

Native arrays are not supported as cell values, so they are delivered as strings.

"tags": "[\"Adobe Life\",\"Responsibility\",\"Diversity & Inclusion\"]"

You can turn them back into arrays in JavaScript using JSON.parse().