Function for reading private Google Spreadsheets

read_private_sheets.py

import pandas as pd
import requests
import gspread


def read_private_sheets(
    credentials_json: str, sheet_url: str, worksheet: int = 0
) -> pd.DataFrame:
    """Read a private available Google Sheets as a Pandas Dataframe.

    Parameters
    ----------
    credentials_json : str
        Path to JSON file with GCloud Credentials.

    sheet_url : str
        Spreadheet URL adress.

    worksheet : int (default=0)
        Index or name for the target worksheet.

    Returns
    -------
    pd.DataFrame
        Dataframe loaded from the spreadsheet.

    """
    # Parsing file URL
    file_id = sheet_url.split("/")[-2]

    gcloud = gspread.service_account(filename=credentials_json)
    sheet = gcloud.open_by_key(file_id)
    worksheet = sheet.get_worksheet(worksheet)
    list_rows_worksheet = worksheet.get_all_values()
    return pd.DataFrame(
        list_rows_worksheet[1:], columns=list_rows_worksheet[0]
    )