Google Spreadsheet Data Sources

Page edited by Steven Hawkins


Changes (8)

...
| {color:#333333}Password{color} | {color:#333333}Password to Google account. Applicable only if AuthMethod=ClientLogin{color} |
| {color:#333333}SpreadsheetName{color} | {color:#333333}Required property with name of the Spreadsheet that is datasource for this connector.{color} |
| {color:#333333}BatchSize{color} | {color:#333333}Integer that designates how big will be portions of data loaded by this JCA Connector. Defaults to 1000.{color} |

h2. {color:#000000}{*}Getting an OAuth Refresh Token{*}{color}
...
{color:#333333}Use this request:{color}

{color:#005e8f}[{color:#005e8f}https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive+https%3A%2F%2Fspreadsheets.google.com%2Ffeeds&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&client_id=217138521084.apps.googleusercontent.com{color}|https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive+https%3A%2F%2Fspreadsheets.google.com%2Ffeeds&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&client_id=217138521084.apps.googleusercontent.com]{color}
[https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive+https%3A%2F%2Fspreadsheets.google.com%2Ffeeds&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&client_id=217138521084.apps.googleusercontent.com]

{color:#333333} Then copy the authorization code into following POST request and run it in command line:{color}

{color:#333333}curl \--data-urlencode code=<AUTH_CODE> \{color}
{color:#333333}\--data-urlencode client_id=217138521084.apps.googleusercontent.com \{color}
{color:#333333}\--data-urlencode client_secret=gXQ6-lOkEjE1lVcz7giB4Poy \{color}
{color:#333333}\--data-urlencode redirect_uri=urn:ietf:wg:oauth:2.0:oob \{color}
{color:#333333}\--data-urlencode grant_type=authorization_code \[https://accounts.google.com/o/oauth2/token\]{color}
{code}curl \--data-urlencode code=<AUTH_CODE> \
--data-urlencode client_id=217138521084.apps.googleusercontent.com \
--data-urlencode client_secret=gXQ6-lOkEjE1lVcz7giB4Poy \
--data-urlencode redirect_uri=urn:ietf:wg:oauth:2.0:oob \
--data-urlencode grant_type=authorization_code https://accounts.google.com/o/oauth2/token{code}
{color:#333333}The refresh token will be in the response{color}

...

{color:#333333}Google Translator is implemented using GData API \[1\] (java library for \[4\]) and Google Visualization Protocol \[2\]. Authentication is implemented using OAuth2 for devices \[3\].{color}
# {color:#005e8f}[{color:#005e8f}https://developers.google.com/google-apps/spreadsheets/{color}|https://developers.google.com/google-apps/spreadsheets/]{color}
# {color:#005e8f}[{color:#005e8f}https://developers.google.com/chart/interactive/docs/querylanguage{color}|https://developers.google.com/chart/interactive/docs/querylanguage]{color}
# {color:#005e8f}[{color:#005e8f}https://developers.google.com/accounts/docs/OAuth2ForDevices{color}|https://developers.google.com/accounts/docs/OAuth2ForDevices]{color}
# {color:#005e8f}[{color:#005e8f}https://developers.google.com/google-apps/spreadsheets/{color}|https://developers.google.com/google-apps/spreadsheets/]{color}
1. [https://developers.google.com/google-apps/spreadsheets/]
2. [https://developers.google.com/chart/interactive/docs/querylanguage]
3. [https://developers.google.com/accounts/docs/OAuth2ForDevices]
4. [https://developers.google.com/google-apps/spreadsheets]

Full Content

Google Spreadsheet Data Sources

Authentication to your google account may be done in two ways. ClientLogin (requires login and password) or OAuth (more complicated, requires user to supply refresh token).

Google JCA connector is named teiid-connector-google.rar. The examples include a sample google.xml file. The JCA connector has number of config-properties to drive authentication.  The JCA connector connects to exactly one spreadsheet.

Config property Description
AuthMethod Method to access Google. This property can be set to one of: ClientLogin, OAuth2. If the setting is ClientLogin it is necessary to provide additional two config properties Username, Password. If the setting is OAuth2 it is necessary to provide RefreshToken.
RefreshToken Use guide below to retrieve RefreshToken. Request access to Google Drive and Spreadsheet API. Applicable only if AuthMethod=OAuth2
Username Username to Google account. Applicable only if AuthMethod=ClientLogin
Password Password to Google account. Applicable only if AuthMethod=ClientLogin
SpreadsheetName Required property with name of the Spreadsheet that is datasource for this connector.
BatchSize Integer that designates how big will be portions of data loaded by this JCA Connector. Defaults to 1000.

Getting an OAuth Refresh Token

Use this request:

https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive+https%3A%2F%2Fspreadsheets.google.com%2Ffeeds&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&client_id=217138521084.apps.googleusercontent.com

 Then copy the authorization code into following POST request and run it in command line:

curl \--data-urlencode code=<AUTH_CODE> \
--data-urlencode client_id=217138521084.apps.googleusercontent.com \
--data-urlencode client_secret=gXQ6-lOkEjE1lVcz7giB4Poy \
--data-urlencode redirect_uri=urn:ietf:wg:oauth:2.0:oob \
--data-urlencode grant_type=authorization_code https://accounts.google.com/o/oauth2/token

The refresh token will be in the response

Implementation Details

Google Translator is implemented using GData API [1] (java library for [4]) and Google Visualization Protocol [2]. Authentication is implemented using OAuth2 for devices [3].
1. https://developers.google.com/google-apps/spreadsheets/
2. https://developers.google.com/chart/interactive/docs/querylanguage
3. https://developers.google.com/accounts/docs/OAuth2ForDevices
4. https://developers.google.com/google-apps/spreadsheets

Stop watching space | Change email notification preferences
View Online | View Changes | Add Comment