Google Sheets Reader, Writer, Appender, Updater only sees one sheet and unable to see others or change the selection

[alabamian2]

Hi all,
I have Google Authentication API Key and Google Sheets Connection working and I was able to first test Google Sheets Reader/Writer/Appender/Updater with one test Google sheet. Now I wanted to point to other sheets and the selector only shows the first test sheet I had selected originally. I cannot type over. There is no fetch button like in DB nodes. I tried resetting, deleting, putting a new node and they all show that one sheet. I couldn’t find other posts about this issue.

Thank you very much for your help in advance.

[tommy]

Hi @alabamian2,

the way I access data from Google Spreadsheets looks like…

I think, you got this already working for your first file.

Just to clarify, Spreadsheet is the file itself points to the filename in your Google Drive. Sheet is the tab/page within the spreadsheet (a spreadsheet contains one ore more sheets).

To access a spreadsheet from your Google Drive you have to mark it as favorite or you have to be the owner/creator. You’re not able to navigate through your Gdrive folder structure. The list shows all the favorites and own files sorted by the last modification DESC.

Hope, that helps, greetz, Tommy

[alabamian2]

Hi @tommy,
Thanks for the reply. Yes, I followed your check points. I realized I forgot one step that I did with the first one, which was to share the spreadsheet with the service account. Sharing with the services account made the files to appear in the nodes so I now see more than one.
Thank you so much for your help and happy (no-)coding and KNIME!!

[alabamian2]

Hi @tommy,
My next task is to go a series of transformation and manipulation within Google Spreadsheet. Have you done that? I saw in the documentation (https://hub.knime.com/knime/extensions/org.knime.features.google.api/latest/org.knime.google.api.sheets.nodes.sheetupdater.GoogleSheetUpdaterFactory) that there are many transformation node options. Can we do basically most things we can do in Excel using KNIME or functionality with Google Sheets somewhat limited compared to what you can do to Excel?

Thank you so much for sharing your expertise!!

[tommy]

hi @alabamian2,

KNIME as a data mining tool is extremely powerful doing transformations and manipulations.
So the common way to use KNIME in combination with Google Spreadsheet is start with the reader at the beginning of your KNIME workflow and end with the writer/updater/appender…

In between you are free to use any KNIME nodes performing your operations.
My use case with Google Spreadsheets was combining data from different sheets and other (web) data sources. At the end I save my final table back to Google Drive as a Spreadsheet.

Hope that helps, br, Tommy

[alabamian2]

Thank you, @tommy. Yes, that makes total sense. Thank you.