Can python connect to google sheets?
This post is inspired by Patrick McKenzie’s reminder that sometimes you don’t need a database: Show
In this tutorial, we’ll use Anton Burnashev’s excellent gspread Python package to read, write, and delete data from a Google Spreadsheet with just a few lines of code. Google Drive API and Service AccountsAt the risk of being Captain Obvious, you’re going to need a spreadsheet if you want to follow along with this post. If you don’t have one on hand that’s full of juicy data, might I suggest you make a copy of this spreadsheet with contact information for all United States legislators? (Side note: Ian Webster uses this data in conjunction with Twilio to make it easy for citizens to call congress). To programmatically access your spreadsheet, you’ll need to create a service account and OAuth2 credentials from the Google API Console. If you’ve been traumatized by OAuth2 development before, don’t worry; service accounts are way easier to use. Follow along with the steps and GIF below. You’ll be in and out of the console in 60 seconds (much like Nic Cage in your favorite Nic Cage movie). There is one last required step to authorize your app, and it’s easy to miss! Find the client_email inside client_secret.json. Back in your spreadsheet, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send. If you skip this step, you’ll get a gspread.exceptions.SpreadsheetNotFound error when you try to access the spreadsheet from Python. We’re done with the boring part! Now onto the code. Read Data from a Spreadsheet with PythonWith credentials in place (you did copy them to your code directory, right?) accessing a Google Spreadsheet in Python requires just two packages: Install these packages with:
Then paste this code into a new file called spreadsheet.py:
Run python spreadsheet.pyand marvel at the glorious, well-formatted data. Insert, Update, and Delete from a Spreadsheet with PythonWe’ve just scratched the surface of gspreads’ well documented and comprehensive functionality. For instance, we extracted the data into a list of hashes, but you can get a list of lists if you’d prefer: Or you could just pull the data from a single row, column, or cell:
You can write to the spreadsheet by changing a specific cell:
Or you can insert a row in the spreadsheet:
You can also delete a row from the spreadsheet: And find out the total number of rows: Check the gspread API reference for the full details on these functions along with a few dozen others. Using Google Spreadsheets with Python opens possibilities like building a Flask app with a spreadsheet as the persistence layer, or importing data from a Google spreadsheet into Jupyter Notebooks and doing analysis in Pandas. If you want to start playing with Python and Twilio, check out our Python quickstarts. If you build something cool, please let me know. You can find me at or @greggyb. And if this post was helpful, please share it with someone else who might dig it. Many thanks to Devin and Sam for the reviews, to Google for Sheets, and most of all, to Anton for gspread. Can Python automate Google Sheets?Pygsheets is a simple python library that can be used to automate Google Sheets through the Google Sheets API. An example use of this library would be to automate the plotting of graphs based on some data in CSV files that we can export to Google Sheets.
How do I send data from Google Sheets to Python?Search for 'Google Drive API', enable it. Select Compute Engine service default, JSON, hit create. Open up the JSON file, share your spreadsheet with the "[email protected]" email listed. Save the JSON file wherever you're hosting your project, you'll need to load it in through Python later.
How do I write in Google Sheets using Python?Go to https://developers.google.com/sheets/api/quickstart/python.. change the google sheet ID.. change the JSON file.. change the range of sheet according to your data. ( if needed). Can Python update Google sheet?To work with Google Sheets in Python, first, we have to install gspread and oauth2client. In addition to that, we'll use Pandas to read our local data and update it to Google Sheets.
|