I am a beginner in knime, anyone can guide me about a workflow that can help me to read and merge multiple files (50-60) from google drive (Google sheets).
attach is the workflow img im working on, but its only reading one file which is being selected in google sheet reader config (img attach as well), it seems like the reader file is not picking flow variable containing the spreadsheet names (Not overwriting the names)
what im missing here? or do i have to follow some other workflow in order to achieve what i want!
Hi @shoukat1720 and welcome to the community.
Not sure why the screenshot of your workflow didn’t post, but here it is for clarity:
Anyway, I think the problem stems from you trying to connect to two different places: Google Drive and Google Sheets. These are actually different repositories for your files. We have a couple of tickets in our system to improve how the Google Sheets functionality works - one for more closely integrating with Google Drive (AP-11391) and another for improving the ability to search/filter Google Sheets (AP-16357). These haven’t yet been implemented, so let me ask internally to see if we have a workaround for this.
(If you do happen to know in advance the sheets you want to loop over, you could create a table of the Google Sheet names and in theory that should work. But I realize this is not optimal for many cases.)
thank you for your kind response, i suppose i have to wait for the solution
the mentioned looping over by table solution might not be the best case because sheets might change in future
Hi @shoukat1720 ,
You need to provide “spreadsheetId” instead of the “spreadsheetName” in the flow variables tab to make the node work as desired.
It seems that the Google Sheet Reader node populates the sheet ID only when you select a file manually and it doesn’t update it when only the sheet name is provided via flow variables.
But if the user populate the ID in the flow variables instead of the sheet name, it works fine.
I couldn’t find a straightforward way to get a list of sheet names with their IDs in KNIME. So, to me it seems impossible to loop over sheet names and get the files.