Combine Google sheets data stored on Google drive in dataflow

[Katr-PBI]

Hi,

I need to connect to google drive folder in which each day is saved new daily report in Google sheets format.

I need one sheet from this Google sheets from each day, but I need to combine them in order to have the full view.

It is simple to do with Sharepoint, but is there any work around how to do it when it is stored on Google drive, and the format of the files is Google sheets?

Thanks a lot in advance 

[danextian · Accepted solution]

Hi @Katr-PBI 

Power BI doesn't have a Google Drive connector.  What you can do is maintain a single spreadsheet containing the link to the other spreadsheets. Connect to the main spreadsheet then create a custom column that connects to each individual link. The custom column  formula would be something link

= GoogleSheets.Contents([spreadsheet url column)

[v-prasare]

May I ask if you have resolved this issue? If so, Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.

[v-prasare]

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

HI @Katr-PBI,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Shahid12523& @danextian, thans for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are

MS Fabric community support

[Shahid12523]

To combine daily Google Sheets from a Google Drive folder into one view using Power BI Dataflow:
- Create a list of the Google Sheets URLs (one per daily report).
- Build a Power Query function to fetch the needed sheet from each URL.
- Apply this function to the URL list to combine all data.
- Update the URL list regularly to include new daily files.
Alternatively, third-party connectors can simplify this process by treating Google Drive like SharePoint.

[danextian · Accepted solution]

This is incorrect. The Google Sheets connector either in Dataflow or Desktop connects to the Google Sheets only and there is no such option to connect to a Google Drive folder.. 

[danextian · Accepted solution]

Hi @Katr-PBI 

Power BI doesn't have a Google Drive connector.  What you can do is maintain a single spreadsheet containing the link to the other spreadsheets. Connect to the main spreadsheet then create a custom column that connects to each individual link. The custom column  formula would be something link

= GoogleSheets.Contents([spreadsheet url column)