How to read and write google sheet using Python
Imp Note: if you are looking for reading and writing gsheet using python then this blog will solve your problem. this my 100% guarantee but for this just you have to go slow and read it carefully.
Hello Scholars,
Today we’ll see how to read the data from one google spreadsheet, do some operation on data and again write that data into another google spreadsheet.
Sounds cool? Let’s Start
First, you have to import the following packages using Pip.
pip install google_spreadsheet
pip install google-auth-oauthlib
pip install pandasThen take the ID of google sheet from which you want to read the data.
- Get a sheet ID.
- Go to the spreadsheet (in my case I want to read data from athlete_event sheet) and click on share.
2. Once you click on a share, you will get the page like this so click on copy link.
3. You will get a link in this format.
https://docs.google.com/spreadsheets/d/1cvZswLiDo3LfhnA7RcS8vFqacx73RGor-OZ_FtvyLE8/edit?usp=sharing
Bold text is your google sheet ID. (if you are reading data from someone else sheet then that sheet should be shared with your google id)
- Enable the Google sheet API.
Go to https://developers.google.com/sheets/api/quickstart/python.
Click on ‘Enable the google sheet’ API button and download the JSON file and store in the same folder where our code will be saved.
(if you are using google cloud for the first time then please activate GCP and create a new project)
- Run the following code in your python environment.
In this code, you have to make the following changes.
- change the google sheet ID.
- change the JSON file.
- change the range of sheet according to your data. (if needed)
Once you make these changes, authentication link will come once you run the code so authenticate by logging to your google account.
This code will read data from your google sheet and store this in the pandas dataframe.
Let’s understand the code quickly.
- Imported necessary libraries.
- Defined the scope of the sheet. A scope can be changed as per need.
since we are reading sheet we can choose any one of the first two scopes.
3. Entered the ID and range of sheet. from mentioned ID and data from the selected range will be read by code.
4. Authenticated Google API by a downloaded JSON file. by using this file, we created ‘token.pickle’ file which will be stored in our pc for future use and whenever this pickle file will expire our code will refresh the file.
5. Built the service which will call sheet API and get the data. then finally storing data in pandas dataframe.
6. Here we are storing our credentials in the pickle file so that every time we should not have to go through the process of authentication and sending this pickle file to the server we can easily put it in production.
Now you can do whichever operation you want on your dataframe. (generally, I use this for multiple user inputs so I don’t need to do changes in my code as some variable value changes)
Write dataframe to google sheet.
I have read the data of ‘athlete_events’ sheet and stored it in dataframe. Now I want data of only athletes who won a gold medal in Gymnastics in a ‘gold_medal’ sheet.
cool…. let’s do this.
- Get the ID of the sheet where you want to store your output.
- Determine how much range my output will take.
This code will create a pickle file from JSON file, build service and finally update records to the ‘gold_medal’ sheet.
Once you execute the above code, an authentication window will open and will ask for access. after allowing access for writing to a sheet, authentication successful message will come.
Woooooooooo….. Now our dataframe is in our output sheet.
Thank you ❤️
For any doubts about this blog please feel free to contact me Linkedin
