Are you looking to level up your google sheets game with automation? In the guide below we’ll be exploring how to use google sheets with python 🐍 Show
Learning Outcomes
Module ImportsIn order to run the module imports below you will need to have installed google’s recommended python client: A quick way to do that would be to run the following command and to restart your Jupyter Notebook server:
Additionally I would encourage you to download the gcloud software development kit (SDK), which will come in useful for deploying and access your Google Cloud Platform resources: You can find more information on how to do that from:
Download PygsheetsWe will be using a python package called pygsheets to easily interact with a new google sheet that we will create. If you’re using anaconda you can navigate to your terminal and install the latest version of this package with:
Alternatively if you’re using pip or pip3 to manage your packages you can type either:
As you can see from above I’ve previously installed pygsheets, after you’ve installed the python package if you’re working from a jupyter notebook, then please restart the Kernel:
Authenticating To A Google Cloud Project With A .JSON Service Account KeyUsing a previously obtained local .json file you can authenticate to your google service account.
Create A New Google Sheet + Obtain The Unique IDFirstly we are going to create a new google sheet, then we’ll obtain the id of that specific google sheet which can be found within the URL: Google Sheet Wizardry With Python CommandsAuthenticating With Google Sheets With PyghseetsYou can successfully authenticate to google sheets with a .json key like so:
How To Connect To a Specific Google sheetNow that we’ve authenticated pygsheets with our google cloud project let’s connect to a specific google sheet. In order to do this we will need to grab the URL link and share the spreadsheet.
There are three ways to open the spreadsheet:
🔥 Let’s showcase all three and you can take your pick 🔥
How To Select A Specific Google WorksheetLet’s select the automatically created worksheet called Sheet1:
Accessing Rows & Columns Meta-dataAfter uploading a dataframe into a google sheet, you can see how many columns and rows you have with:
How To Upload Data To A Google Sheet From A Pandas DataFrameI am going to use the keyword data that I originally downloaded from Ahrefs, however if you have another csv that’s absolutely fine. I’d encourage you to simply read any csv file and to create a pandas dataframe:
To upload a dataframe to this worksheet we can use the following syntax:
It’s worth remembering that when you run the above command you will overwrite any cell values. However if you prefer to progressively append new rows to the bottom of the worksheet, please use the below syntax:
Pro Tip: If you don’t have enough rows within your existing worksheet, then uploading a pandas dataframe might fail! In order to account for this you can check the existing number of rows within the worksheet vs the number of rows within the dataframe and add extra rows if required. We will do the same checking for the columns too!
How To Download A Pandas Dataframe From A Google Sheets Specific WorksheetWe can also get data directly from any google sheets worksheet in the form of a pandas dataframe:
How To Get All Of The Google Sheet Values In A Python FormatWe can also get all of the values in a list of lists:
In my case, notice how this has picked up all of the empty spaces that are located on the right hand side of the worksheet:
A way to quickly remove the empty strings per list would be to do a nested list comprehension:
How To Get Cell Ranges In Google Sheets With PythonYou can also extract specific ranges (columns and rows) similar to your excel functions:
How To Get A Single Row For Extracting The Column HeadersWe can get a single row with:
☝️☝️☝️ Also notice how we’ve changed the include_tailing_empty to False which automatically removed any empty strings in that row ☝️☝️☝️ How To Extract A Single ColumnSometimes you might want to select a specific column from your worksheet:
How To Sort By A ColumnYou can easily sort the google sheet by a specific column with:
Its worth knowing that np.nans (not a number) are treated as large numbers within google sheets. Therefore let’s clear the sheet, remove all of the nans before uploading the dataframe, then we will perform this operation again. How To Clear A Google Sheet With PythonYou can clear all of the existing rows and columns within the sheet by referencing the same workspace variable (wks) and the .clear() syntax:
Now I’d recommend removing any nans before uploading your dataframe! So let’s remove any np.nans within the Volume Column:
Let’s try sorting again by the Volume column with pygsheets:
How To Loop Over Every RowYou can iterate over row in your google sheet with a for loop:
Updating Specific Parts Of Your Google SheetUpdating parts of your Google sheet gives you the flexibility to do a raw data dump into google sheets. Afterwards and if other situations or API’s change, you can specifically update any column, row or a range within your google sheet. How To Replace Specific Values Within Your Google SheetReplacing values within your google sheets is incredibly easy and can be done via:
Let’s for example try replacing any np.nans (not a number) in the Clicks and CPS column:
Although this has indeed replaced the “NaN” values, the operation has occurred across all columns and rows within the worksheet. This might not be exactly what you’re looking for. For example maybe you’re simply looking to replace cell values within 2 columns? Introducing Find And Update Values
We will perform the following operations:
How To Update A Range of ValuesIf you’d like to update a range of values use the following syntax, for this example we’ll assign the selected nan values to “Other”:
As you can see, we’ve only updated the CPC and Clicks column “NaN” values with “Other” and the other column values remain the same! In the example above we’ve specifically referenced a cell_list, however you could also use the two methods below:
Column Data ManipulationHow To Add Multiple ColumnsTo add columns to your existing worksheet you can use this syntax:
How To Get The Column Names With The Column Index PositionsFirstly we will extract all of the headers on row 1, then we will wrap this variable with enumerate and convert it into a python dictionary. This will allow us to create a lookup table for index positions! Notice below how we have also changed:
The reason why is that we just want to make sure that if we had empty column values between tables, we would also pick up any empty string values between columns.
Now let’s remove any of the enumerated headers that have an empty string with a list comprehension:
We can then convert this list of tuples into a python dictionary to create a look up table for columns versus their index positions:
How To Delete ColumnsYou will need to pass two parameters when deleting columns via pyghseets:
As we can see the highest index position that we have as a true column from row 1 is 10. Therefore let’s delete all of the columns from index position 12 and greater: Pro tip: Now the reason why it is 12 and not 11 is because pygsheets starts its index positioning for columns and rows at 1, whilst python starts its indexing at position 0. Also pygsheets is inclusive with the starting index (1 means from 1 onwards, rather than column 2 onwards).
You can now easily delete multiple columns by creating your own column index with enumerate! How To Insert ColumnsThe following syntax allows you to insert multiple columns:
How To Update A Single ColumnIn order to update a single column, use this syntax:
We can use the previous lookup table that we created to search for a specific column:
Protip: If you need to create a list of a specific size in python use the following code:
How To Adjust Column WidthDo you need to adjust specific columns to be a certain width? No problem!
Also its worth remembering that whenever you use wks.clear(), all of the formatting will be erased from the worksheet. We can just provide pygsheets with a start index and end index for columns with a specific pixel size like so:
Row Data ManipulationHow To Select A Single RowTo select a single row you can just use:
How To Insert Multiple RowsSelecting multiple rows is very easy and can be done like so:
Let’s find the last row to add a row, and we can insert a new row at index position 3.
Pro Tip: When you insert new rows, they must be below the index number of the final row. If you need to create more rows, I’d recommend using this method instead:
How To Delete RowsTo delete rows, utilise this syntax:
Formatting Google Sheets In PythonFrozen Rows + ColumnsYou can easily set the number of Frozen Rows + Columns with the two commands:
How To Bold CellsLet’s bold all of the cells in the first row:
The first image below shows the BEFORE, and the second image shows the AFTER after of applying the DataRange based upon the model cell! Before: After: Batching Your API CallsIf you are going to make a lot of requests to the Google Sheets API, its recommended to batch your API requests. This reduces the chances that your API requests will be unsuccesful due to rate limits. The way that pygsheets implements API batching is as follows:
How To Share A Google Sheet With An Email Address In PythonYou can easily share any connected spreadsheet with:
Additionally you can include an email message to your user!
How To Export A Google Sheet To A .CSVAlso its possible to easily export your google sheet to a .csv file with:
However we’ll need to enable the Google Drive API for this to work, so quickly go to your Google Cloud Project and activate this API for the following command to work:
How To Convert A Google Sheet Into JSONSometimes when communicating with another developer, they might want to receive your data in JSON format. You can easily turn your google sheets data into a JSON API and feed it directly into another REST API by chaining these two commands:
This will do two things:
How To Use Google’s Python Native API For Google SheetsThe above commands solely work within Pygsheets, however its important that you learn how to connect with Google’s Native Python API for google sheets. How To Connect To The API
Sending Your First RequestIn this tutorial, we are going to be specifically look at the batchClear, batchGet, batchUpdate as these three end points are incredibly useful.
Let’s Understand This Step By Step:
Clearing A Column / Range With batchClear
How To Add Data Validation Boxes With batchUpdateAdding data validation boxes is relatively easy, the code below will add several data validation boxes to the second column.
How To Retrieve A Range of Cells
Hopefully this guide has provided you with a comprehensive overview of how best to use
Google Sheets with Python!
What's your reaction?This website contains links to some third party sites which are described as affiliate links. These affiliate links allow us to gain a small commission when you click and buy products on those sites (it doesn't cost you anything extra!). understandingdata.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for website owners to earn advertising fees by advertising and linking to Amazon and any other website that may be affiliated with Amazon Service LLC Associates Program. How do I read a Google Sheet in Python?Project to Read Data From Google Sheets using Python Details
The objective of the project is to create a GUI Window that will display data. This displayed data is read from a google sheet. For creating the GUI Window, we are using the Tkinter Module. And we are going to use the gspread library and oauth2client.
How do I read a Google Sheet in Pandas?Here's a quick guide to how it's done.. Install the packages. First, open a Jupyter notebook and install the GSpread Python package by entering ! ... . Authenticate with Google Sheets. ... . Open the Google Sheet with Python. ... . Select the specific worksheet. ... . Bring it all together.. How do I read a Google Sheet in Python Colab?Importing data from Google Sheets. from google.colab import auth.. import gspread.. from google.auth import default.. worksheet = gc. open('Your spreadsheet name').sheet1.. print(rows). How do I connect Google Sheets to Python?Reading and writing to Google Spreadsheets using Python. Head over to the Google API Console.. Create a new project by selecting My Project -> + button.. Search for 'Google Drive API', enable it.. Head over to 'Credentials' (sidebar), click 'Create Credentials' -> 'Service Account Key'. |