Excel Spreadsheet Automation on Outlook Data with the QUERY Formula

The CData Excel Add-In for Outlook provides formulas that can query Outlook data. The following three steps show how you can automate the following task: Search Outlook 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 Outlook 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 Outlook data, separated by semicolons.

  Using OAuth Authentication

  Microsoft Graph API uses OAuth 2.0 for authentication. You must register an application in the Microsoft Azure Portal to obtain OAuth credentials (Client ID and Client Secret).

  Obtaining OAuth Credentials

  1. Log in to the Azure Portal.
  2. Navigate to Azure Active Directory > App registrations.
  3. Click New registration to create a new application.
  4. Enter an application name and select the appropriate account types.
  5. Set the Redirect URI to your application's callback URL (e.g., http://localhost:33333 for desktop apps).
  6. Click Register to create the application.
  7. On the application overview page, copy the Application (client) ID - this is your OAuthClientId.
  8. Navigate to Certificates & secrets and create a new client secret.
  9. Copy the client secret value - this is your OAuthClientSecret.
  10. Navigate to API permissions and add the required Microsoft Graph API permissions:
      • Mail.Read - For accessing email messages
      • Contacts.Read - For accessing contacts
      • Calendars.Read - For accessing calendar events
      • Tasks.Read - For accessing To Do tasks
      • offline_access - For obtaining refresh tokens
  11. Click Grant admin consent to grant these permissions.

  Connecting with OAuth

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

  • AuthScheme: Set this to OAuth.
  • InitiateOAuth: Set this to GETANDREFRESH. The CData API Profile for Outlook will automatically walk through the OAuth process in order to obtain the access token.
  • OAuthClientId: Set this to the Application (client) ID from Azure Portal.
  • OAuthClientSecret: Set this to the client secret value from Azure Portal.
  • TenantId: Set this to your Azure AD tenant identifier (GUID or domain name like 'contoso.onmicrosoft.com').
  • CallbackURL: Set this to the Redirect URI you specified in your app registration (e.g., http://localhost:33333 for desktop apps).

  Example connection string

  Profile=C:\profiles\Outlook.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;TenantId=your_tenant_id;CallbackUrl=http://localhost:33333;
  

• 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 Outlook data, such as CalendarGroupId.
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 CalendarGroupCalendars WHERE CalendarGroupId = '"&B8&"'","Profile="&B1&";AuthScheme="&B2&";InitiateOAuth="&B3&";OAuthClientId="&B4&";OAuthClientSecret="&B5&";TenantId="&B6&";CallbackUrl="&B7&";Provider=API",B9)


3. Change the filter to change the data.