Reading GoogleSheet with pandas dataframe doing search on it-CodePudding

Do I need read_excel GoogleSheet for doing further search action on its columns in Python?

I must gather data from the entire Google Sheet file. I need search by sheetname firstly, then gather information by looking up the values in columns. I started by looking up the two popular solutions on the internet; First one is, with the gspread package : as it relies on service_account.json info I will not use it. Second one is, appropriate for me. But it shows how to export as csv file. I need to take data as xlsx file.

code is below;

import pandas as pd
sheet_id=" url "
sheet_name="sample_1"

url=f"https://docs.google...d/{sheet_id}/gviz/tq?tqx=out:csv&sheet={sheet_name}"

I have both info sheet_id and sheet_name but need to export as xlsx file.

Here I see an example how to read an excel file. Is tehre a way to read as excel file but google spreadsheet

Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

xls = pd.ExcelFile('excel_file_path.xls')

# Now you can list all sheets in the file
xls.sheet_names
# ['house', 'house_extra', ...]

# to read just one sheet to dataframe:
df = pd.read_excel(file_name, sheet_name="house")

CodePudding user response：

I have no problem reading a google sheet using the method I found here:

Python Read In Google Spreadsheet Using Pandas

sheet_id = "<INSERT YOUR GOOGLE SHEET ID HERE>"
url = f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv"
df = pd.read_csv(url)
df.to_excel("my_sheet.xlsx")

You need to set the permissions of your sheet though. I found that setting it to "anyone with a link" worked.