Excel Spreadsheet Automation on Google Translate Data with the QUERY Formula

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

  Authentication

  Google Cloud Translation API requires OAuth 2.0 authentication to ensure secure access to translation services, datasets, glossaries, and adaptive MT resources. This authentication method allows you to securely connect to your Google Cloud project and manage translation resources with proper authorization.

  OAuth 2.0 Setup and Configuration

  Step 1: Create Google Cloud Project and Enable API

  To set up OAuth authentication:

  1. Visit the Google Cloud Console
  2. Create a new project or select an existing project
  3. Note down your Project ID (required for all API calls)
  4. Navigate to "APIs & Services" > "Library"
  5. Search for and enable the "Cloud Translation API"
  6. Go to "APIs & Services" > "Credentials"
  7. Click "Create Credentials" and select "OAuth Client ID"
  8. Configure the OAuth consent screen if prompted
  9. Select "Desktop application" or "Web application" as appropriate
  10. Set the authorized redirect URI (CallbackURL)
  11. Copy the Client ID and Client Secret for use in your connection

  Required Connection Properties

  • AuthScheme: Set this to OAuth (required)
  • OAuthClientId: Client ID from Google Cloud Console (required)
  • OAuthClientSecret: Client secret from Google Cloud Console (required)
  • CallbackURL: Redirect URI specified in your OAuth application (required)
  • InitiateOAuth: Set to GETANDREFRESH for automatic token management (recommended)
  • ProjectId: Your Google Cloud project ID or project number (required for queries)

  Required OAuth Scopes

  The Google Cloud Translation API Profile requires the following OAuth scope:

  • https://www.googleapis.com/auth/cloud-translation - Full access to Cloud Translation API resources including translation, datasets, glossaries, and adaptive MT
• 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 Google Translate data, such as ProjectId.
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 SupportedLanguages WHERE ProjectId = '"&B7&"'","Profile="&B1&";AuthScheme="&B2&";InitiateOAuth="&B3&";OAuthClientId="&B4&";OAuthClientSecret="&B5&";CallbackUrl="&B6&";Provider=API",B8)


3. Change the filter to change the data.