topic tGoogleDriveGet can't download Google Spreadsheet? in Talend Studio

tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Fri, 30 Oct 2015 17:06:43 GMT

Hello,
I have been using talend for some time and I am usually able to find an answer to any question I have in these forums. However I could not find a solution to my problem which has lead me to create my very first post.
I have successfully made a connection and I am able to download files using tGoogleDriveGet such as pdf files or image files.. however I cannot download the Google spreadsheet that is generated from a Google form.
tGoogleDriveConnection ---> tGoogleDriveGet
Name of File: "QA Form (Responses)"
Save To: "/Users/me/Desktop/QA Form (Responses)"
Prefer to download in CSV format. Any assistance appreciated.
Thanks so much.
Al

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 03 Nov 2015 08:12:50 GMT

Hi,
Did you get any error when you download the Google spreadsheet by using tGoogleDriverGet?
We have a related new feature jira issue: https://jira.talendforge.org/browse/TDI-28114
In addition, here is a custom component tGSpreadsheetInput shared on Talend Exchange
TalendExchange:tGSpreadsheetInput
Best regards
Sabrina

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 03 Nov 2015 17:50:48 GMT

Hello Sabrina,
Apologies for not being specific.
I have tried tGSpreadsheetInput however I receive an error here regarding Authentication. I believe it may be due to oAuth2
Exception in component tGSpreadsheetInput_1
com.google.gdata.util.AuthenticationException: Error authenticating (check service name)
at com.google.gdata.client.GoogleAuthTokenFactory.getAuthException(GoogleAuthTokenFactory.java:553)
at com.google.gdata.client.GoogleAuthTokenFactory.getAuthToken(GoogleAuthTokenFactory.java:430)
at com.google.gdata.client.GoogleAuthTokenFactory.setUserCredentials(GoogleAuthTokenFactory.java:301)
at com.google.gdata.client.GoogleService.setUserCredentials(GoogleService.java:324)
at com.google.gdata.client.GoogleService.setUserCredentials(GoogleService.java:301)
at bd.testzone_0_1.testzone.tGSpreadsheetInput_1Process(testzone.java:805)
at bd.testzone_0_1.testzone.runJobInTOS(testzone.java:1292)
at bd.testzone_0_1.testzone.main(testzone.java:1141)
Here is the error tGoogleDriveGet I receive when I attempt to load the Name of File as: "QA Form (Responses)"
Exception in component tGoogleDriveGet_2
java.lang.IllegalArgumentException: java.net.MalformedURLException
at com.google.api.client.http.GenericUrl.parseURL(GenericUrl.java:638)
at com.google.api.client.http.GenericUrl.(GenericUrl.java:114)
at bd.testzone_0_1.testzone.tGoogleDriveGet_2Process(testzone.java:524)
at bd.testzone_0_1.testzone.tGoogleDriveConnection_1Process(testzone.java:429)
at bd.testzone_0_1.testzone.runJobInTOS(testzone.java:899)
at bd.testzone_0_1.testzone.main(testzone.java:748)
Caused by: java.net.MalformedURLException
at java.net.URL.(URL.java:619)
at java.net.URL.(URL.java:482)
at java.net.URL.(URL.java:431)
at com.google.api.client.http.GenericUrl.parseURL(GenericUrl.java:636)
... 5 more
Caused by: java.lang.NullPointerException
at java.net.URL.(URL.java:524)
... 8 more

Thanks so much for your assistance!
Al

edit:
when I print out to the console a tGoogleDriveList results, I can see all the pertaining documents in the google drive. Here I can see that talend is able to detect and list all the contents of the folder, including the QA form (google form) and the QA form responses (google worksheet) :
TGoogleDriveList global variables:
tGoogleDriveList_1_ERROR_MESSAGE: null
tGoogleDriveList_1_ID: 144HWZMpTVu5555555555naBR-npCUkhlXTJA
tGoogleDriveList_1_LAST_MODIFIED: 2015-10-29T20:35:57.263Z
tGoogleDriveList_1_MIME_TYPE: application/vnd.google-apps.spreadsheet
tGoogleDriveList_1_SELF_LINK: https://www.googleapis.com/drive/v2/files/144HWZMpTVuzU555555555BR-npCUkhlXTJA
tGoogleDriveList_1_TITLE: QA Form (Responses)

TGoogleDriveList global variables:
tGoogleDriveList_1_ERROR_MESSAGE: null
tGoogleDriveList_1_ID: 1nEhtGqhxF8Iyz55555555555EPWtEHC9A
tGoogleDriveList_1_LAST_MODIFIED: 2015-10-27T22:06:43.955Z
tGoogleDriveList_1_MIME_TYPE: application/vnd.google-apps.form
tGoogleDriveList_1_SELF_LINK: https://www.googleapis.com/drive/v2/files/1nEhtGqhxF8I5555555pLUEPWtEHC9A
tGoogleDriveList_1_TITLE: QA Form

TGoogleDriveList global variables:
tGoogleDriveList_1_ERROR_MESSAGE: null
tGoogleDriveList_1_ID: 0B5o655555555555lcl9maWxl
tGoogleDriveList_1_LAST_MODIFIED: 2014-08-13T22:32:12.144Z
tGoogleDriveList_1_MIME_TYPE: application/pdf
tGoogleDriveList_1_SELF_LINK: https://www.googleapis.com/drive/v2/files/0B5o6HAN555555555lcl9maWxl
tGoogleDriveList_1_TITLE: How to get started with Drive

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Mon, 09 Nov 2015 19:08:50 GMT

Hello,
Just curious if you had any follow up for me.
Thanks,
Al

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 10 Nov 2015 09:26:37 GMT

Hi Al,
Sorry for delay.
On which official version did you get that?
Best regards
Sabrina

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Wed, 11 Nov 2015 17:32:31 GMT

No problem. The version is: Talend Open Studio for Big Data (6.0.1.20150908_1633)
Al

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Mon, 16 Nov 2015 21:01:04 GMT

Hello,
I am still struggling with this issue.
I am sure there must be a way to download contents of a Google Sheet using Talend.
Thanks,
Al

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 17 Nov 2015 02:52:59 GMT

Hi,
Would you mind creating a work item jira issue of DI project on Talend Bug Tracker?
https://jira.talendforge.org/secure/Dashboard.jspa
Best regards
Sabrina

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 17 Nov 2015 15:51:02 GMT

Hi Alvin, Sabrina
I got exactly the same issue. I however can connect to google drive with the tgoogledrivelist component but the tgoogledriveget doesn't work - same log errors than you !
Note that I tried to do a save-as file with a prepared xls file and this is not working as well.
There aren't many configuration on this component and if the API is working I'm wondering if it has ever been working !
Regards,
Mehdi

Re: tGoogleDriveGet can't download Google Spreadsheet?

_AnonymousUser — Thu, 19 Nov 2015 12:30:01 GMT

Hello,
I have the same problem, any updates? news?
Regards

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Fri, 20 Nov 2015 02:16:25 GMT

Seems my current credentials don't work in that JIRA?

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Mon, 23 Nov 2015 11:35:42 GMT

FYI , I've created a Jira issue ticket
https://jira.talendforge.org/browse/TBD-2858

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 24 Nov 2015 12:34:31 GMT

Hello,
First I want to say that for now tGoogleDriveGet component works only with files created
outside of Google Drive. I mean we do not support download of Google Docs, Google Sheets and etc.
There is no way to just download them as is, I as user should tell in which format I want to download
this spreadhseet (pdf, cxv, xlsx). This is not implemented yet.

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 24 Nov 2015 22:37:31 GMT

imelnik,
Thanks for the response. Based on your experience, is there anyway to work around this limitation? Is this on the roadmap to be implemented?
Thanks,
Al

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Wed, 25 Nov 2015 06:52:52 GMT

Afaik no. To download "Google type" files you should convert them to some format like xlsx, csv, and so on and then
you will be able to download it. About roadmap - afaik no, but you can create "New feature" request.
https://jira.talendforge.org/secure/Dashboard.jspa
Regards,
Ilya

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Mon, 07 Dec 2015 19:52:21 GMT

Hello All,
I was able to download a CSV of the google spreadsheet using tFileFetch with the following string in the URI field: "https://docsgooglecom/spreadsheets/d/LONG_ID_STRING/export?format=csv&id=LONG_ID_STRING"
Replace LONG_ID_STRING with the google spreadsheet ID. You will see it in the url when you open it. Also replace the with . since the forum will not allow me to post a url.
Al

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Mon, 21 Dec 2015 12:25:56 GMT

Hi alvin.kumar, I'm interested in your solution that uses tFileFeatch,
how do you do then auth2 to google drive prior to do the file featch ?
Can you better detailing your solution?
Thank you in advance
ML

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Tue, 05 Jan 2016 22:23:21 GMT

maslopo:
No need for authorization with tFileFetch. I'll try to write a simple tutorial:
1) Go to google drive, open the doc, in the top right section there is a SHARE button. Click that and change Link sharing to "Anyone with the link can view". This is a URL that will give access to view, to anyone that has the URL.
2) Create a tFileFetch component and input the URL except you must edit the url to contain " export?format=csv"
(Example: https://docsgooglecom/spreadsheets/d/LONG_ID_STRING/export?format=csv&id=LONG_ID_STRING ) Change to . and change LONG_ID_STRING to the ID in the share URL--it will be an alphanumerical number--mine was about 44 digits.
My plan is to simply download the file then read it later. A little bit crude but it works.

Re: tGoogleDriveGet can't download Google Spreadsheet?

Anonymous — Wed, 27 Jan 2016 20:45:00 GMT

maslopo:
No need for authorization with tFileFetch. I'll try to write a simple tutorial:
1) Go to google drive, open the doc, in the top right section there is a SHARE button. Click that and change Link sharing to "Anyone with the link can view". This is a URL that will give access to view, to anyone that has the URL.
2) Create a tFileFetch component and input the URL except you must edit the url to contain "export?format=csv"
(Example: https://docsgooglecom/spreadsheets/d/LONG_ID_STRING/export?format=csv&id=LONG_ID_STRING ) Change to . and change LONG_ID_STRING to the ID in the share URL--it will be an alphanumerical number--mine was about 44 digits.
My plan is to simply download the file then read it later. A little bit crude but it works.

Great! It works, Thanks alvin.kumar
for more information

CSV file :

https://docs.google.com/spreadsheets/d/<KEY>/export?gid=0&format=csv</KEY>

the gid=0 0 means the 1st sheet, and replace the number that which sheet you want download

XIANG

Re: tGoogleDriveGet can't download Google Spreadsheet?

_AnonymousUser — Mon, 19 Sep 2016 09:50:28 GMT

Hi there,
I could download the csv using tFileFetch but the downloaded file contains HTML text. I did follow the same step. I could download it while copy and pasting the API URL in the browser. But the same URL from tFileFetch did not provide original file, it has HTML text. I tried to add header from tFileFetch step, even though no luck.
API URL I used : <ID>/export?format=csv&id=<ID>
Kindly help on this.
Thanks