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 Pipedrive provides formulas that can edit, save, and delete Pipedrive data. The following three steps show how you can automate the following task: Search Pipedrive 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 Pipedrive data records you want to retrieve or the modifications to be made, written in standard SQL.
Connection: Either the connection name, such as PipedriveConnection1, or a connection string. The connection string consists of the required properties for connecting to Pipedrive data, separated by semicolons.
- 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 Pipedrive data, such as Value.
- 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 Deals WHERE Value = '"&B4&"'","AuthScheme="&B1&";CompanyDomain="&B2&";APIToken="&B3&";Provider=Pipedrive",B5)