Google Sheets and Python - Tutorial
Вставка
- Опубліковано 5 вер 2024
- Learn how to use Google Sheets API in Python. We are using the gspread module for this. It's super simple to setup a project, and then access and modify our spreadsheet with a Python script. Google Sheets can be pretty powerful and used as a backend to store some data for your web applications.
~~~~~~~~~~~~~~ GREAT PLUGINS FOR YOUR CODE EDITOR ~~~~~~~~~~~~~~
✅ Write cleaner code with Sourcery: sourcery.ai/?u... *
📚 Get my FREE NumPy Handbook:
www.python-eng...
📓 Notebooks available on Patreon:
/ patrickloeber
⭐ Join Our Discord : / discord
If you enjoyed this video, please subscribe to the channel!
The code can be found here:
github.com/pat...
Check out gspread documentation here: gspread.readth...
You can find me here:
Website: www.python-eng...
Twitter: / patloeber
GitHub: github.com/pat...
#Python
----------------------------------------------------------------------------------------------------------
* This is a sponsored link. By clicking on it you will not have any additional costs, instead you will support me and my project. Thank you so much for the support! 🙏
I watched many videos, they all required HELL long process for enabling reading and writing. This is the best one so far that works even a year later. Hats off to you.
thanks, glad its helpful!
explained in a simple fast effective way.
You are the number one .
I see 20 others tutorials without doing anything
Thanks
Glad it helped
Finally, a video without importing hundreds of other libraries. Awesome! Worked like charm. Want to experiment this further and must see if I can incorporate this to my day to day work. Thank you, Patrik!
glad you like it :)
I’m really grateful for this video. I’d watched a dozen of videos before found your content. Really appreciate it, wish you the best of luck 🤞
Spend so many times watching videos and yours is the most clearest one. Thank you!
Happy to hear that!
Clearly explained, worked like a charm! Congrats, Patrick!
Really good explained.
Thank you! very clear and easy to understand, complete lifesaver
Glad it's helpful!
Brilliantly done, going through examples helps a lot!
Great to hear!
Hey Python Engineer!
First of all - thanks a lot. This tutorial has been very helpful for me and I enjoy the way you pass your knowledge.
I have a problem that I couldn't find the answer to on the gspread documentation, so I'd love it if you could find the time to answer:
My problem is - running my script on my sheet cancels all existing formulas in the sheet.
The script loops over rows, and depending on a certain condition, It uses the update_cell function to update certain cells' values, one cell at a time.
I've investigated and found there's the parameter value_input_option - which is not a part of the function "update_cell".
I just want to run my script and let the whole GS formulas remain (e.g. an Index row with the formula =a1+1 -> the formula will be deleted and the value will stay the same).
I'm writing this comment after spending hours online but couldn't find a solution.
Thanks in advance,
Roy
You are the best.. clear explanation of the concepts and very easy to follow along...
glad you like it!
easily explained thanks bro
this is an awesome video...I was watching other videos and getting stuck by an extra difficult way of accessing google sheets!
I'm glad it's helpful :)
Great tutorial, thank you!
First of all thanks for your great video. Could you suggest me a tutorial how to check google forms every minute and send auto emails to new entries.
Very good tutorial. Simple to follow and cleared a lot of my doubts! Awesome stuff.
Glad you like it!
Appreciate your help 🙏
Thanks this tutorial bro. Can we send multiple data from python to google sheets?
Love your tutorial. Very easy to follow. Thanks
Glad it was helpful!
Thank you so much!!
this helped me mate thank you
glad it is helpful!
thanks a lot...really very useful video
Glad it was helpful!
Your Videos are amazing. Keep up the good work.
Excellent video. Thank you - I struggled without this.
glad you like it!
Very useful video. I want to replace the whole old df with new df in sheets with this method. Dfs contain 1500 columns.
Thanks very much. Spent a long time trying to figure this out
I'm glad it was helpful :)
Thanks so much for your guide!
Is there a way to remove the "['']" text?
Congratulations I subscribed to your channel
Thanks for subbing :)
Thanks a lot, this was very useful for me. Im noob in Python, but i try to create application which uses sheets API
Glad you like it :)
Thank you.
Excellent 👌
It is a great video. Thank you! if please share a code to save data of panda data frame data into a google sheet.
thank you for your time and clear explanation, is there a way we can make the google sheet receive the data every 24hours?
thanks very helpfull
Subscribed! Please make more google apps and python videos! You have good content and are explaining pretty well! Or maybe sync Libre Office Calc with Sheets.
Thanks for the tips and thanks for subscribing!
very useful! I am just starting out with Python and GoogleSheets, and am looking to read QR-codes, and append the information scanned + time/date for an attendance system.
Love the idea of the data in the cloud, and my task is to get a foolproof input system maybe a raspberry pi headless with a barcode scanner....
Subscribed!
cheers und danke
Sounds like a nice project :)
great job . but how i make search operation ?
very useful video.
thanks!
module 'gspread' has no attribute 'service_account'
I am getting this error :(
Thank you Python Engineer for this to-the-point and well thought-out video. Subscribed :)
Thanks 😊 glad you like it :)
Hi @@patloeber,
Can you please help me with the following issue? I’m completely new to Python. I was facing difficulty with one script I had written:
==================================
import requests
from bs4 import BeautifulSoup
# Opening NSE website to scrape Free Float Market Cap(Crs) value
url = "www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ACC&illiquid=0&smeFlag=0&itpFlag=0"
headers = {
'Access-Control-Allow-Origin': '*',
'Access-Control-Allow-Methods': 'GET',
'Access-Control-Allow-Headers': 'Content-Type',
'Access-Control-Max-Age': '3600',
'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.114 Safari/537.36'
}
response = requests.get(url, headers=headers)
soup = BeautifulSoup(response.content, 'html.parser')
floatValue = soup.find(id="ffmid")
print(floatValue)
print(floatValue.string)
==================================
Even though the Free Float Market Cap(Crs) value is 14,229.25 on the website, I’m getting the following output in Sublime Text:
--
--
Why is it showing as -- instead of the value? Can’t figure out the error.
Huge like, share and thumbsup, your guide is the perfect introduction on UA-cam and that's final.
BTW, is there a way to reference columns by their letter instead of number?
Thanks so much! Yes absolutely, e.g. worksheet.get('A1')
@@patloeber Cool! Also, why do the script stops loading the credentials once I put them in another folder? Shouldn't the files being in the same folder be enough?
How can we write a python script to push data from Excel to Google sheets?
Could you please teach us how to do update google sheets through Excel? Thank you!
Super informative and thank you for keeping this video concise! I was wondering, is it possible to do an append style command for a row but with specified columns? For example, using the same append function that you used, but the table is in columns J, K, and L. Thank you!
Yep should be possible. I recommend to have a look into the official docs to find this method
hi Nicholas...same issue i got n went thru official docs as well as searched online but no proper solution (i used table_range but after appending few rows in my desired column it switches back to 1st column) ....please let know if you were able to find the way..thanks
Super video
Thanks!
Thank you!
How we can connect sql server table to google sheet
Thank you! This video helped a lot.
That's nice to hear :)
you're amazing man!! Thank yuo so much!
Thank you :)
Hello. It says that "no module named 'gspread', what will I do?
open command prompt/terminal and run "pip install gspread".
yep
Please make videos more frequently
I try :)
@@patloeber Please
i dont have create key option
Thank you very much
glad you like it!
Great! Thanks!
Thanks for watching:)
Thanks a lot for the very useful information!!!! But I have a question do I need to pay for the usage of the api than Google offers me or is it free?
They offer a generous free limit (I think it's 10.000 API calls per day)
how to append multiple row?
Very very useful
Thanks!
Very useful! Could you do same tut using google colab?
Thanks! I will have a look at that
How to integrate python and google sheet android mobile ( mobile vsmart )
love you
how can I CREATE a spreadsheet that doesnt exist yet ? (then fill it with data) the example here uses a spreadsheet 'tutorial' that already exist.
go to google sheets and create a new spreadsheet manually
What app it download more
is there a way to switch between the sheets in the google sheets (bottom left corner of the google sheets)
yes you can access different worksheets like so: sh.get_worksheet(0), or by title: sh.get_worksheet("name")
thanks a lot, great information. and i have a doubt please clarify me.
We all know how is our team page looks in WordPress webpage. My question is, is it possible to update those information from google sheet to WordPress team page and it looks same as our webpage teams page.. If so, please tell me how to do.
now i got to know how to read data from google sheet using python and then now need to update those data to wordpress team page. how to do please reply me.
yes it's possible but I'm not very familiar with Wordpress myself...
Very useful video. This means a lot to me. I couldn’t have done it without you.
Thanks! Glad you like it
awesome!
thanks!
Is it possible to read cell colors?
Yes it should be possible to get and set colors
thnk u
You're welcome 😊
I don't know why, but when I'm on the last step of creating the service account for the Google Drive API, the create key is not an option here. What should I do?
You can create the service account without the key and then go to edit the new account and add a new key
yes, thanks for helping out!
I tried doing the pip install on the command prompt for my windows laptop and I keep on getting "'pip' is not recognized as an internal or external command, operable program or batch file." any ideas how I can get this to work?
Before using pip install you need to go to the folder where you installed python. Otherwise it doesn't work. Doing it is simple, open command prompt and then type "cd [space] [full address where you installed python] pip install what you want to install. I hope I have been useful
You have to install pip first...Maybe googling for “install pip on windows” helps :)
I had this problem too after reinstalling Python 3, which pip supposedly comes installed. To solve it I found a stackoverflow thread titled "pip is not recognized as an internal or external command" (google it). Essentially, the scripts folder which contains pip was not in %PATH%. The first solution in that thread worked for me.
How to append all rows of a dataframe in python, to an existing google sheet? I followed till the process, where you mentioned appending one row. But I want to append all rows of a dataframe. Can you help?
use append_rows() which takes a list as input
@@patloeber I used df.values.tolist() to convert df to list. Then iterated all list values using append_rows(). But still no luck. Any idea where I am going wrong?
Hi, I receive thi error
('invalid_grant: Invalid JWT Signature.', '{"error":"invalid_grant","error_description":"Invalid JWT Signature."}')
at this line:
worksheet = sh.sheet1
i think your API keys are not valid, or you forgot to share the sheet with the created bot account
is that IDLE? im new to this so idk. Im on windows python v3.8.3
I'm using VS Code as my editor
when i run it, it got "NameError: name 'credentials' is not defined" does anyone have an idea on why this is happening?
Maybe you have a typo ?
Integrated python and google sheet android mobile please, thank you
How do I select a specific sheet?
get_worksheet(index)
@@patloeber thank you so much for this video, clear and very easy to follow. I also had this question, sorry how do i use the get_worksheet(index) to lets say put a df in sheet2?
Sorry if my question is unclear i am still new to Python
thank you i figured it out
Thank you so much, very clear and understandable. It's a life-saver for me
Glad you like it
Thank you a lot!!
thanks for watching!
I don't have the create key button
Thanks!
what to write if we have sheet name with spaces?
does it not work with spaces? otherwise you can also access the sheet by it's id
Was very useful thank you!