How to Get Google to Read to Me

Overview

• Learn how to setup a Google service account
• Read and Write data in Google Spreadsheets using Python

Introduction

Automation of work has been one of the quickest ways to reach functional efficiency. Moreover, in today's era where success is dependent on speed, automation of myriad repetitive tasks play a primal office in any manufacture and at the near basic level of functionality. Merely many of us neglect to understand how to automate some tasks and end in the loop of manually doing the aforementioned things again.

For instance, we frequently spend hours daily extracting data and and so copy-pasting to spreadsheets and creating reports leading to excessive time consumption. Consequently, it would be great if we only run a script, and data is uploaded in the spreadsheet and the written report is prepared with just a click. There are multiple advantages of study automation like you would be able to salve fourth dimension on information collection and removing typos and focus would be more on the analysis part.

In this article, we will encounter a step by footstep procedure to ready up a Google service account. We will brand utilize of the Google APIs to read google spreadsheets data using python and we will besides update the data in the spreadsheet using python. We are going to read the cricket commentary data from the spreadsheet and find out the number of runs scored past each batsman so upload the results into a separate spreadsheet.

In case you are unfamiliar with Python, do take a look at our gratis course Introduction to Python

Tabular array of Contents

1. Create Google Service Business relationship
2. Read Data from Google Sheets
3. Update Data in Google Sheets

Create Google Service Account

In gild to read and update the data from google spreadsheets in python, we will take to create a Service Account. It is a special type of account that is used to make authorized API calls to Google Cloud Services. Get-go of all, make certain that you lot have a google business relationship. If you lot have a Google account, you can follow these steps to create a Google service account.

1. Go to the programmer's console.  At present, yous will see something like this. Click on the Create Projection button.
2. And then provide the project name and the organization name which is optional. Then click on the create button.
3. Now, that our projection is created we need to enable the APIs that we require in this project. Click on the Enable APIs and Services button to search for the APIs that Google provides. Consequently, nosotros will add two APIs for our projection.
   • Google Sheets API
   • Google Drive API
4. And so, in the search bar, search for these APIs and click on the enable push button.
5.  Google Sheets API will look something similar this. Information technology will allow yous to admission Google Spreadsheets. You would be able to read and modify the content nowadays in the Spreadsheets.
   Google Drive API will await something similar this. It will let you to admission the resources from Google Drive.
6. One time yous have enabled the required APIs in your project then it's time to create credentials for the service account. Click on the Create Credentials push button to go on.
   

7. At present, select Google Drive API in the type of API required question. We volition be calling the API from a not UI based platform so select Other non-UI (e.g. cron task, daemon). Select the Application Data in the side by side question as we do not crave any user data to run our application. And also we are not using any cloud-based compute engine for our awarding. Finally, click on the What credentials practice I need? button.
8. So, share the google spreadsheets with other people and provide permission like edit or view only. Similarly, we will provide access to our service business relationship. We will give information technology the complete access then that we volition be able to read also every bit write the spreadsheets and download the JSON file of the credentials.

Now, a JSON file will be downloaded which contains the keys to access the API. Our google service account is ready to apply. In the side by side section, we will read and modify the data in the spreadsheet.

Read Information from Google Sheets

We will read the commentary information of the India People's republic of bangladesh cricket match. You can access the data here.

We have a ball by ball information of the consummate match in the spreadsheet. At present, we will do a very basic task and calculate how many runs are scored by each of the batsmen. We can exercise this past using a elementary groupby in pandas. And finally, we volition upload the results in a separate sheet.

Provide access to the Google Sail

Now, we need to provide access to the google sheet so that the API tin admission it. Open up the JSON file that nosotros downloaded from the programmer's console. Await for the client_email in the JSON file and re-create it.

And so click on the Share button on the Spreadsheet and provide admission to this client electronic mail.

Now, nosotros are ready to lawmaking and access the sheet using python. The post-obit are the steps-

ane. Importing the Libraries

We will use the gspread and oauth2client service to qualify and make API calls to Google Cloud Services.

You can install the libraries using the following commands.

!pip3 install gspread !pip3 install --upgrade google-api-python-client oauth2client                            

two. Ascertain the telescopic of the application

Then, we will ascertain the scope of the application and add the JSON file that has the credentials to access the API.

3. Create the Sheet Instance

Use the client object and open the sheet. You just need to pass the title of the sail as the argument. Also, you can laissez passer the URL of the sheet if you want to do so.

Admission Detail Sheet: We have multiple sheets in a single spreadsheet. You can access particular google spreadsheets with python by providing the index of that sheet in the get_worksheet office. For the first canvass, pass the alphabetize 0 and and then on.

Bones functionalities

The API provides some basic functionalities such equally the number of columns by using col_count and get the value in a particular cell. Hither are some examples of the same.

4. Get all records

Then, we will get all the information present in the sheet using the get_all_records function. Information technology will return a JSON string containing the information.

5. Convert the Dictionary to the Dataframe

In data science, pandas is one of the most preferred libraries to do data manipulation tasks. And then nosotros will first catechumen the JSON string to the pandas dataframe.

In case you are not comfortable with the pandas, I would highly recommend you to enroll in this complimentary course: Pandas for Data Assay in Python

half-dozen. Grouping Batsman

Then, nosotros will create a groupby of the number of runs scored past a batsman and upload that dataframe in the separate sail.

Now, we volition add this dataframe into the google sheets.

Update Data in Google Sheets

The following are steps to update data in google sheets.

1. Create a Split up Sheet

   Firstly, we volition create a separate sail to store the results. For that, use the add_worksheet function and pass the number of rows and columns required and the championship of the canvas. Subsequently that get the instance of the 2d sheet past providing the index which is 1.

   Once you run this command, y'all will encounter that a dissever canvass is created.

   

2. Update values to the sheet

   Then, convert the runs dataframe into the 2-D list and utilise the part to add together values in the canvas. With this single line of lawmaking, you can update the sheet. So, y'all volition get a message of the number of rows and columns updated with some more details.

   

   

End Notes

To summarize, in this article, we dived into understanding various steps involved in the process of creating a service account. And how to read the write in the google spreadsheets correct from your python console. Nosotros downloaded the spreadsheet information and converted it into the pandas dataframe and created a groupby table and uploaded that on the spreadsheet again. This API can be very helpful in the automation of reports.

In example you want to brush up your spreadsheet concepts, I recommend the post-obit article and course-

• Microsoft Excel: Formulas & Functions
• x+ Uncomplicated Yet Powerful Excel Tricks for Data Assay

I promise this helps yous in automating scripts and saving loads of your valuable time. Accomplish out in the comment section in case of any doubts. I volition exist happy to help.

johnsoncoularl.blogspot.com

Source: https://www.analyticsvidhya.com/blog/2020/07/read-and-update-google-spreadsheets-with-python/

Share this post