Get list of all google sheets using gspread?

Question

Currently I can connect using gspread and create/share a spreadsheet. I can also read data from said spreadsheet but I have to request it by name. Is there a way to list of all spreadsheets shared with the OAuth account being used.

I may not know all the names of the spreadsheets that will be shared with the account so I want to have them listed off programmatically if possible.

All I have been able to do so far is select a sheet by name. But I need to be able to pull a list of all sheets shared with my service_account.

Everything I have found so far online is related to getting a list of sheets in a workbook but not a list of all workbooks available.

Documentation is welcome though all the stuff I have found so far has not helped.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import tkinter as tk


class Main(tk.Tk):
    def __init__(self):
        super().__init__()
        self.rowconfigure(0, weight=1)
        self.columnconfigure(0, weight=1)
        self.geometry('1000x400')
        self.txt = tk.Text(self)
        self.txt.grid(row=0, column=0, sticky='nsew')
        self.scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
                      "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
        self.txt.insert('end', '{}\n'.format(self.scope))
        print('Scope: ', self.scope)
        self.after(2000, self.testing)

    def testing(self):
        creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', self.scope)
        client = gspread.authorize(creds)

        try:
            sh = client.create('Created By App')
            sh.share('[email protected]', perm_type='user', role='writer')

        except Exception as e:
            print('Error: ', e)

        try:
            print('Client: ', client)
            try:
                self.txt.insert('end', '{}\n'.format('Running: client.list_permissions("TestingSheet")'))
                self.txt.insert('end', '{}\n\n'.format(client.list_permissions("TestingSheet")))
            except Exception as e:
                self.txt.insert('end', 'Error: {}\n\n'.format(e))
                print('Error: ', e)
            try:
                self.txt.insert('end', '{}\n'.format('Running: client.list_spreadsheet_files()'))
                self.txt.insert('end', '{}\n\n'.format(client.list_spreadsheet_files()))
            except Exception as e:
                self.txt.insert('end', 'Error: {}\n\n'.format(e))
                print('Error: ', e)
            try:
                self.txt.insert('end', '{}\n'.format('Running: client.session()'))
                self.txt.insert('end', '{}\n\n'.format(client.session()))
            except Exception as e:
                self.txt.insert('end', 'Error: {}\n\n'.format(e))
                print('Error: ', e)

            # Find a workbook by name and open the first sheet
            # Make sure you use the right name here.
            sheet = client.open("TestingSheet").sheet1
            self.txt.insert('end', '{}\n\n'.format(sheet))
            print('Sheet: ', sheet)
            # Extract and print all of the values
            list_of_hashes = sheet.get_all_records()
            self.txt.insert('end', '{}\n\n'.format(list_of_hashes))
            print('list_of_hashes: ', list_of_hashes)
        except Exception as e:
            self.txt.insert('end', 'Error: {}\n\n'.format(e))
            print('Error: ', e)

creds.json is formated like this:

{
  "type": "service_account",
  "project_id": "XXXXXXXXXXXXXXXXXXXXXXXX",
  "private_key_id": "XXXXXXXXXXXXXXXXXXXXXXXX",
  "private_key": "-----BEGIN PRIVATE KEY-----\nXXXXXXXXXXXXXXXXXXXXXXXX\n-----END PRIVATE KEY-----\n",
  "client_email": "project-service-account@XXXXXXXXXXXXXXXXXXXXXXXX.iam.gserviceaccount.com",
  "client_id": "XXXXXXXXXXXXXXXXXXXXXXXX",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/project-service-account@XXXXXXXXXXXXXXXXXXXXXXXX.iam.gserviceaccount.com"}

Answer 1

It is possible.

import gspread
gc = gspread.service_account('./creds.json')
def createNewSheetIfNotExist(title):
    if title not in [sh.title for sh in gc.openall()]:
        gc.create(title)
    print([sh.title for sh in gc.openall()])
    
createNewSheetIfNotExist('Test')
createNewSheetIfNotExist('Test')

Running the function multiple times will not add a new sheet because it has found its title inside gc.openall().

gspread API Reference

Answer 2

It is not possible to list all shared Google Sheets file using Gspread. Since it uses Google Sheets API v4, there is no existing method in Sheets API to list Google Sheets files in your drive.

You need to use Drive API to search for Google Sheets file shared to your service account using files.list with a query string q="mimeType='application/vnd.google-apps.spreadsheet' and sharedWithMe"

Your files.list request will return a list of files which contains the name of the file and its file id. Then you can use gspread to read/modify your Google Sheets file.

Sample Response using API Explorer:

{
 "kind": "drive#fileList",
 "incompleteSearch": false,
 "files": [
  {
   "kind": "drive#file",
   "id": "1wXCD1SaujjD46NM7NFpP8jrqL_xxxxx",
   "name": "File1",
   "mimeType": "application/vnd.google-apps.spreadsheet"
  },
  {
   "kind": "drive#file",
   "id": "1Bm7bFs8SveQt75geOGjDAmOgfxxxxx",
   "name": "File2",
   "mimeType": "application/vnd.google-apps.spreadsheet"
  }]
}

You can check the following references on how to setup and use Drive API in Python:

• Python Quickstart
• Search for specific files or folders on the current user's My Drive - Sample Python script included

Answer 3

If the goal is to obtain a list of workbook names, the following works:

import gspread

gc = gspread.service_account("./creds.json")
workbooks = [file["name"] for file in gc.list_all_spreadsheets()]

The .list_all_spreadsheets method of the gc instance of gspread's Client object returns a list of dictionaries with the information of every spreadsheet shared with the service account.

If our service account had been shared to from 3 different spreadsheets, "Workbook 1", "Workbook 2", and "Workbook 3", then this method would return the following.

[{'kind': 'drive#file',
  'id': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
  'name': 'Workbook 1',
  'mimeType': 'application/vnd.google-apps.spreadsheet'},
 {'kind': 'drive#file',
  'id': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
  'name': 'Workbook 2',
  'mimeType': 'application/vnd.google-apps.spreadsheet'},
 {'kind': 'drive#file',
  'id': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
  'name': 'Workbook 3',
  'mimeType': 'application/vnd.google-apps.spreadsheet'}]

I decided to use list comprehension to loop through these dictionaries. Feel free to go about this step in another manner. If the above is the result of gc.list_all_spreadsheets(), then workbooks would be

['Workbook 1', 'Workbook 2', 'Workbook 3']