0

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")
Bill
  • 10,323
  • 10
  • 62
  • 85
xlmaster
  • 659
  • 7
  • 23
  • 2
    Have you tried using [pandas.DataFrame.to_excel](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html). I don't quite understand which bit doesn't work for you. Can you please post your code? – Bill Oct 14 '22 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 Oct 14 '22 at 17:32
  • 1
    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 Oct 14 '22 at 17:36
  • Can I read_excel(googleshetUrl, sheetname, header=0) ? @Bill – xlmaster Oct 14 '22 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](https://stackoverflow.com/q/19611729/1609514) for example? – Bill Oct 14 '22 at 18:00
  • I was reading `https://stackoverflow.com/questions/26474693/excelfile-vs-read-excel-in-pandas` let me also look your link, but it writes more about csv, as I said, I need to export as xlsx to being able to search rowByrow for every column – xlmaster Oct 14 '22 at 18:07
  • that's not what I needed, I read it before posting question – xlmaster Oct 14 '22 at 18:27
  • What do you mean by "a way to read as excel file but google spreadsheet"? That's confusing. – Bill Oct 14 '22 at 20:29
  • to as xlsx file not as csv file. – xlmaster Oct 14 '22 at 20:31

1 Answers1

1

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

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")
Bill
  • 10,323
  • 10
  • 62
  • 85
  • I did other way; `pd.read_excel(pd.ExcelFile(f'link'),sheetname,header=0)` – xlmaster Oct 14 '22 at 23:25
  • Also, why did you remove the `gid` from your code? There must be `output=csv` also – xlmaster Oct 15 '22 at 00:50
  • 1
    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 Oct 15 '22 at 16:38
  • 1
    I will update this answer with an alternative using the `gid`. – Bill Oct 15 '22 at 16:40
  • what's I am curious about why `&format=xlsx` does not work instead of `&format=csv`... – xlmaster Oct 15 '22 at 17:28
  • I don't know. You would have to look in the Google sheets documentation to see if they have an export format option called `xlsx`. – Bill Oct 15 '22 at 17:35
  • it is ambigious google api. I am trying to find proper documentation since yesterday. Gspread also needs some kind of authorization id, better to go this way. Also, `df.to_excel("my_sheet.xlsx")` it will right to first sheet, or we need to mention sheetname also? – xlmaster Oct 15 '22 at 17:37
  • You should get used to reading [the documentation](https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html) before asking questions. Only ask when you've tried and you're stuck. – Bill Oct 15 '22 at 17:51
  • What I got that is all documentation are written in a general purpose, that gives you understanding if you already written in many langiages and know well the programming paradigm. If you are shift to CS then you can read few books and search for title on internet but, again cannot find out your answer if not any engineer from stackoverflow would mentor you to specific direction – xlmaster Oct 15 '22 at 18:00
  • 1
    Go to the link above and scroll down to the bit where it says "To write to separate sheets in a single file". – Bill Oct 15 '22 at 18:06
  • 1
    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 – xlmaster Oct 15 '22 at 18:15