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

Issue

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)
  

Answered By - Tanaike