We all know the power of Google Sheets and its numerous utilities that helps the different types of businesses around the world. Many of the businesses like to perform their operations via Google Sheets. Moreover, they are not limited to business but to the developers too for collaborative documentation.
Though Google Sheets are a powerful collaborative way to perform data operations, most of the time there are so many manual tasks that may be tiresome.
In this blog, we are going to connect the Google Sheet with our Python program so that all the manual tasks can be automated. For example, we have to read a column of data and write its respective price from a CSV file which is in our local systems. There are so many possibilities once we connect these two powerful tools. So enough of the talking let's dive right into the procedures.
At first, we need to create a credential file to secure our connection with Google Cloud Platform which allows us to connect them.
Open this URL //console.cloud.google.com/ to create a new project in the Google Cloud Platform console:
Now click on the My First Project drop-down on the top left corner. You will be shown the following pop up to create a new project:
Click on the New Project Option:
Now fill-up the form by adding your project name and if you have an organization listed you can add that also by clicking the browse option, otherwise leave it as it is. Then click on the create button after filling up all information.
This will take some time and you will get the notification about the creation of a new project as given below:
Now we have to enable Google Drive API and Google Sheets API. We can do that by searching on the top search bar with the same name:
You will be redirected to the detailed page of the API searched as given below:
Click on the Enable button. You will be redirected to the dashboard of the API you have enabled:
Now again repeat the same steps to enable Google Sheets API:
Enable the Google Sheets API:
Now again you will be redirected to the dashboard page of Google Sheets API. Now we have to create a credentials file which we will be using later on for our python project.
Click on the Create Credentials button on the top-right corner of that page as shown below.
Now in Select, an API choose Google Drive API from the drop-down:
Now choose Application Data and No option for the next question respectively as shown below and hit Next Button:
Now we will be redirected to the Service Account Details Page. Fill up the Service Account Name as per your desire and click on CREATE AND CONTINUE button:
Now we have to give the created service account access. Give the Owner access from the dropdown as shown:
Then click on continue:
Now skip the 3 numbered forms and click on DONE:
Now you will be redirected to the credentials page where the recently created service account can be found:
Now on the last Details of Service Account, there will be a recently created service account email. Click on that:
Then you will be redirected to the specific page for that service account:
Now click on the KEYS tab on the top:
Now click on the ADD KEY button on that page and Choose to Create New Key:
Choose the JSON Key Type from the Pop Up Prompt:
And click on the CREATE button.
The credentials file will be downloaded on your local system as a JSON file:
Now do not share this file with anyone. The name of the JSON file will be as per your service account details. Copy it in a secure folder and rename it as credentials.json as shown below:
Open up the credentials file and copy the client_email value from it.
Before diving into the python code, we have to create a spreadsheet to communicate.
Open //docs.google.com/spreadsheets/u/0/ and create a new Spreadsheet:
And click on the share button and paste the copied client_email from the credentials.json file before:
Now Change the Sheet name from Sheet1 to Test:
Add the following data in your spreadsheet or you can add your data as in the given format:
test.csv fileNow that sheets are all set up it’s time to dive into our python code.
Open the folder on which credentials.json reside with Visual Studio Code:
Now we have to create a virtual environment with python to download the dependency modules.
Create a requirements.txt in the same folder and paste the following into it:
requirements.txtNow we have to create a virtual environment to install those dependency files.
Click on Terminal and Click on New Terminal and Enter the following command to create a new virtual environment
$ python3 -m venv pythontosheet
If you are having a problem creating the virtual environment go through these links
For Windows: //docs.python.org/3/library/venv.html
For Ubuntu: //linuxize.com/post/how-to-create-python-virtual-environments-on-ubuntu-18-04/
After creating the virtual environment you can see the file in your terminal by hitting the following command:
$ ls
Activate the created virtual environment by:
$ source pytosheet/bin/activate
Now finally install the requirements file with the following command:
$ pip3 install -r requirements.txt
If you have not installed Pip3 previously follow the guide below:
For Ubuntu: //linuxize.com/post/how-to-install-pip-on-ubuntu-18.04/
For Windows: //phoenixnap.com/kb/install-pip-windows
Now create a python script file with the name of pythontosheet.py and paste the following code in it:
pythontosheet.py fileNow go in the browser and copy the spreadsheet id from the URL as shown below:
Just copy the text between the d/ and /edit and you are all set. After copying the ID, paste it in the code by replacing YOUR_SPREADSHEET_ID as shown below:
All set! It's time for some action.
Run the code and see for yourself!
$ python3 pythontosheet.py
Your output will be something like this:
— — Reading from Google Sheets — — —
— — — — — — — — — — — — — — — — — —
11 cells retrieved.
— — — — — — — — — — — — — — — — — —
[[‘id’, ‘name’], [‘1’, ‘Merola’], [‘2’, ‘Alonso’], [‘3’, ‘Alric’], [‘4’, ‘Claudie’], [‘5’, ‘Myrle’], [‘6’, ‘Jakob’], [‘7’, ‘Paula’], [‘8’, ‘Dierdre’], [‘9’, ‘Maritsa’], [‘10’, ‘Kenon’]] rows retrieved.
That’s it you have successfully read from the Google Sheet by using Python.
You can also write data into the Sheet. For that create a new tab in the Google Sheet and rename it as Write as shown below:
Now uncomment the last line of code and comment the second last on as given below:
#read_range[]write_range[]
Now again run the program with:
$ python3 pythontosheet.py
Your output will be something like this:
--- Reading from Google Sheets------
------------------------------------
11 cells retrieved.
------------------------------------
[['id', 'name'], ['1', 'Merola'], ['2', 'Alonso'], ['3', 'Alric'], ['4', 'Claudie'], ['5', 'Myrle'], ['6', 'Jakob'], ['7', 'Paula'], ['8', 'Dierdre'], ['9', 'Maritsa'], ['10', 'Kenon']] rows retrieved.
--- Writing from Google Sheets------
------------------------------------
22 cells updated.
------------------------------------
Finally, check out the Google Sheet in the newly created Write Tab:
That is it! Congrats you have successfully communicated between the Python and Google Sheets!
Thanks for reading!
More content at plainenglish.io