Need Help Connecting Google Sheets Reader Node

[Haystack]

Hi All,

I am trying to connect to Google Sheets using the Google Authentication (API Key) + Google Sheets Connection + Google Sheets Reader nodes as shown below. I have also created a service account and enabled Google Sheets API, but still get the error: “Could not retrieve spreadsheets. Check connection.”

• I have set up the Google Authentication (API Key), and enabled the appropriate scopes:

• I created a services account and P12 key, but no OAuth as indicated in the node description:

Can someone please tell me what I am doing wrong? Do I need to create an OAuth client ID too? If so, how?

Thank you for your help!

-Haystack

[Haystack]

Has anyone successfully connected the Google Sheets Reader node? Can you tell me how you did it?

Thanks,
Haystack

[ipazin]

Hi there,

I have connected using Google Authentication node - not API Key if it helps

Br,
Ivan

[oole]

Hi everyone,

The easiest way to get your Google Sheets into KNIME is definitively by using the Google Authentication node, which @ipazin mentioned.

It is, however, also possible to use the Google Authentication (API Key) node.

• First, to solve your problem when trying to select a spreadsheet, make sure that you also have the Google Drive API enabled for you project in Google’s developer console. Listing the files in Google Drive requires access to that API, since the Google Sheets API only handles spreadsheets.

• Second, in order to have the spreadsheets from your private account available to your service account, you have to share your private sheets with your service account’s email address. You can do that in browser, as if you would share it with another person. (top right corner)
  The service account really is a separate account to your own Google account.

I hope this helps, if you have any further questions, please ask!

[Haystack]

Hi @oole,

Thanks for your detailed instructions. I shared one sheet from my private account to my service account using the “share” button:

I added the Sheets scope to my service account in the Google Authentication (API Key) node:

But, I still get the ‘Could not retrieve spreadsheets’ error:

I have confirmed that I have the Google Sheets API enabled:

What else am I missing? I would prefer to use the API Key node, as I would rather not share access with another platform and prefer to connect with my credentials instead. Thank you.

• Haystack

[oole]

The scopes you used where fine. You need at least Sheets drive and Sheets scopes.

And don’t forget to also activate the Google Drive API in your Google developer console .

[Haystack]

@oole, Thank you so much! I added the Google Drive API and it worked brilliantly! Thank you!!!

[Haystack]

Hi @oole,

One other question: when writing a Google Sheet using the Google Sheets Writer node, what is the correct process to follow?

I am using the exact set up as before and successfully writing, but because this is a Service Account, I am being asked to request access from the Service Account to which the sheet was written to:

It’s not clear how to do this. How can I write the sheet to my private account instead?

Thank you!