One Star

tGoogleDriveGet can't download Google Spreadsheet?

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

22 REPLIES
Moderator

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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

Re: tGoogleDriveGet can't download Google Spreadsheet?

Hi Al,
Sorry for delay.
On which official version did you get that?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
Moderator

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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

Re: tGoogleDriveGet can't download Google Spreadsheet?

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

Re: tGoogleDriveGet can't download Google Spreadsheet?

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

Re: tGoogleDriveGet can't download Google Spreadsheet?

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.
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
Employee

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
Four Stars

Re: tGoogleDriveGet can't download Google Spreadsheet?

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
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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.
Four Stars

Re: tGoogleDriveGet can't download Google Spreadsheet?

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//export?gid=0&format=csv

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

XIANG
One Star

Re: tGoogleDriveGet can't download Google Spreadsheet?

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 : /export?format=csv&id=
Kindly help on this.
Thanks
Five Stars

Re: tGoogleDriveGet can't download Google Spreadsheet?

Hi,
My CSV File is also showing up in HTML format.
Thanks,
Five Stars

Re: tGoogleDriveGet can't download Google Spreadsheet?

Can anyone provide any sort of insights on why CSV files are showing up in HTML format? 
Moderator

Re: tGoogleDriveGet can't download Google Spreadsheet?

Hi dudedoe01,
We have replied to your another topic:https://www.talendforge.org/forum/viewtopic.php?id=53748.
Could you please take a look at it?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.