I'm using the Google Sheets query component to move sheets into Snowflake and would like to retrieve the folder on Google Drive that the sheet resides in. The Spreadsheets data source correctly pulls all of the file names but does not show any sort of folder hierarchy despite the files residing in different folders in Google Drive.
Example: I have folder A and folder B on google drive. I would like the job to grab all sheet names out of folder A and not folder B or vice versa.
Is there any way to get this information through the component? If not, could I achieve this with a more complex and explicit scripting function? I couldn't seem to find this information in the data model either.
1 Community Answers
Dan D'Orazio —
Hi Chris -
I think the gap that you’re noticing comes from the components use of the Google Sheets API, as opposed to Google Drive. I’ve taken a quick glance at the Google Sheets API documentation and can’t find any reference to its folder location in Drive. If you’re logged into your Google Account and you choose Sheets from the google apps menu in Chrome, I think you’ll see what I mean. In this interface, you just have a list of sheets that exist in your account, which is essentially the view you get from the Google Sheets Query Component.
You may be able to script this using the Google Drive API, and some Python, to get the list of files that you’re interested in. If you can do that, then you could use one of Matillion’s iterators, attached to the Google Sheets Query Component, to iterate over that list and supply the sheet name to the component.