Now, we need to provide access to the google sheet so that the API can access it. And finally, we will upload the results in a separate sheet. We can do this by using a simple groupby in pandas. Now, we will do a very basic task and calculate how many runs are scored by each of the batsmen. We have a ball by ball data of the complete match in the spreadsheet. We will read the commentary data of the India Bangladesh cricket match. In the next section, we will read and modify the data in the spreadsheet. Our google service account is ready to use. Now, a JSON file will be downloaded which contains the keys to access the API.
We will give it the complete access so that we will be able to read as well as write the spreadsheets and download the JSON file of the credentials. Similarly, we will provide access to our service account. Then, share the google spreadsheets with other people and provide permission like edit or view only.Finally, click on the What credentials do I need? button. And also we are not using any cloud-based compute engine for our application. Select the Application Data in the next question as we do not require any user data to run our application. We will be calling the API from a non UI based platform so select Other non-UI (e.g. Now, select Google Drive API in the type of API required question.Click on the Create Credentials button to continue. Once you have enabled the required APIs in your project then it’s time to create credentials for the service account.It will allow you to access the resources from Google Drive. Google Drive API will look something like this. You would be able to read and modify the content present in the Spreadsheets. It will allow you to access Google Spreadsheets. Google Sheets API will look something like this.Then, in the search bar, search for these APIs and click on the enable button.Consequently, we will add two APIs for our project. Click on the Enable APIs and Services button to search for the APIs that Google provides. Now, that our project is created we need to enable the APIs that we require in this project.Then provide the project name and the organization name which is optional.If you have a Google account, you can follow these steps to create a Google service account. First of all, make sure that you have a google account. It is a special type of account that is used to make authorized API calls to Google Cloud Services.
In order to read and update the data from google spreadsheets in python, we will have to create a Service Account.
In case you are unfamiliar with Python, do have a look at our free course Introduction to Python We are going to read the cricket commentary data from the spreadsheet and find out the number of runs scored by each batsman and then upload the results into a separate spreadsheet. We will make use of the Google APIs to read google spreadsheets data using python and we will also update the data in the spreadsheet using python. In this article, we will see a step by step process to set up a Google service account. There are multiple advantages of report automation like you would be able to save time on data collection and removing typos and focus would be more on the analysis part. Consequently, it would be great if we just run a script, and data is uploaded in the spreadsheet and the report is prepared with just a click.
But many of us fail to understand how to automate some tasks and end in the loop of manually doing the same things again.įor instance, we often spend hours daily extracting data and then copy-pasting to spreadsheets and creating reports leading to excessive time consumption.
Moreover, in today’s era where success is dependent on speed, automation of myriad repetitive tasks play a key role in any industry and at the most basic level of functionality. Read and Write data in Google Spreadsheets using PythonĪutomation of work has been one of the quickest ways to reach functional efficiency.Learn how to setup a Google service account.