Reading GoogleSheet with pandas dataframe doing search on it

Question

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")

Have you tried using pandas.DataFrame.to_excel. I don't quite understand which bit doesn't work for you. Can you please post your code? — Bill
– Bill, Commented Oct 14, 2022 at 17:28
basically, "How to gather from GoogleSheet to Excel via python" ...the code i found on internet, exports googlesheet data from a single sheet as CSV file. But I need to export as XLSX file, Because further I will search sheet by Column — xlmaster
– xlmaster, Commented Oct 14, 2022 at 17:32
Okay well then have a go at writing some code to do that and only post a question if you get stuck or have an error message you don't understand. If possible, post a reproducible code segment and data sample that we can run to get the same error. — Bill
– Bill, Commented Oct 14, 2022 at 17:36
Can I read_excel(googleshetUrl, sheetname, header=0) ? @Bill — xlmaster
– xlmaster, Commented Oct 14, 2022 at 17:45
Okay that's a different question. I see you changed the title now to reflect that. What have you found so far by googling "Python Pandas read Google Sheets"? Have you looked at this: Getting Google Spreadsheet CSV into A Pandas Dataframe for example? — Bill
– Bill, Commented Oct 14, 2022 at 18:00

Bill · Accepted Answer · 2022-10-15 16:53:28Z

1

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

Python Read In Google Spreadsheet Using Pandas

spreadsheet_id = "<INSERT YOUR GOOGLE SHEET ID HERE>"
url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_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.

UPDATE - based on comments below

If your spreadsheet has multiple tabs and you want to read anything other than the first sheet, you need to specify a sheetID as described here

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

edited Oct 15, 2022 at 16:53

answered Oct 14, 2022 at 21:38

Bill

11.8k13 gold badges68 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

xlmaster Over a year ago

I did other way; pd.read_excel(pd.ExcelFile(f'link'),sheetname,header=0)

Bill Over a year ago

You're right, pd.read_excel does seem to work. That is a good answer. Why don't you add it to the original question I posted a link to above? You could also add it as a full answer here if you like.

Bill Over a year ago

I will update this answer with an alternative using the gid.

Bill Over a year ago

Go to the link above and scroll down to the bit where it says "To write to separate sheets in a single file".

xlmaster Over a year ago

Thanx Bill, I meant in general. Actually after your words, I did not post other question what I wanted, I am already in stage of pd.DataFrame(dictDataOutput,index=[0]).to_excel('output.xlsx') as you can see, I can read and surf on posts . But sometimes I think formulation in documentations or books are a little bit noisy. Not to exact point or I maybe have focusing problem. But I am trying to look for programming paradigms on web, youtube also reading Mark Lutz Learning Python, Programming Python

|

Collectives™ on Stack Overflow

Reading GoogleSheet with pandas dataframe doing search on it

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related