Getting data from multiple google sheets in one query

[Alex_Pierse]

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?

[Alex_Pierse · Accepted solution]

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 script

import pandas as pd
import glob

from 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

[v-cgao-msft]

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 FOLDER

Google Drive Power BI Connector

Best Regards,
Gao
Community Support Team

If 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

[Alex_Pierse]

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.