DocumentationAEM as a Cloud ServiceUser Guide

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

Last update: Wed Jun 19 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Admin
  • Developer

Once you’ve 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 or 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

  1. Open the spreadsheet that has your form and append a new sheet, renaming it to incoming.

    note warning
    WARNING
    If the incoming sheet is not present, AEM does not send any data to the spreadsheet.

  2. 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.

  3. 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.

  4. 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-default” sheet. In your “shared-default” sheet, select and copy the form IDs listed under the “Name” column, except for the submit field.

  5. 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-default sheet, excluding the submit button, can serve as a header in the incoming sheet. For instance, consider the following image illustrating headers for a “contact-us” form:

    Fields for a contact-us form

  6. Use the AEM Sidekick extension to preview the form updates. Your sheet is now ready to accept incoming form submissions.

    note note
    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 using Adaptive Forms Block or use POST requests to start sending data to the sheet.

WARNING
Never should the “shared-default” 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:

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

    note warning
    WARNING
    If the incoming sheet doesn’t exist, AEM won’t send any data to this workbook.

  2. Preview the sheet in the sidekick.

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

  3. 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

    code language-json 
    
POST 'https://admin.hlx.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

    code language-json 
    
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:

    code language-json 
    
curl -s -i -X POST 'https://admin.hlx.page/form/wkndforms/portal/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.

  1. 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 note
    NOTE
    Older forms didn’t have the “teamId” column. The “teamId” was included in the channel column, separated by a “#” or “/”.

  2. 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).

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

Send data to your sheet send-data-to-your-sheet

After the sheet is set to receive data, you can preview the form using Adaptive Forms Block or use Admin APIs to start sending data to the sheet.

Use Admin APIs to send data to your sheet

You can send POST requests directly to your form using hlx.page, hlx.live, or your production domain, to send data.


POST https://branch–repo–owner.hlx.(page|live)/email-form
POST https://my-domain.com/email-form
NOTE
The URL should not have the .json extension. You must publish the sheet for POST operations to function on .live or on the production domain.

Formatting the form data

There are a few different ways that you can format the form data in the POST body. You can use:

  • array of name:value pairs:

    code language-json 
    
{
  "data": [
    { "name": "name", "value": "Clark Kent" },
    { "name": "email", "value": "superman@example.com" },
    { "name": "subject", "value": "Regarding Product Inquiry" },
    { "name": "message", "value": "I have some questions about your products." },
    { "name": "phone", "value": "123-456-7890" },
    { "name": "company", "value": "Example Inc." },
    { "name": "country", "value": "United States" },
    { "name": "preferred_contact_method", "value": "Email" },
    { "name": "newsletter_subscribe", "value": true }
  ]
}

    For example

    code language-json 
    
curl -s -i -X POST 'https://main--portal--wkndforms.hlx.page/contact-us' \
    --header 'Content-Type: application/json' \
    --data '{
    "data": [
        { "name": "name", "value": "Clark Kent" },
        { "name": "email", "value": "superman@example.com" },
        { "name": "subject", "value": "Regarding Product Inquiry" },
        { "name": "message", "value": "I have some questions about your        products." },
        { "name": "phone", "value": "123-456-7890" },
        { "name": "company", "value": "Example Inc." },
        { "name": "country", "value": "United States" },
        { "name": "preferred_contact_method", "value": "Email" },
        { "name": "newsletter_subscribe", "value": true }
    ]
}'

  • an object with key:value pairs:

    code language-json 
    
    {
      "data": {
        "name": "Jessica Jones",
        "email": "jj@example.com",
        "subject": "Regarding Product Inquiry",
        "message": "I have some questions about your products.",
        "phone": "123-456-7890",
        "company": "Example Inc.",
        "country": "United States",
        "preferred_contact_method": "Email",
        "newsletter_subscribe": true
      }
    }

    For example,

    code language-json 
    
curl -s -i -X POST 'https://admin.hlx.page/form/wkndforms/portal/main/contact-us.json' \
--header 'Content-Type: application/json' \
--data '{
    "data": {
        "Email": "khushwant@wknd.com",
        "Name": "khushwant",
        "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
    }
}'

  • URL encoded (x-www-form-urlencoded) body (with content-type header set to application/x-www-form-urlencoded)

    code language-shell 
    
'Email=kent%40wknd.com&Name=clark&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'

    For example,

    code language-shell 
    
curl -s -i -X POST \
  -d 'Email=kent%40wknd.com&Name=clark&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' \
  https://main--portal--wkndforms.hlx.live/contact-us

Next, you can customize the thank you message.

See also

recommendation-more-help
fbcff2a9-b6fe-4574-b04a-21e75df764ab