Learn more
 

Excel Spreadsheet Automation with the QUERY Formula


Pull data, automate spreadsheets, and more with the QUERY formula.

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

About Salesforce Data Integration

Accessing and integrating live data from Salesforce has never been easier with CData. Customers rely on CData connectivity to:

  • Access to custom entities and fields means Salesforce users get access to all of Salesforce.
  • Create atomic and batch update operations.
  • Read, write, update, and delete their Salesforce data.
  • Leverage the latest Salesforce features and functionalities with support for SOAP API versions 30.0.
  • See improved performance based on SOQL support to push complex queries down to Salesforce servers.
  • Use SQL stored procedures to perform actions like creating, retrieving, aborting, and deleting jobs, uploading and downloading attachments and documents, and more.

Users frequently integrate Salesforce data with:

  • other ERPs, marketing automation, HCMs, and more.
  • preferred data tools like Power BI, Tableau, Looker, and more.
  • databases and data warehouses.

For more information on how CData solutions work with Salesforce, check out our Salesforce integration page.


Getting Started


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 records you want to retrieve or the modifications to be made, written in standard SQL.

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

    There are several authentication methods available for connecting to Salesforce: Login, OAuth, and SSO. The Login method requires you to have the username, password, and security token of the user.

    If you do not have access to the username and password or do not wish to require them, you can use OAuth authentication.

    SSO (single sign-on) can be used by setting the SSOProperties, SSOLoginUrl, and TokenUrl connection properties, which allow you to authenticate to an identity provider. See the "Getting Started" chapter in the help documentation for more information.

  • 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, such as Name.
  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.
    3. =CDATAQUERY("SELECT * FROM Account WHERE Name = '"&B4&"'","User="&B1&";Password="&B2&";SecurityToken="&B3&";Provider=Salesforce",B5)
  3. Change the filter to change the data.

Ready to get started?

Download a free trial of the Excel Add-In for Salesforce to get started:

 Download Now

Learn more:

Salesforce Icon Excel Add-In for Salesforce

The Salesforce Excel Add-In is a powerful tool that allows you to connect with live Salesforce account data, directly from Microsoft Excel.

Use Excel to read, write, and update Leads, Contacts, Opportunities, Accounts, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

In this article

Related articles