Access Excel data in JSON format

In this blog post, we will try to understand the process of accessing or retrieving data defined across multiple tabs within Excel or Google Sheets. Our focus will be to extract this data in both JSON and tabular formats. This exploration is designed to arm you with the knowledge and skills to efficiently manipulate and utilize your data, irrespective of its layout within your spreadsheets.

Begin by creating a Google Sheet titled fruits-vegetables in the root folder as shown below:

fruits-vegetables Google sheet created in root folder as shown below:

Declare below 10 fruits name under first tab of sheet named as fruits.

Click on Preview button to activate the changes and make it available to get load on the page.

Hitting ‘https://main–edge–tokhanimran.hlx.page/fruits-vegetables.json’ URL with .json extension will allow us to access data in JSON or tabular format.

If sheet having only tab it will by default pick data from that tab.

Data is coming in tabular format because of AEM Sidekick extension as shown below:

Applying offset as 0 and limit to 5 will allow us to load first five fruits data and at the same time will help us to implement pagination.

It will load data in JSON as soon as we close the Sidekick extension:

In the instance where a single sheet contains multiple tabs, the system will, by default, access the data from the first tab when the URL is triggered.

Now, let’s enhance our spreadsheet by adding another tab named vegetables within the same sheet, as demonstrated below:

As mentioned earlier, on hitting URL will load data always from first tab as shown below:

If we try to access vegetables sheet using https://main–edge–tokhanimran.hlx.page/fruits-vegetables.json?sheet=vegetables URL will give below error:

To address above issue, it is advisable to adhere to a specific naming convention. Incorporating helix- as a prefix to every sheet name is recommended. For instance, helix-fruits and helix-vegetables would be ideal examples of this convention. This method ensures a seamless and error-free experience.

Now, let’s proceed by activating the sheet. Attempt to access it by using the sheet parameter value vegetables without the helix- prefix. This will enable us to retrieve data as demonstrated below:

Define Default Tab Within Sheet

In the pursuit of flexibility, one can designate any tab as the default by simply renaming it as helix-default as highlighted below in red.

When the URL is triggered, the system will automatically fetch the form data from the helix-default tab, thereby providing you with the desired information efficiently and effectively.