Need Help Connecting Google Sheets Writer Node

Hi all,

I need help writing a Google Sheet using the Google Sheet Writer Node.

I am using the exact set up as in this thread and successfully reading the original sheet and adding some extra analysis in KNIME. Now, I want to take this output and write it to a new sheet in my personal account, 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:

How can I write the sheet to my private account instead and not the service account? Or, alternatively, how can I grant access from the service account?

@oole would you happen to know?

Thank you!

Hi;
If you want to take a look at this link, maybe give you an idea. Right now I’m exactly what you want to do

Thank you @umutcankurt.

But your image shows the reader and updater/appender nodes. I am trying to use the writer node, but I can’t find any example workflows on it.

Thank you,
Haystack

Hi @Haystack

I can finally get back to you, you were in fact not the only one having that problem. I finally have a solution. It’s using a Java Snippet node, using the service account mail and the P12 key to share the spreadsheet with write permissions. This could also be changed to read only permissions.

Let me know if you need that, the workflow could be extended to handle that nicely using the String Configuration node

The workflow is here: Share Spreadsheet To Account

Make sure to configure the first component, so that it reflects your service account credentials. (To see the inner workings hold Ctrl + double-click the component).

3 Likes

@oole,

This is great! Thank you for building this solution!

Questions:

  1. Do you need to enter the emails to share with in both the Table Creator AND the ‘Share Spreadsheet with ONE user’? The latter is breaking on runs.
  2. Can you share a little about what the Java Snippet is doing and what the initial problem was with the Google Sheets Writer node? Just curious about the solution.

Thank you!
Haystack

You are very welcome, @Haystack !

  1. I forgot to pass the appropriate variables to the Share Spreadsheet With one Email component. I fixed it, if you download the latest version from the hub it should work.
    The Top Java Snippet and the bottom component actually do the same thing. I wanted to provide an example of how to share the spreadsheet with one or with multiple Google accounts.

  2. The Java Snippet and the component containing the Java Snippet for one user do the same thing. They use the credentials provided (Service Account email and P12 key) to create a Google Drive Service which is used to alter the permissions of the written spreadsheet, namely sharing it with either the provided list or single user.

Initially there is nothing wrong with the Google Sheets Writer node, but since you are working with a service account, the spreadsheet is written/stored in the service account’s Google Drive, to which you don’t have access. The service account acts as an individual user with its own Google Drive. The solution is to share the Spreadsheet, just like you would share one of your own spreadsheets with another Google user.

I hope this helps for understanding the inner workings! If you need more explanation/help, let me know!

1 Like

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