Fetch Google Sheet Names Using Python and Google Sheets API

Understanding the Google Sheets API and Fetching Sheet Names with Python

As a developer, working with Google Sheets can be an efficient way to manage data. However, accessing specific sheet names from a Google Sheet’s ID is not as straightforward as you might think. In this article, we will delve into how to fetch Google Sheet names using the Google Sheets API and Python.

Prerequisites: Setting Up Your Environment

To begin with, ensure that you have the following installed in your environment:

Google API Client Library for Python: You can install it via pip.

pip install –upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib


*   **Create a project and enable the Google Sheets API**:
    Navigate to the [Google Cloud Console](https://console.cloud.google.com/) and create a new project. Enable the Google Sheets API by searching for it in the search bar, clicking on the result, and then clicking on the "Enable" button.

*   **Create credentials for your project**: Generate OAuth client ID and secret key.
    -   Go to the [Google Cloud Console](https://console.cloud.google.com/) > Select your project > Navigation menu (three horizontal lines in the top left corner) > API & Services > Credentials
    -   Click on "Create Credentials" > OAuth client ID > Select "Other" > Give a name to your client ID, and then click on Create.

### Step 1: Authenticating with Google Sheets API

To use the Google Sheets API, you need to authenticate your requests. In this example, we'll create an authentication flow using the OAuth credentials obtained earlier:

```python
import os
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow

# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

def sheets_to_csv(GOOGLE_SHEET_ID):
    # The ID of the Google Sheets API client object that was created earlier.
    service = None
    creds = None

    # Prompt for user to grant permission. Store the token in a local file.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)

    # Use obtained credentials to authenticate
    service = build('sheets', 'v4', credentials=creds)

    try:
        gsheets = service.spreadsheets().get(spreadsheetId=GOOGLE_SHEET_ID).execute()
        sheets = gsheets['sheets']

        for sheet in sheets:
            dataset = service.spreadsheets().values().get(
                spreadsheetId=GOOGLE_SHEET_ID,
                range=sheet['properties']['title'],
                majorDimension='ROWS'
            ).execute()
            df = pd.DataFrame(dataset['values'])
            df.columns = df.iloc[0]
            df.drop(df.index[0], inplace=True)
            filename = gsheets['properties']['title'] + '-' + sheet['properties']['title']
            df.to_csv(filename + '.csv', index=False)
            print()

    except Exception as e:
        print(e)

sheets_to_csv('1LqynjF33-mrO9M5INf4wJvJuY57Hhy4vjv_FjtuM')

Step 2: Understanding Google Sheets API Request Structure

To understand how to fetch the name of a specific sheet, you need to grasp the structure of a request made using the Google Sheets API.

import requests
from googleapiclient.discovery import build

# The ID of the Google Sheets API client object that was created earlier.
service = build('sheets', 'v4')

def get_sheet_name(service, spreadsheet_id):
    try:
        result = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
        return result['properties']['title']
    except Exception as e:
        print(e)

# Get the title of a specific sheet
print(get_sheet_name(service, '1LqynjF33-mrO9M5INf4wJvJuY57Hhy4vjv_FjtuM'))

Step 3: Modifying Your Code to Fetch Sheet Names

To modify your existing code and fetch the names of all sheets in a Google Sheets document, you can loop through each sheet:

import pandas as pd
from google import Create_Service

def sheets_to_csv(GOOGLE_SHEET_ID):
    # The ID of the Google Sheets API client object that was created earlier.
    service = None
    creds = None

    # Prompt for user to grant permission. Store the token in a local file.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)

    # Use obtained credentials to authenticate
    service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)

    try:
        gsheets = service.spreadsheets().get(spreadsheetId=GOOGLE_SHEET_ID).execute()
        sheets = gsheets['sheets']

        for sheet in sheets:
            dataset = service.spreadsheets().values().get(
                spreadsheetId=GOOGLE_SHEET_ID,
                range=sheet['properties']['title'],
                majorDimension='ROWS'
            ).execute()
            df = pd.DataFrame(dataset['values'])
            df.columns = df.iloc[0]
            df.drop(df.index[0], inplace=True)
            filename = sheet['properties']['title'] + '-' + sheet['formattedId']
            df.to_csv(filename + '.csv', index=False)
            print(f"Sheet Name: {sheet['properties']['title']}")

    except Exception as e:
        print(e)

sheets_to_csv('1LqynjF33-mrO9M5INf4wJvJuY57Hhy4vjv_FjtuM')

Step 4: Using the Google Sheets API to Fetch Sheet Names

You can use the service.spreadsheets().get() method with a specific sheet ID. First, you need to get the sheet IDs from your document using the following command:

def get_sheet_ids(service, spreadsheet_id):
    try:
        result = service.spreadsheets().list(spreadsheetId=spreadsheet_id).execute()
        return [sheet['id'] for sheet in result.get('sheets', [])]
    except Exception as e:
        print(e)

sheet_ids = get_sheet_ids(service, '1LqynjF33-mrO9M5INf4wJvJuY57Hhy4vjv_FjtuM')

Then you can loop through each sheet ID to fetch the names of all sheets:

def fetch_sheet_names(service, spreadsheet_id):
    try:
        result = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
        return [result['properties']['title']]
    except Exception as e:
        print(e)

# Fetch the title of a specific sheet
print(fetch_sheet_names(service, '1LqynjF33-mrO9M5INf4wJvJuY57Hhy4vjv_FjtuM'))

Conclusion

This guide has walked you through how to use the Google Sheets API to authenticate and fetch the names of sheets in a specific document.

Last modified on 2023-06-20