Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for Adobe Commerce provides formulas that can edit, save, and delete Adobe Commerce data. The following three steps show how you can automate the following task: Search Adobe Commerce data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the Adobe Commerce data records you want to retrieve or the modifications to be made, written in standard SQL.

• Connection: Either the connection name, such as Adobe CommerceConnection1, or a connection string. The connection string consists of the required properties for connecting to Adobe Commerce data, separated by semicolons.

  Adobe Commerce uses the OAuth 1 authentication standard. To connect to the Adobe Commerce REST API, you will need to obtain values for the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties by registering an app with your Adobe Commerce system. See the "Getting Started" section in the help documentation for a guide to obtaining the OAuth values and connecting.

  You will also need to provide the URL to your Adobe Commerce system. The URL depends on whether you are using the Adobe Commerce REST API as a customer or administrator.

  • Customer: To use Adobe Commerce as a customer, make sure you have created a customer account in the Adobe Commerce homepage. To do so, click Account -> Register. You can then set the URL connection property to the endpoint of your Adobe Commerce system.

  • Administrator: To access Adobe Commerce as an administrator, set CustomAdminPath instead. This value can be obtained in the Advanced settings in the Admin menu, which can be accessed by selecting System -> Configuration -> Advanced -> Admin -> Admin Base URL.

    If the Use Custom Admin Path setting on this page is set to YES, the value is inside the Custom Admin Path text box; otherwise, set the CustomAdminPath connection property to the default value, which is "admin".

• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Adobe Commerce data, such as Style.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM Products WHERE Style = '"&B5&"'","OAuthClientId="&B1&";OAuthClientSecret="&B2&";CallbackURL="&B3&";Url="&B4&";Provider=Adobe Commerce",B6)
3. Change the filter to change the data.