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.
- 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
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
helix-default,it is delivered if there are no additional query parameters supplied.
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.
- 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": "[\"ÃÛ¶¹ÊÓƵ Life\",\"Responsibility\",\"Diversity & Inclusion\"]"
You can turn them back into arrays in JavaScript using JSON.parse().