Google sheets Reader node read automatically a google spreadsheet file from Google Drive?

KNIME Analytics Platform
1

Dear all,

I would like to know if it possible to read automatically google spreadsheet file using google sheets Reader without any interaction of the third party.
Mean if have a google spreadsheet in my google drive read it automatically no need to select the file inside the node.
looking for your suggestion. Thanks

Best Regards

2

Hi @Mokrani

There is no node to list the files in Google Drive (yet), but if you want to realize this now, you should be able to do so with a workaround using REST nodes.

Authentication works like in this blog post, it describes how to access Google Sheets through using the REST nodes. You would have to adapt it to use the Google Drive API to get a list of your Files in Google Drive.

… I modified the workflow from the blog post a bit, and made it list the files sorted by modifiedTime. Then I filtered for Google Spreadsheets. The API has a lot more to offer, see Google Drive API (Especially: The list part). The results from that query can be fed in to the Google Sheets Reader node as flow variable.

See: google_sheet_and_drive_rest_api.knwf (254.3 KB)

3

@oole thank you for the suggestion I configure the node Post Request Wizard like the description in the website but when I execute the node POST Request I received this error “Execute failed: Duplicate column name “body” at positions 0 and 3.” what does mean?
Thanks

4

That happened to me as well on the first try, it is a little confusing, but I can walk you through it:

On the first try you will need to deselect Refresh token. After the first successful authentication you will receive a Refresh Token, which you can input to the Authorization code/ Refresh token field and select Refresh token. You will get the Refresh Token from the JSON Processing metanode.

Some other things:

Did you find the Client Secret? (It is hidden in the JSON which you can download from the credentials page)

Also did you enable the Google Drive API for these credentials in the Google Admin panel?

I also wanted to attach the proper URL for the authentication (using the necessary scope), here it is, remember to set the Client ID:

https://accounts.google.com/o/oauth2/v2/auth?client_id=<Your_Client_ID>&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code &scope=https://www.googleapis.com/auth/drive.metadata.readonly

1 Like
5

@oole yes I tired I grant the project of Google API the access to my google drive and I got the authorization code. I Enable the google drive API but the same problem:-(.
Take a look my screenShot

screen%20shoot%20of%20GS%20and%20D_rest_api
screen%20shoot%20of%20GS%20and%20D_rest_api1632Ă—431 30.9 KB

there is some configuration need to implement it in Post Request Node ?
Best Regards

6

Did you remember to deselect the refresh token in the dialog? Try authorizing again with a new Authorization Code it worked for me.

image

7

yes i tired also i add another node Post i configure it like the other one but the same error :frowning:
also I enable Google Drive API and google sheets API both and generate new code authorization

Untitled
Untitled1079Ă—905 53.8 KB

8

Okay :thinking:, I will give it another go and describe every step (in a lot of detail for future reference):

How to authenticate and use the REST nodes for Google Drive using this workflow: google_sheet_and_drive_rest_api.knwf (255.2 KB)
EDIT: Correct workflow: google_sheet_and_drive_rest_api.knwf (255.2 KB)

  1. Create a new project in Google APIs
  2. Activate the Google Drive API here
  3. Create an OAuth2 client here
  4. Grab the JSON for that client using the little download button on the far right side (look for this:image)
  5. From the JSON get the necessary credentials for the authentication metanode. These are the client_id and the client_secret.
  6. Put the client_id into this URL:
    https://accounts.google.com/o/oauth2/v2/auth?client_id=<Your_Client_ID>&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code &scope=https://www.googleapis.com/auth/drive.metadata.readonly
  7. Copy this URL to the browser of your choice and follow the instructions
  8. Copy the authentication token to you authentication metandode.
  9. Make sure that Refresh token is not selected.
  10. You should now be able to execute the first three nodes.
  11. From the third node you will receive the refresh token through the output table.
  12. Copy the refresh token to the first metanode and select Refresh token. This way your authentication will renew as long as you don’t revoke access to your account via the Connected Apps Settings
  13. Now the workflow should be executable.

This might be way to detailed, but if others have the same problem this will help them as well. I really hope it works now, I tried this procedure multiple times and it worked for me.

4 Likes
10

Excuse me for deleting your post @Mokrani, I did it for your own safety, sharing access tokens is not a good idea :slightly_smiling_face:

It is because I forgot to include the important step of actually creating the correct request URL. It is fixed here:google_sheet_and_drive_rest_api.knwf (255.2 KB)

1 Like
11

Thank very you mcuh ! you are a legend!

1 Like
12

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.