Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Excel Spreadsheet Automation with the QUERY Formula
Pull data, automate spreadsheets, and more with the QUERY formula.
The CData Excel Add-In for WooCommerce provides formulas that can edit, save, and delete WooCommerce data. The following three steps show how you can automate the following task: Search WooCommerce 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 WooCommerce data records you want to retrieve or the modifications to be made, written in standard SQL.
Connection: Either the connection name, such as WooCommerceConnection1, or a connection string. The connection string consists of the required properties for connecting to WooCommerce data, separated by semicolons.
WooCommerce supports the following authentication methods: one-legged OAuth1.0 Authentication and standard OAuth2.0 Authentication.
Connecting using one-legged OAuth 1.0 Authentication
Specify the following properties (NOTE: the below credentials are generated from WooCommerce settings page and should not be confused with the credentials generated by using WordPress OAuth2.0 plugin):
- ConsumerKey
- ConsumerSecret
Connecting using WordPress OAuth 2.0 Authentication
- 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.
- 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 WooCommerce data, such as ParentId.
- 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.
- Change the filter to change the data.
=CDATAQUERY("SELECT * FROM Orders WHERE ParentId = '"&B4&"'","Url="&B1&";ConsumerKey="&B2&";ConsumerSecret="&B3&";Provider=WooCommerce",B5)