Excel Spreadsheet Automation with the QUERY Formula

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

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

  Start by setting the Profile connection property to the location of the Harvest Profile on disk (e.g. C:\profiles\Harvest.apip). Next, set the ProfileSettings connection property to the connection string for Harvest (see below).

  Harvest API Profile Settings

  To authenticate to Harvest, you can use either Token authentication or the OAuth standard. Use Basic authentication to connect to your own data. Use OAuth to allow other users to connect to their data.

  Using Token Authentication

  To use Token Authentication, set the APIKey to your Harvest Personal Access Token in the ProfileSettings connection property. In addition to APIKey, set your AccountId in ProfileSettings to connect.

  Using OAuth Authentication

  First, register an OAuth2 application with Harvest. The application can be created from the "Developers" section of Harvest ID.

  After setting the following connection properties, you are ready to connect:

  • ProfileSettings: Set your AccountId in ProfileSettings.
  • AuthScheme: Set this to OAuth.
  • OAuthClientId: Set this to the client ID that you specified in your app settings.
  • OAuthClientSecret: Set this to the client secret that you specified in your app settings.
  • CallbackURL: Set this to the Redirect URI that you specified in your app settings.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to manage how the driver obtains and refreshes the OAuthAccessToken.
• 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 Harvest data, such as State.
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 Invoices WHERE State = '"&B5&"'","Profile="&B1&";ProfileSettings="&B2&";AccountId="&B3&";Provider=API",B6)
3. Change the filter to change the data.