Google Sheets and Python
HTML-код
- Опубликовано: 22 авг 2024
- Learn more from the blog post at www.twilio.com...
Check out the wonderful gspread library on Github: github.com/bur...
If you have any questions, leave them in the comments or find me on Twitter at / brentschooley
If you're running into insufficient permission errors, update the scope in the code to:
scope = ['spreadsheets.g..., 'www.googleapis...]
![Inverting Binary Trees - You Suck at Coding [1]](http://i.ytimg.com/vi/ijL0h6-1qbQ/mqdefault.jpg)
![[TF2] The Russian Bear Trap](http://i.ytimg.com/vi/YFNSwz2_rOA/mqdefault.jpg)
2 year ago I knew nothing about coding and found this video fascinating and confusing.
Today I needed to manipulate Google Sheets via code and found yet again this amazing video, it's pretty cool to see how things change.
I’m so happy to hear this! Glad we could help :)
Even I'm bit confused. I have just started with python. I want to try this but I have to know a lot more about what's Google API, Spreadsheet,....
i want to do a project with this but im not sure what yet
@@siddietrich5652Let me give you a challenge, try making a game, any game, as simple as it is, it may be pretty fun to code something like that.
Are you a comp sci major? I am a freshman in college as a general engineer and I want to do comp sci because I see how useful it is but I have almost ZERO coding experience... Idk if I should risk it
Absolutely perfect youtube video. Superb voice and video quality. No flashy long intros. Short and to the point whilst explaining everything perfectly. Needless to say, I'm in love with this channel now! Good stuff man!
Harrison If I had to quibble, I'd recommend adding a description.
Well said
Accessing data on the cloud without messing around with databases or ssh and using python? I've been looking for something like this for years, thank you!
How about RESTful post or put? How about AWS s3 bucket, Google drive or Dropbox API?
Will Bobson gotta take into account cost. Aws May have free trials but isn’t free forever. For an enterprise solution awe is the clear answer, but for a fun side project this is a great workaround
@@huckthatdish actually a lot of free tier is permanent now.
This video is inspiring, but it’s missing something. A CRUD application like this might be possible, you have first to mess around with OAuth and try to handle multiple accounts that can access to a doc
This is fucking amazing. Thank you! This will be great to teach my students because installing postgresql is a difficult process & most students get lost in just the installation process let alone trying to understand how databases work. Spreadsheets are WAY more intuitive and you already have a nice "GUI" to play around with the data.
Yeah, that's what I meant! Start with this so they can understand how databases work & then move them on to real databases.
Quick question, I'm trying to make a very basic flask todo list app with crud functionality using this gspread idea. Would it be so slow that it would render it useless or you think it'll still work?
immature swearing
You really should just be teaching them to use SQLite for early database lessons in Python.
Hey clever programmer bro i watch your videos of python, you teach me enough that my prof. cant teach
In my experience, now you have to add the Google Sheets API in addition to the Google Drive API to the project to have access to the sheets with python
Lifesaver, thanks. You just saved me at least an hour of head-scratching.
Easily the best how to coding video I think I've ever seen. Clarity, completeness, pace, accessible for a beginner but not condescending to an expert. Massive kudos - you have a knack for this.
I’ve got no idea what any of this means but I liked this video
Haha same
🔥 WATCH NEXT: Build an Animal Crossing price lookup bot with Google Sheets and WhatsApp ruclips.net/video/eqtQ76X_owQ/видео.html
If you're running into insufficient permission errors, update the scope in the code to:
scope = ['spreadsheets.google.com/feeds', 'www.googleapis.com/auth/drive']
Thanks! I also had to enable Google Sheets API on my project.
Thanks! Solves my issue as well.
can you please explain why this is? just wanted to learn the reasoning :)
Thank you!!!!!, your revised scope worked!
Twilio *****
Not sure why Twilio made this video, but I am thankful.
Great vid.
it's called content marketing :)
Twilio thanks for doing this, these videos are super helpful and you have no reason to make them other than the fact you’re a good company!
@@escherlies the only kind of marketing i am down with
I built an API in python to do my work in exell and I still got paid at the end of the month for 4 months before I got bored of playing games in my cubical and maintaining my API and adapting it to new work requirements... I did post it on my GitHub but no one wanted it since you do need to configure it to do your specific work, but I didn't have any documentation so....
Basically what my work was: check growth margin from the last hour, day, week, month, year and run a formula which will give me the expected growth in the next year... It was a bit hard since my company works with many articles so I had to do that for as many articles as I can in one day so I just built an API, gave it the data it needs and it gave me the finished product, I entered it in the Excell and I was done for the day.... 30 minutes and I am done for the next 7:30 hrs
I could have spilled the truth and gave my API to my co-workers but then I'd run the risk of being fired because once the boss finds out he doesn't need 3 workers, he is going to fire 2 and keep 1 to do all of the work....
What's your github, looks like a interesting api to tweak around with
Honestly, if you made something like that. Boss probably would have kept you as the one worker and given you a raise. But then you just got your two coworkers fired and that's not too good
@@carsoncase223 This has not been my experience. I once made the mistake of streamlining a process that used to take 8 hours a day and made it take 20 minutes through some API work. Showed my boss that I had saved the company 8 hours of labour weekly (Was only run on Mondays) and the result was that I was given a stack of processes to streamline in a addition to the now 32 hours of work a week plus 8 more hours to round it out. I should also note that my job has nothing to do with programming, its data analysis and projections. But showing my boss that I had some very modest programming skills from hobby work had him shoving impossible tasks my way because, "You can get 8 hours of work done in 30".
@@Adaephonable Damn. That's pretty brutal. Should've started a company and sold the tech to your boss. No way he'll know it's made by a modest programer as opposed to a pro. Although, that's probably more effort than it's worth I guess
@@Adaephonable its because youre a little twit and you dont dare to ask for a raise for doing more work
I think I now know what it feels like to be an old person listening to young people talk about computers
✋ ✋ ✋
The best programming tutorial on RUclips by far. Clear, concise, to the point, and never once succumbing to the familiar social media urge to "mix things up." Thank you.
I'm glad this came up in my recommended. This is brilliant instead of buying a server/hosting off your own machine for a basic in-house project
Thank god, someone can explain something simply!!!!!! Google's own python quick start guide left me with errors and no idea what was going wrong.
Note: the layout of google cloud has changed since this tute, but you can still muddle your way thru. Note the comment "
If you're running into insufficient permission errors, update the scope in the code to:
scope = ['spreadsheets.google.com/feeds', 'www.googleapis.com/auth/drive']"
I wish it was possible to update a video in place because I totally would. Unfortunately, people would abuse that. Glad you were able to work your way through it!
Never really comment on videos, but I must say that this is a great video, straight to the point, fast and just really great.
this is so freaking cool!!!
This is a perfect tutorial, no messing just so well done well scripted and well edited.
Where can we easily get help with things like this? Is there any one place you'd recommend to help troubleshoot?
I have a fairly simple question but I know it's frowned upon to ask it in the comments section. :)
I'm learning to write neural nets so seeing normal code for a change helps to keep me sane.
It's stupid how difficult it is to find a nice tutorial with decent planning in the video by someone that speaks at least decent English. Thanks!
Thanks for watching and you’re welcome!
The best part about this is that you can easily create charts & snippets from the database which you can embed in your website (with real time updates!) That is truly a great option.
My only concern is concureent access and throughput, which probably isn't that great. But for many applications, ut'a definitely sufficient.
>2017
>not using Jupyter and Pandas :)
But seriously: good video! Not many programming yt-videos have such a nice paced explanation
I actually never thought about doing this even though I use google's javascript API all the time in spreadsheets.
It's pretty powerful especially when teams are already collaborating inside of Google Sheets.
This video was amazing. Simple and easy to follow if you have had any experience with python. Great work
Great video. I had a permission issue but i changed the url based on your description and enabled both google drive and google spreadsheets and it worked like charm. Thanks again!
Thanks for watching!
That is a thing of beauty. I am a Google Sheets maniac. I learned some Python a while ago but thought I'd need Javascript to write scripts for Sheets. This video is a real eye-opener.
If only all tutorials were like this tutorial
If the spreadsheet updates quick enough, cant you make animations by coloring the cells?
Just tested, seems like its too slow :/
Man, and I thought I stumbled on something ridiculously delightful :D
darn would have been really funny to see a video playing in sheets with the cells as pixels.
I'm definitely going to try that and make it work somehow
Did you make it work somehow?
Really handy for smaller applications where you don't need any headers or relational links. You can get both of those with a little bit of workarounds, but if you needed those, you might as well have put the stuff in a more conventional database anyway.
I don't know coding but I loved it
Yeah. It just feels satisfying seeing how nicely it works. :)
This is so cool! I hated the thought of even hearing the word database for years, this is such a game changer. Very cool video, keep it up.
Thanks for watching, glad it could help!
Good tutorial. Also very easy to understand what you are saying, you have a very clear and understandable voice.
Thanks for watching and for the kind words!
I don't even know how to create a graph in exel but the way this video us made it remains easily interesting enough for me to finish watching it.
I'm so glad I came across this video. It was very nicely done and quickly got me up to speed with gspread, an amazing python library.
best teaching video I have watched in my life. Speed is perfect.
Love this format - great presentation! Such a relief not having to sit through a long into segment...
I think it will be next to impossible to set our rules for such a thing. Sometimes when I watch certain channels I have to watch the same intro again and again ... it is not that they aren't impressive, but it does get tedious after a while.
I guess was just pleasantly surprise when I watched your video. The no nonsense, the speed of progress, the voiceover and the idea. Great job ^_^
That's cool and all, but can it run doom?
Coloured Spreadsheets would allow a Multiplayer experience. Just imagine how great this would be!
Interesting Idea, I wonder what the latency is like.
0:50 twilio sighting (upper right hand corner)
I tired to use the Google Sheets API for PHP.
*PTSD flashbacks*
Never again.
Why?
Que?
Warum?
どうして？
Почему?
Relly good! Clear, straight to the point perfect quality and a very pleasent voice. - And on top I got some new Ideas! Thx
holy after hours of trying to get this thing to work, it finally worked and I'm so excited
I’m glad you got it working!
You are such a great person MAN. I love you for this great stuff.
Can't express how excited I'm right now.
Thanks for watching!
An amazingly clear and simple video that works. And even better you kept it up to date... Go you!
Please remember when inserting into the DB to replace any = signs with "= (unless you need math operation to work)
Why didn't I find it earlier? Just brilliant. On point. No complicacy. Clap worthy really👏👏👏👏👏👏
Interesting indeed. But I still prefer SQLite hahaha.
Was doing this since at least 2010 with Google spreadsheets and PHP. It's nice to see other languages evolving
It’s missing something, though it blew my mind a few years ago and I’m here again because it’s a simple idea yet amazing.
there is a python module that allows you to edit an Microsoft excel sheet
openpyxl
This channel is gonna grow so fast I can see it already
well I 've been searching for such video for a millennium! Thx Twilio
Great video, I've been meaning to do something like this ever since I saw Travis from DevTips use the google sheets as a data base for his email inputs.
I love how it's easy code awesome things with Python (and of course good libs)
If you are still running into errors after updating the scope to: scope = ['spreadsheets.google.com/feeds', 'www.googleapis.com/auth/drive'],
be sure to ALSO enable Google Sheet API
Can you update this? I am getting import errors since it seems that ServiceAccountCredentials is no longer in use
works like a charm, thanks @Twilio!
updating the scope did the trick
Great video, nicely explained
Awesome video! Gonna use this to access my school docs and automatically update them.
This is genius!! No server, built-in data and API!!
i always wanted to learn coding.. this is so easy to understand.. thanks
finally got what i actually wanted :)
awesome work Twilio (y)
Oh you should have shown the Revision History.. that would have been fun to see!
I laughed so hard at "glorious list of perfectly formatted content"
Spl33ney It really was a bit of a jumbled mess, right?
this is the kind of content that I like, subscribed.
Thanks for watching!
Nice. I don't know what i am going to use this for yet. But still super nice to know it's available!
Simple and Effective. Thanks for sharing with us!
I had no idea what was going on but I loved it! :D
Is inserting/deleting rows supposed to take 30 seconds and more? I wanted to use the sheet as a queue. Appending tasks and just working on the first row and deleting it afterwards.
2aimless why not use a deque (deque library)
MamboBean because you might want persistent storage................?
that's true, so you could serialize it using e.g. pickle. but at that point, it'd be better to use a *real* database lol like MySQL or SQLite
agreed! according to my book: "Keeping a limited history is perfect for "collections.deque"
such a nice job of keeping it simple and clean. Thank you! Subscribed.
Screen text so sharp it almost cuts. This is a REALLY great video. Good job Twilio!
How did you record it?
I'm a noob but I love this so much! Thanks for this introduction, eventhough I'm late to the party. :-)
Never too late to party with code 🎉
what if i have an error?:
"message": "Insufficient Permission"
I got it!
If you have "403" Problem on Windows -
1)Uninstall gspread 2.0.0 and install gspread == 0.6.2 (www.versioneye.com/python/gspread/0.6.2)
2)Turn on "Google sheets API
3)Change "scope" to scope = ['spreadsheets.google.com/feeds',
'www.googleapis.com/auth/drive']
as Twillio said.
Thank you!
adding the googleapis.com/auth/drive fixed it for me - thankyou!
same here !
True damn hero here
Can you show your full Line 4? The one he pinned isn't working for me
You still need to format the data with headers to make it understandable.
In python you could do it like this (although use with caution as I haven't tested it). In fact, there may already be an api solution for this already
```python3
def importData(sheet):
headers = sheet.row_values(0)
result = []
for i in range(1, sheet.row_count):
row = {}
for j in range(headers):
row[headers[j]] = sheet.cell(i, j)
result.append(row)
return result
```
Final note, you should be going full postgresql if you are just looking for a simple project like this. It would be better to use firestore or dynamoDB instead. It's faster and is better equipped at handling and organizing large volumes of data with minimum setup required. Plus I think their dashboards look pretty good.
I agree, formatting would be essential in the final application. This project is mainly for use in situations where the data is already being collaborated on by teams of devs and non-devs inside of Google Sheets and are unlikely to want to work on that data in a new application. This data can still be used to create interesting tools to help analyze that collaboration effort without having to build and synchronize a separate datastore.
@@twilio interesting point but I guess that's why you have VBA for. Although granted it would be a lot more difficult to setup than this... Actually there are extensions you can implement that enables the user to download the data as csv or xlsx. Feels that would be a lot more user friendly. As for teaching, I see nothing wrong with storing the data as a global variable (for now at least)
Indeed, it can be downloaded an worked with for that individual. We have found this useful as a stopgap between "all collaboration and analysis/tools is done in the Google Sheet" to "brand new tool is built to handle the project". With a distributed team, the easiest collaboration is sometimes achieved with the cloud spreadsheet. With data volumes pretty low on most of these projects it has been easier at times to simply work against that data that already exists to provide a different view or way to work with it.
Fully agree with any project that will last longer term or generate higher volumes of data this would not be a great solution. It's a good temporary solution for small projects though :)
Guess you have me. Whatever works and as long as the client is happy :3 Do you do a lot of enterprise code?
Another programmer from Pennsylvania! Who woulda thought.
Thanks for watching! -A programmer from the Keystone State, probably
I really thank for your support.
I m gettin excited when I see something like this. How did you learn this? and which university you graduated?
Just google: "Python beginner course" and click the first free link.
This is really Cool. Especially for people who have had no Background in Dbs.
Nice practical emphasis Twilio Team.
Keep it up!
I was in need of that, you saved me. Thanks ! ❤
Thanks for watching and glad we could help!
Amazing really, I'm just starting to learn Python and all I can only say really simple and impressive , Thanks for the video.
Wow. This is powerful!
Looking forward to using this for a project
I did it from my Windows 10 Ent with python 3.6 worked seamlessly. thank you
Now I can say that I understood a python code :)
Nice and clean tutorial. All relevant information. Good job.
Or you could just use your favorite SQL Database instead.
Not the same thing, uploading to the cloud has a lot of benefits, several people can add into the cloud without having to run an SQL server or a machine all the time.... And not to mention the security of the information as well, having it up on the cloud
this channel deserves more subscribers
If all programming classes had their weeks of content completely removed and replaced with this video, the software development industry would boom with smart, experienced, and eager programmers
This looks so cool! Can't wait to try it! Awesome video
Great tutorial Twilio... amazing
Congratulations, You Just earnt my subscription, Awesome, simple, and lot's of Ideas Just came to mind. Thanks. simple, readable, even not in full screen, easy explained, direct instructions. and etc.
Hmm... saved for later, when i actually know what you are talking about^^ currently learning javascript to get custom functions for GS
amazing tutorial, perfect voice for narration
Thanks for watching and for the kind words!
Thank you so much for this. SUPER STRAIGHT FORWARD and just what I need. God bless
Waho i didn't know your channel, it seems to be a mine of gold !
Thanks for watching!
INSTANTLY SUBBED When I Heard Many More Things Coming
this is really intresting. but i have a question ( i did not do any digging ): i m working on a node js app so is it possible to do this in javascript ? or is it possible to use python like you did and somehow integrate it in the node app ? And thanks :)
Yes buy for any enterprise facing project you will still need to use database
I did exactly what was in the video and it worked perfectly. However after a couple days it gave me an Invalid Credentials error when I leave my program running
and I'm not sure what that happened
You may want to try the code listed in this GitHub issues answer if you have long running code. You may need to use a refresh token: github.com/burnash/gspread/issues/333#issuecomment-166398290
Great voice. Thanks for introducing the new concept.
I think you could do most of that with Google Apps Script itself. No need for python.
Very interesting, I don't see an actual use for me right now but looks really fun I'd like to try it out XD