Excel Spreadsheet Automation on Salesforce Data Cloud Data with the QUERY Formula

The CData Excel Add-In for Salesforce Data Cloud provides formulas that can query Salesforce Data Cloud data. The following three steps show how you can automate the following task: Search Salesforce Data Cloud 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 Salesforce Data Cloud data records you want to retrieve, written in standard SQL.

• Connection: Either the connection name, such as SalesforceDataCloudConnection1, or a connection string. The connection string consists of the required properties for connecting to Salesforce Data Cloud data, separated by semicolons.

  Salesforce Data Cloud supports authentication via the OAuth standard.

  OAuth

  Set to OAuth.

  Desktop Applications

  CData provides an embedded OAuth application that simplifies authentication at the desktop.

  You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the Salesforce Data Cloud console. For further information, see Creating a Custom OAuth App in the Help documentation.

  Before you connect, set these properties:

  • : GETANDREFRESH. You can use to avoid repeating the OAuth exchange and manually setting the .
  • (custom applications only): The Client ID assigned when you registered your custom OAuth application.
  • (custom applications only): The Client Secret assigned when you registered your custom OAuth application.

  When you connect, the driver opens Salesforce Data Cloud's OAuth endpoint in your default browser. Log in and grant permissions to the application.

  The driver then completes the OAuth process as follows:

  • Extracts the access token from the callback URL.
  • Obtains a new access token when the old one expires.
  • Saves OAuth values in so that they persist across connections.

  For other OAuth methods, including Web Applications and Headless Machines, refer to the Help documentation.

• 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 Salesforce Data Cloud data, such as EmployeeCount.
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 Account WHERE EmployeeCount = '"&B2&"'","InitiateOAuth="&B1&";Provider=SalesforceDataCloud",B3)

👁 Formula inputs used in this example. (Google Apps is shown.)

3. Change the filter to change the data. 👁 The outputs of the formula. (Google Apps is shown.)