2

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:

  1. create a folder with my spread sheets (.xlsx files) in a subfolder within this folder
  2. create a gcp project
  3. enable google drive api on the project
  4. enable google sheets api on the project
  5. create a service account on the project with a basic owner IAM role
  6. take the service key json and place it locally on my computer to reach using python
  7. 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.
  8. 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?

user4933
  • 1,485
  • 3
  • 24
  • 42
  • 1
    About `my spread sheets (.xlsx files)`, which are these Google Spreadsheet files or Microsoft Excel files? – Tanaike Jan 31 '23 at 04:39
  • Thank you @Tanaike, I did not realise that a google sheet is completely different format to an excel file. I thought that it would still pick up xlsx as a gsheet. You actually just answered my question. There is an additional step to convert xlsx to sheets. – user4933 Jan 31 '23 at 06:23
  • Thank you for replying. Unfortunately, it seems that `gc.list_spreadsheet_files()` returns only Google Spreadsheet. From your reply, for example, do you want to get the file list of both Google Spreadsheet filest and Microsoft Excel files? – Tanaike Jan 31 '23 at 06:27
  • Hi @Tanaike, Yes if its possible – user4933 Jan 31 '23 at 08:13
  • Thank you for replying. From your reply, I posted a sample script as an answer. Please confirm it. – Tanaike Jan 31 '23 at 12:00

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165