Home > Software engineering >  find all sheet id in a drive
find all sheet id in a drive

Time:06-08

In their documentation, google show the example of how to read a sheet using their API. it requires you to manually find the google sheet ID first from the url. However, I would like to just give a google drive directory, and find the sheet id of all the google sheets in that drive. How can I do that?

Code tried:

from google.oauth2 import service_account
from googleapiclient.discovery import build


sheetMymeType = "application/vnd.google-apps.spreadsheet"
parent = PARENTFOLDER_ID

SERVICE_ACCOUNT_FILE = 'keys.json'
SCOPES = ['https://www.googleapis.com/auth/drive.readonly']

credentials = None
credentials = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

drive = build('drive', 'v3', credentials=credentials)

q = "mimeType = '{}' and parents = '{}'".format(sheetMymeType, parent)
list = drive.files().list(q=q, fields='files(id)').execute()
print(list)

CodePudding user response:

In your case you need to use the Google Drive API, in specific the Files.list method.

As you can see in the documentation for Search files and folders you can supply a q parameter in order to search for a determined type of file. As your goal is give a google drive directory, and find the sheet id of all the google sheets in that drive you need to use two query terms:

  • mimeType in your case application/vnd.google-apps.spreadsheet (you can review the full list of Google Workspace and Drive MIME Types here)
  • parent the ID of the folder that you want to use.

And, as you need to retrieve those who accomplish the two requeriments you need to use the query operator and

sample.py
def get_all_sheets_in_folder():
    drive = build('drive', 'v3', credentials=creds)
    sheetMymeType = "application/vnd.google-apps.spreadsheet"
    parent = 'SOME_FOLDER_ID'
    q = "mimeType = '{}' and parents in '{}'".format(sheetMymeType, parent)
    list = drive.files().list(q=q, fields='files(id)').execute()
    print(list)

I recommend you review the quickstart.py in order to know how to set up your project.

  • Related