Using Spreadsheets to Manage Tabular Data tabular-data
Learn how to use spreadsheets to manage tabular data for various values such as metadata and redirects for your AEM with Edge Delivery Services site.
Use Cases use-cases
For any AEM with Edge Delivery Services site, there is a need to maintain lists of tabular data such as for key-value mappings. These can be lists of many different values such as metadata and redirects. Edge Deliver Services allows you to maintain such tabular lists using an intuitive tool: the spreadsheet. AEM translates these spreadsheets into JSON files that can easily be consumed by your website or web application.
Common use cases include:
- Placeholders
- Metadata
- Headers
- Redirects
- Configurations such as for CND setups
In addition, you can create your spreadsheets of any structure to store mappings for your own purposes.
This document uses the example of redirects to illustrate how to create such spreadsheets. See the previously-linked topics in the Edge Delivery Services documentation for details of each use case.
Prerequisites prerequisites
In order to create mappings using spreadsheets in your AEM with Edge Delivery Services project, you need to have created your site using the latest site template.
Please see the document Developer Getting Started Guide for WYSIWYG authoring with Edge Delivery Services for more information.
Creating a Spreadsheet spreadsheet
In this example, you will create a spreadsheet to manage redirects for your AEM with Edge Delivery Services site. The same steps apply to other spreadsheet types that you wish to create.
-
Sign in to your AEM as a Cloud Service authoring instance, go to the Sites console, and navigate to the root of the site which requires a spreadsheet. Tap or click Create -> Page.
-
On the Template tab of the create page wizard, tap or click the Redirects template to select it and then tap or click Next.
-
The Properties tab of the wizard presents the default values for the redirects spreadsheet. Tap or click Create.
-
Title - Leave this value as-is.
-
Columns - The minimum columns needed for redirects are prepopulated.
- source - The page to be redirected
- destination - The page to redirect to
-
-
In the Success dialog, tap or click Open.
-
A new tab opens with the spreadsheet loaded into an editor with the predefined source and destination columns. To define your redirects, tap or click the empty row of the source column. Changes are saved automatically as you edit the spreadsheet.
- The source is relative to the domain of your website, so it only contains the relative path.
- The destination can be either a fully qualified URL if you are redirecting to a different website, or it can be a relative path if you are redirecting within your own website.
- Use the tab-key to move focus to the next cell.
- The editor adds new rows to the spreadsheet as necessary.
- To delete or move a row, use the Delete icon at the end of each row and the drag handles at the beginning of each row, respectively.
Publishing a Spreadsheet paths.json paths-json
In order for AEM to be able to publish the data in your spreadsheet, you additionally need to update the paths.json
file of your project.
-
Open the root of your project in GitHub.
-
Tap or click the
paths.json
file to open its details and then the Edit icon. -
Add a line to map your new spreadsheet to a
redirects.json
resource.code language-json { "mappings": [ "/content/<site-name>/:/", "/content/<site-name>/redirects:/redirects.json" ] }
note note NOTE This paths.json
entry is based on the example of creating redirects using tabular data. Make sure to update the path appropriate to the type of spreadsheet you are creating. -
Click Commit changes… to save the changes to
main
.- Either commit to
main
or create a pull request as per your process.
- Either commit to
-
When you are finished defining your redirects and you updated the path mapping, return to the Sites console.
-
Tap or click to select the redirects spreadsheet that you created in the console and then tap or click Quick Publish in the actions bar to publish the spreadsheet.
-
In the Quick Publish dialog, tap or click Publish.
-
A banner confirms the publication.
The redirects spreadsheet is now published and publicly-accessible.
Other Spreadsheet Types other
Now that you know how to create a redirects spreadsheet, you can create any other standard spreadsheet type:
- Placeholders
- Metadata
- Headers
- Configuration
- Taxonomy
Simply follow the same steps in the sections Create Spreadsheet and Update paths.json and choose the appropriate template and update the paths.json
file appropriately.
For Configuration, Headers and Metadata make sure to add a mapping to publish them to their default locations:
- Configuration:
/.helix/config.json
- Headers:
/.helix/headers.json
- Metadata:
/metadata.json
- Taxonomy: Please see the document Managing Taxonomy Data for more information.
Additionally, you can create your own spreadsheet with arbitrary columns for your own use.
helix-query.yaml
file.Creating Your Own Spreadsheet own-spreadsheet
-
Follow the same steps in the section Create Spreadsheet.
-
When selecting the template, choose Spreadsheet.
-
In the Properties tab of the wizard, you can add your own columns.
- In the Columns section, tap or click Add to add a new column.
- Provide a name for the column.
- Remove or reorganize the columns using the Delete and drag handle icons, respectively.
-
Create the spreadsheet and publish as per the instructions for the redirects spreadsheet.
-
Add a mapping to the
paths.json
file as per the instructions for the redirects spreadsheet.