How can I get the name of a spreadsheet from only its URL using pandas

I just need the name of the file so I when I create a CSV file out of the imported sheet I can give it a descriptive name. But I don't know how to get its name using the pd.read_excel() function or anyother function.

The following code is where I am at

import pandas as pd

link = input("Enter link here:")

link=link.split('/')
sheet_id=link[5]
print (sheet_id)

  #sets spreadsheet to pd
xls = pd.ExcelFile(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=xlsx") 

  #sets sheet_1 to be spreasheet
sheet_1 = pd.read_excel(xls,"Summary", header = 0)
print(sheet_1)
print("------------")

Solution

I believe your goal is as follows.

You want to retrieve the title of a Google Spreadsheet of f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=xlsx".
In your situation, the Spreadsheet f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=xlsx" has already been publicly shared.

If my understanding is correct, how about the following sample script?

Unfortunately, I couldn't find the title of the Google spreadsheet from ExcelFile object. So, in this answer, I would like to propose another approach.

Sample script:

In this sample, the filename of Google Spreadsheet is retrieved from the response header.

import pandas as pd
import requests

link = input("Enter link here:")

link=link.split('/')
sheet_id=link[5]
print (sheet_id)

r = requests.get(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=xlsx")
print(r.headers['content-disposition'])
filename = r.headers['content-disposition'].split("filename*=UTF-8''")[1].replace(".xlsx", "")
print(filename)

Or, when unquote is used, it becomes as follows.

import pandas as pd
import requests
from urllib.parse import unquote

link = input("Enter link here:")

link=link.split('/')
sheet_id=link[5]
print (sheet_id)

r = requests.get(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=xlsx")
print(r.headers['content-disposition'])
filename = unquote(r.headers['content-disposition'].split("filename*=UTF-8''")[1].replace(".xlsx", ""))
print(filename)

Note:

In this case, it supposes that your Google Spreadsheet has already been publicly shared. Please be careful about this.

By the way, if you can use your API key for using Drive API, the following script can be also used.

api_key = "###" # Please set your API key.
spreadsheet_id = "###" # Please set your Spreadsheet ID.
r = requests.get(f"https://www.googleapis.com/drive/v3/files/{spreadsheet_id}?key={api_key}")
filename = r.json().get("name", "")
print(filename)