Developer Guide to Document Authoring with Edge Delivery Services. Part 3

Author: Tom Cranstoun

Spreadsheets

Indexing

Spreadsheets and Json

Query parameters

Redirects

.helix folder

This is part 3 of my Developer’s Guide to Document Authoring with Edge Delivery Services.

Previously

We extended ‘Hello World’ with style variations on the bio block, added fragments, and discussed styling in more depth.

Next - We can use spreadsheets, not just documents.

Within Google Drive or Sharepoint, you can upload or create spreadsheets in Excel or Google Sheets format. If you are using Google Drive, remember to change your settings to convert Excel to Google Sheets automatically,

How to Convert Multiple Word Documents/Sheets to Google Docs.

Using spreadsheets

Spreadsheets are versatile tools that can be used for various purposes. In this post, we will explore some of the most common uses for spreadsheets, including:

1 Use a spreadsheet as a source of JSON for websites.

One can create a spreadsheet in MS Excel and upload it to Google Drive or create one directly inside Google Drive. For the demo, we will create a fortune cookie block in our document that displays a random cookie from a JSON file named cookies.

In Google Drive, create a folder named /data; in that folder, create a sheet named cookies.

The first row of the sheet becomes the json attribute names.

We now want a list of cookies, and I will get chatGPT to give me a list of 9

Press the preview button in the sidekick to see a nice json layout; I use json view.

Investigating the structure of the json.

The JSON structure consists of an object with several properties:

1. "total": A number representing the total count of data elements.

2. "offset": A number indicating the starting position of the data subset.

3. "limit": A number specifying the maximum number of data elements in this subset.

4. "data": An array containing the actual data elements. Each element is an object with two properties:

- "key": A string representing the message's short title

- "value": A string containing the actual message.

5. ":type": A string indicating the type of data, in this case, "sheet", the source of the data

In simple terms:

- The object has metadata about the data set, including the total number of elements, the offset and limit for this subset of data (useful for pagination), and the data type.

- The actual data is in the "data" array.

All JSON derived from spreadsheets in Document Authoring with Edge Delivery Services share the same formatting; the main json is in the ‘data’ element, and everything else is metadata.

We would like to build a fortune cookie block.

Start by creating a table ‘FortuneCookie’ in our Hello World document.

Remember that the name cell, the only cell in the case above, is changed to lowercase when used in Edge Delivery Services.

In our GitHub, we need to create

blocks/fortunecookie/fortunecookie.css and

blocks/fortunecookie/fortunecookie.js

The code in fortunecookie.js will use the browser's fetch command to read the json, generate a random number, use that number to pluck a key and a value, and then place the results in the block.

export  default async function decorate(block) {
    const fortuneCookieElement = document.querySelector('.fortunecookie');
    const url = '/data/cookies.json';
    try {
        const response = await fetch(url);
        const data = await response.json();
        
        const dataArray = data.data;
        const randomIndex = Math.floor(Math.random() * dataArray.length);
        const randomItem = dataArray[randomIndex];

        const content = `<p><strong>${randomItem.key}:</strong> ${randomItem.value}</p>`;
        fortuneCookieElement.innerHTML = content;
    } catch (error) {
        console.error('Error fetching the JSON data:', error);
    }
}

Viewing the page

We now have a Fortune Cookie on our page. When someone presses preview and publish, you can add any JSON to your pages using spreadsheets.

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 your website or web application can easily consume.

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, as seen above.

Edge Delivery Services allows you to manage workbooks with multiple sheets.

If there is only one sheet, Edge Delivery Services will use that sheet as the default source of information.

Edge Delivery Services will only deliver sheets with sheet names prefixed with helix if there are multiple sheets. This lets you keep additional information and possibly formulas in the same spreadsheet that will not be delivered to the web.

If there is a sheet named helix-default, it is delivered if no additional query parameters are supplied.

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

Query Parameters

Offset and Limit

Spreadsheets and JSON files can become very large. Edge Delivery Services supports limit and offset query parameters in such cases to indicate which spreadsheet rows are delivered.

As Edge Delivery Services always compresses the JSON, payloads are generally relatively small. Therefore, if the limit query parameter is not specified, the Edge Delivery Services limits the number of rows it returns to 1000 by default. 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. For example,?sheet=jobs will return the sheet named helix-jobs, and ?sheet=jobs&sheet=articles will return the data for helix-jobs and helix-articles.

2 Notification of events from Edge Delivery Services

The empty sheet

Edge Delivery Services keeps a json index of all published pages; in Document Authoring with Edge Delivery Services, you can have a copy of this index by creating a sheet in the root folder of your document store. This sheet must be named query-index and have a single sheet named raw_index. The columns are path, title, image, description and lastmodified. You can choose to add others like ‘author’. See indexing

When you publish a page, Edge Delivery automatically enters it into this spreadsheet with updated attributes and publishes the sheet. It's a two-way traffic sheet.

Using this documentation website as an example

And when we preview

{
total: 4,
offset: 0,
limit: 4,
data: [
{
path: "/blogs/ddt/a-developer-guide-to-document-authoring-with-edge-delivery-services-part-3",
title: "A Developer’s Guide to Document Authoring with Edge Delivery Services  Part 3",
image: "/blogs/ddt/media_1828eee6e37181840c0b11561cc97af7dadd73e34.png?width=1200&format=pjpg&optimize=medium",
description: "This tutorial will guide you through the use of spreadsheets in Document Authoring with Edge Delivery Services.",
lastModified: "1718721409"
},
{
path: "/blogs/ddt/a-developer-guide-to-document-authoring-with-edge-delivery-services-part-1",
title: "A Developer’s Guide to Document Authoring with Edge Delivery Services - Part 1",
image: "/blogs/ddt/media_14dbb3469aefb3b0553ec8d980680e209ce6b8d01.png?width=1200&format=pjpg&optimize=medium",
description: "This tutorial will guide you through creating your blocks and amending the HTML provided by Edge Delivery Services to ensure that the page has the look and feel you need, with coding examples. An ongoing series for developers",
lastModified: "1718723198"
},
{
path: "/blogs/ddt/a-developer-guide-to-document-authoring-with-edge-delivery-services-part-2",
title: "A Developer’s Guide to Document Authoring with Edge Delivery Services  Part 2",
image: "/blogs/ddt/media_1828eee6e37181840c0b11561cc97af7dadd73e34.png?width=1200&format=pjpg&optimize=medium",
description: "This tutorial will guide you through creating your blocks and amending the HTML provided by Edge Delivery Services to ensure that the page has the look and feel you need, with coding examples. Part 2 covers fragments and has more  metadata detail",
lastModified: "1718626192"
},
{
path: "/blogs/ddt/a-managers-guide-to-document-authoring-with-edge-delivery-services",
title: "A manager’s guide to Document Authoring with Edge Delivery Services",
image: "/blogs/ddt/media_1a92fe47eaea83b4441bd39d740d88524886ea019.png?width=1200&format=pjpg&optimize=medium",
description: "A web page describing how Edge Delivery Services works",
lastModified: "1718723133"
}
],
:type: "sheet"
}

This json file can be used in Edge Delivery Services to create dynamic blocks, where the content is pulled from a json file.

Creating a BlogList, using index-query.json

We added a bloglist block to the document.

We will create the bloglist code at

/blocks/bloglist/bloglist.css and
/blocks/bloglist/bloglist.js

Bloglist Javascript

The javascript will also use fetch, similar to the fortunecookie block. We want to obtain the json, restrict it to just my blogs, sort by title, and then restrict the final json to three entries. You can see four entries in the sample above.

export default async function decorate(block) {
  const blogListElement = document.querySelector(".bloglist");
  const url = "/query-index.json";

  try {
    const response = await fetch(url);
    const data = await response.json();

    // Filter the blog items based on the presence of "/blogs/ddt" in the path
    const filteredBlogItems = data.data.filter((item) =>
      item.path.includes("/blogs/ddt")
    );

    // Sort the filtered blog items by title
    const sortedBlogItems = filteredBlogItems.sort((a, b) =>
      a.title.localeCompare(b.title)
    );

    // Limit the sorted blog items to a maximum of 3
    const limitedBlogItems = sortedBlogItems.slice(0, 3);

    // Process the limited data and generate the content
    const content = generateContent(limitedBlogItems);

    blogListElement.innerHTML = content;
  } catch (error) {
    console.error("Error fetching the JSON data:", error);
  }
}
function generateContent(blogItems) {
  let content = "";

  blogItems.forEach((item) => {
    const lastModifiedDate = new Date(item.lastModified * 1000);
    const formattedDate = formatDate(lastModifiedDate);

    content += `
            <div class="blog-item">
                <a href="${item.path}">
                    <strong>${item.title}</strong>
                </a>
                <p>${item.description}</p>
                <p class="last-modified">Last Modified: ${formattedDate}</p>
            </div>
        `;
  });

  return content;
}

function formatDate(date) {
  const day = String(date.getDate()).padStart(2, "0");
  const month = getMonthName(date.getMonth());
  const year = date.getFullYear();

  return `${day}/${month}/${year}`;
}

function getMonthName(monthIndex) {
  const monthNames = [
    "January",
    "February",
    "March",
    "April",
    "May",
    "June",
    "July",
    "August",
    "September",
    "October",
    "November",
    "December",
  ];

  return monthNames[monthIndex];
}


Create the BlogList style

.bloglist {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
    gap: 20px;
}

.blog-item {
    border: 1px solid #ccc;
    padding: 20px;
    box-sizing: border-box;
    display: flex;
    flex-direction: column;
}

.blog-item img {
    width: 100%;
    height: 280px;
    object-fit: cover;
    margin-bottom: 10px;
}

.blog-item p {
    margin: 10px 0;
}

.blog-item .last-modified {
    margin-top: auto;
    padding-top: 10px;
    position: relative;
}

.blog-item .last-modified::before {
    content: '';
    position: absolute;
    top: 0;
    left: -20px;
    right: -20px;
    border-top: 1px solid #ccc;
}

@media screen and (max-width: 600px) {
    .bloglist {
        grid-template-columns: 1fr;
    }
}

Previewing the page

3 Configuring Edge Delivery Services

Edge Delivery Services is controlled in many ways; spreadsheets are the content Author-friendly way of performing this task.

Redirects

You can see that we created a query-index sheet in our root folder. This two-way sheet receives information from the publishing process and can be used to create dynamic content for your pages.

Edge Delivery Services also has a sheet named redirects, which creates the json entity redirects.json

In this sheet, we have two columns: Source and Destination.

I made the mistake of creating my blogs in the root folder of my Google Drive and not naming them similarly, so I moved them to a subtree ‘/blogs/ddt’ and created the redirects sheet in the root of the folder.


I also deleted the original pages, so you are redirected to the new destination when you ask for the older location: nice one, Adobe. Thinking about your hierarchy before you begin is preferable, but knowing that you can change it afterwards is nice.

The .helix folder

You can control the inner workings of Edge Delivery services using the folder named .helix

Note the ‘.’ -- it is dot-helix .helix

Note the name is a previous code name for this technology. The dot in front makes this folder unservable from the internet, which is excellent for secrets like how you are configuring Edge Delivery Services. You could think of the ‘.’ as just like the dot in Unix -- files are hidden!!

Anyway, the files you create in this folder are used for configuring Helix

Headers

The sheet ‘headers’, which in turn ought to create headers,json - you cannot see this file, contains the key and value of the headers you want to send out with your web pages.

My headers contain a content security policy, which helps to detect and mitigate certain types of attacks, such as cross-site scripting (XSS) and data injection attacks.

Link to CSP Description on Mozilla


Bringing up the sidekick

When you click on preview, instead of a new set of options, including publish, you get a transient sign ‘Configuration successfully activated’.

Because this is in the .helix folder, it does not need publishing -- it is not visible to the internet.

Config

The next sheet to look at is config; you put exciting things here for your configuration.

Part of mine is shown below; keys and secrets are here to configure CDN and caching; you can also configure authentication. I will not show you my samples for the obvious reasons.

You can find more info about the ‘.helix’ folder and metadata config here.

https://www.aem.live/docs/bulk-metadata

My next post will be about tips and tricks with Json, including building a react application inside Edge Delivery Services.

/fragments/ddt/proposition
style
bg-dark

Related Articles

guide
Back to Top