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://chromewebstore.google.com/detail/aem-sidekick/igkmdomcgoebiipaifhmpfjhbjccggml"
    },
    {
      "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.

• If there is only one sheet, AEM will by default use that sheet as the source of the information.
• If there are multiple sheets, AEM will only deliver sheets that are prefixed with shared- (or helix-) which lets you keep additional information and possibly formulas in the same spreadsheet that are not delivered to the web.
• If there is a sheet named shared-default (or helix-default), it is delivered if there are no additional query parameters supplied.

Note: the helix- prefix is deprecated and the more neutral shared- prefix should be used.

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 shared-jobs and ?sheet=jobs&sheet=articles will return the data for the sheets named shared-jobs and shared-articles.

Special Sheet Names

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

• The forms service only writes to a sheet named incoming, which is never delivered as a JSON.
• The index service only writes to a sheet named raw_index, which may be delivered to JSON in a simple single sheet setup.

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().