Documentation AEM as a Cloud Service User Guide

Set up your Google Sheets or Microsoft Excel files to start accepting data

Last update: Mon Jan 27 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

Topics:
Edge Delivery Services

CREATED FOR:

Admin
Developer

Once you have created and previewed the form, it’s time to enable the corresponding spreadsheet to start receiving data. You can

Manually enable the spreadsheet to accept data
Use Admin APIs to enable a spreadsheet to accept data

Document-based Authoring ecosystem

Manually enable the spreadsheet to accept data

To enable the spreadsheet to accept data

Open the spreadsheet that has your form and append a new sheet, renaming it to incoming. For example, the enquiry form Microsoft Excel workbook.

WARNING
If the incoming sheet is not present, AEM does not send any data to the spreadsheet.
In this sheet, insert a table named “intake_form”. Select the number of columns required to match your form field names. Then, in the toolbar go to Insert > Table and click OK.
Change the name of the table to “intake_form”. In Microsoft Excel, to change the name of the table, select the table and click Table Design.
Next, add the form field names as the table headers. To make sure the fields are exactly the same, you can copy and paste them over from the “shared-aem” sheet. In your “shared-aem” sheet, select and copy the form IDs listed under the “Name” column, except for the submit field.
In the “incoming” sheet, select Paste Special > Transpose Rows to Columns to copy the field IDs over as column headers in this new sheet. Keep only the fields whose data need to capture other can be ignored.

Each value in the Name column of the shared-aem sheet, excluding the submit button, can serve as a header in the incoming sheet. For instance, consider the following image illustrating headers for a “enquiry” form:
Use the AEM Sidekick extension to preview the form updates. Your sheet is now ready to accept incoming form submissions.

NOTE
Even if you have previewed the sheet before, you must preview it again after creating the incoming sheet for the first time.

Once the field names are added to the incoming sheet, your form becomes ready to accept submissions. You can preview the form and submit data to the sheet using it.

Once sheet is set up to receive data, you can preview the form to start sending data to the sheet.

WARNING

Never should the “shared-aem” sheets contain any personally identifiable information or sensitive data that you are not comfortable with being publicly accessible.

Use Admin APIs to enable a spreadsheet to accept data

You can also send a POST request to the form to enable it to accept data and configure headers for the incoming sheet. Upon receiving the POST request, the service analyzes the body of request and autonomously generates the essential headers and sheets needed for data ingestion.

To use Admin APIs to enable a spreadsheet to accept data:

Open the workbook that you have created and change the name of the default sheet to incoming.

WARNING
If the incoming sheet doesn’t exist, AEM won’t send any data to this workbook.
Preview the sheet in the sidekick.

NOTE
Even if you have previewed the sheet before, you must preview it again after creating the incoming sheet for the first time.

Send the POST request to generate the appropriate headers in the incoming sheet, and add the shared-default sheets to your spread sheet, if it does not exist already.

To understand how to format the POST request for setting up your sheet, refer to the Admin API documentation. You can look at the example provided below:

Request


POST 'https://admin.aem.page/form/{owner}/{repo}/{branch}/contact-us.json' \
--header 'Content-Type: application/json' \
--data '{
    "data": {
        "Email": "john@wknd.com",
        "Name": "John",
        "Subject": "Regarding Product Inquiry",
        "Message": "I have some questions about your products.",
        "Phone": "123-456-7890",
        "Company": "Adobe Inc.",
        "Country": "United States",
        "PreferredContactMethod": "Email",
        "SubscribeToNewsletter": true
    }
}'

Response


HTTP/2 200
content-type: application/json
x-invocation-id: 1b3bd30a-8cfb-4f85-a662-4b1f7cf367c5
cache-control: no-store, private, must-revalidate
accept-ranges: bytes
date: Sat, 10 Feb 2024 09:26:48 GMT
via: 1.1 varnish
x-served-by: cache-del21736-DEL
x-cache: MISS
x-cache-hits: 0
x-timer: S1707557205.094883,VS0,VE3799
strict-transport-security: max-age=31557600
content-length: 138

{"rowCount":2,"columns":["Email","Name","Subject","Message","Phone","Company","Country",      "PreferredContactMethod","SubscribeToNewsletter"]}%

You can use tools like curl or Postman to execute this POST request, as demonstrated below:


curl -s -i -X POST 'https://admin.aem.page/form/wkndform/wefinance/main/contact-us.json' \
    --header 'Content-Type: application/json' \
    --data '{
        "data": {
            "Email": "john@wknd.com",
            "Name": "John",
            "Subject": "Regarding Product Inquiry",
            "Message": "I have some questions about your products.",
            "Phone": "123-456-7890",
            "Company": "Wknd Inc.",
            "Country": "United States",
            "PreferredContactMethod": "Email",
            "SubscribeToNewsletter": true
    }
}'

The above mentioned POST request provides sample data, including both form fields and their respective sample values. This data is used by the Admin service to set up the form.

Your form is now enabled to accept data. You also observe the following changes in your spreadsheet:

Automatic changes to sheet once it is enabled to accept data.

Once the sheet is set to recieve data, you observe the following changes in your spreadsheet:

A sheet named “Slack” is added to your Excel Workbook or Google Sheet. In this sheet, you can configure automatic notifications for a designated Slack channel whenever new data is ingested into your spreadsheet. At present, AEM supports notifications exclusively to the AEM Engineering Slack organization and the Adobe Enterprise Support organization.

To set up Slack notifications enter the “teamId” of the Slack workspace and the “channel name” or “ID”. You can also ask the slack-bot (with the debug command) for the “teamId” and the “channel ID”. Using the “channel ID” instead of the “channel name” is preferable, as it survives channel renames.

NOTE
Older forms didn’t have the “teamId” column. The “teamId” was included in the channel column, separated by a “#” or “/”.
Enter any title that you want and under fields enter the names of the fields you want to see in the Slack notification. Each heading should be separated by a comma (For example name, email).

WARNING
Never should the “shared-default” sheets contain any personally identifiable information or sensitive data that you are not comfortable with being publicly accessible.

Next, you can customize the thank you message.

Set up your Google Sheets or Microsoft Excel files to start accepting data

Manually enable the spreadsheet to accept data

Use Admin APIs to enable a spreadsheet to accept data

Automatic changes to sheet once it is enabled to accept data.

See also