Forum Discussion
Getting data from multiple google sheets in one query
Hello community)
Before me was the task of getting data from 10 Google tables that are in the same folder on Google Drive. Unfortunately, there is no technical possibility to deoat this through the Google API and to allocate a server for this, as a connector. Power Bi does not read the Google folder link and does not see all the tables if the links are inserted all together into the source, it takes only the first one and that's it.
Tell me how to solve this moment, how to get access to all Google spreadsheets in a folder in one source request?
Hello. The problem was solved a little differently. Below I throw off everything that I could find on the Internet. Connectors are not all adequate, and do not work as you want.
The problem was solved through the Google API and a Python script. Below I throw off 2 links to Russian-language videos with a solution to the problem. But his script is a little tricky and a lot of superfluous,
a little reworking, we get a good result
Google Drive
Google Sheets
My example working state python scriptimport pandas as pd
import globfrom pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from oauth2client.service_account import ServiceAccountCredentials
gauth = GoogleAuth()
scope = ["https://www.googleapis.com/auth/drive"]gauth.credentials = ServiceAccountCredentials.from_json_keyfile_name(r"Let to the key file API Google", scope)
drive = GoogleDrive(gauth)
folder_id = ['ID Google Folder ']
for files in folder_id:
file_list = drive.ListFile({'q': "'{}' in parents and trashed=false".format(files)}).GetList()
for file in file_list:
test_id=file['id']
test_title=file['title']
mime='application/vnd.google-apps.spreadsheet'
if mime in file.metadata['mimeType']:
test_file=drive.CreateFile({'id': test_id })
content=test_file.GetContentFile(fr'C:\Users\**\Desktop\xls\{test_title}.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
path = r"C:\Users\**\Desktop\xls"
file_list = glob.glob(path + "/*.xlsx")
excl_list = []
for file in file_list:
excl_list.append(pd.read_excel(file))
excl_merged = pd.concat(excl_list, ignore_index=True)
excl_merged.to_excel(r'C:\Users\**\Desktop\combined.xlsx')
The script version is dry, but since I'm not a programmer, this is a hint for you where to look for the convenience of work.
I worked through Anaconda. And yes, PowerBi needs to be run through Anaconda, because you will have to download a bunch of packages like pandas
3 Replies
Hi Alex_Pierse ,
You may consider using web connector(work with 'rest API'/'web service'), other connector drivers (e.g odbc driver) or create a custom connector to achieve your requirement.
Please refer to:
Connecting to Google Drive FOLDERGoogle Drive Power BI Connector
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Good afternoon
Unfortunately, it does not work according to your advice and other articles that I read.
To implement a connector to Google Drive, you need to be able to understand the Google API, which, unfortunately, cannot be done.
If you have any other tips to solve this situation, I would be glad to help.Let me remind you the task:
There is a folder on Google Disk with 100+ Google Tabs..
You need to upload data from these tables with One request, and refuse to upload from Google Disk to your local PC.
Make it automatic so that it can be updated with all the data as it fills up.
Hello. The problem was solved a little differently. Below I throw off everything that I could find on the Internet. Connectors are not all adequate, and do not work as you want.
The problem was solved through the Google API and a Python script. Below I throw off 2 links to Russian-language videos with a solution to the problem. But his script is a little tricky and a lot of superfluous,
a little reworking, we get a good result
Google Drive
Google Sheets
My example working state python scriptimport pandas as pd
import globfrom pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from oauth2client.service_account import ServiceAccountCredentials
gauth = GoogleAuth()
scope = ["https://www.googleapis.com/auth/drive"]gauth.credentials = ServiceAccountCredentials.from_json_keyfile_name(r"Let to the key file API Google", scope)
drive = GoogleDrive(gauth)
folder_id = ['ID Google Folder ']
for files in folder_id:
file_list = drive.ListFile({'q': "'{}' in parents and trashed=false".format(files)}).GetList()
for file in file_list:
test_id=file['id']
test_title=file['title']
mime='application/vnd.google-apps.spreadsheet'
if mime in file.metadata['mimeType']:
test_file=drive.CreateFile({'id': test_id })
content=test_file.GetContentFile(fr'C:\Users\**\Desktop\xls\{test_title}.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
path = r"C:\Users\**\Desktop\xls"
file_list = glob.glob(path + "/*.xlsx")
excl_list = []
for file in file_list:
excl_list.append(pd.read_excel(file))
excl_merged = pd.concat(excl_list, ignore_index=True)
excl_merged.to_excel(r'C:\Users\**\Desktop\combined.xlsx')
The script version is dry, but since I'm not a programmer, this is a hint for you where to look for the convenience of work.
I worked through Anaconda. And yes, PowerBi needs to be run through Anaconda, because you will have to download a bunch of packages like pandas
