google service account not finding .xlsx files-CodePudding

I am trying to use a google drive folder to store data then read and manipulate it with python using gspread. The steps I followed in order to try and do this are:

create a folder with my spread sheets (.xlsx files) in a subfolder within this folder
create a gcp project
enable google drive api on the project
enable google sheets api on the project
create a service account on the project with a basic owner IAM role
take the service key json and place it locally on my computer to reach using python
take the email address for the service key and share it with the main folder and data sets to allow the service key to have access.
run the following code to try and see a list of all spread sheets my service account has access to:

import gspread

# open credentials file and connect to google drive
gc = gspread.service_account(filename='credentials.json')

# list all spread sheets with access from credentials (returning empty list)
gc.list_spreadsheet_files()

My result is always the same, an empty list suggesting that my service account has access to no spread sheets, but when I look at my .xlsx files they all say that the service account email has been added as an editor.

What could be the purpose of this and what would a good solution be?

CodePudding user response：

I believe your goal is as follows.

Unfortunately, it seems that gc.list_spreadsheet_files() returns only Google Spreadsheet. So, in your expected goal, from your reply, I understood that you wanted to get the file list of both Google Spreadsheet files and Microsoft Excel files.

In this case, how about the following sample script?

Sample script:

import gspread
from googleapiclient.discovery import build

gc = gspread.service_account(filename='credentials.json')
service = build("drive", "v3", credentials=gc.auth)

fileList = {"spreadsheet": [], "excel": []}
pageToken = ""
while pageToken is not None:
    res = service.files().list(q="(mimeType='application/vnd.google-apps.spreadsheet' or mimeType='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') and trashed=false", fields="nextPageToken, files(id,name,mimeType)", pageSize=1000, pageToken=pageToken, corpora="allDrives", includeItemsFromAllDrives=True, supportsAllDrives=True).execute()
    for e in res.get("files", []):
        if e["mimeType"] == "application/vnd.google-apps.spreadsheet":
            del e["mimeType"]
            fileList["spreadsheet"].append(e)
        elif e["mimeType"] == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
            del e["mimeType"]
            fileList["excel"].append(e)
    pageToken = res.get("nextPageToken")

print(fileList)

Testing:

When this script is run, the following result is obtained.

{
  'spreadsheet': [{'id': '###', 'name': '###'}, {'id': '###', 'name': '###'},,,],
  'excel': [{'id': '###', 'name': '###'}, {'id': '###', 'name': '###'},,,]
}

By this, you can see the Google Spreadsheet files and the Microsoft Excel files.

Reference:

Files: list