Overview
IntroductionAutomation 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 key role in any industry and at the most basic level of functionality. But many of us fail to understand how to automate some tasks and end in the loop of manually doing the same things again. Show For instance, we often spend hours daily extracting data and then copy-pasting to spreadsheets and creating reports leading to excessive time consumption. 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. 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. In this article, we will see a step by step process to set up a Google service account. 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. 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. In case you are unfamiliar with Python, do have a look at our free course Introduction to Python Table of Contents
Create Google Service AccountIn order to read and update the data from google spreadsheets in python, we will have to create a Service Account. It is a special type of account that is used to make authorized API calls to Google Cloud Services. First of all, make sure that you have a google account. If you have a Google account, you can follow these steps to create a Google service account.
Now, a JSON file will be downloaded which contains the keys to access the API. Our google service account is ready to use. In the next section, we will read and modify the data in the spreadsheet. Read Data from Google SheetsWe will read the commentary data of the India Bangladesh cricket match. You can access the data here. We have a ball by ball data of the complete match in the spreadsheet. Now, we will do a very basic task and calculate how many runs are scored by each of the batsmen. We can do this by using a simple groupby in pandas. And finally, we will upload the results in a separate sheet. Provide access to the Google SheetNow, we need to provide access to the google sheet so that the API can access it. Open the JSON file that we downloaded from the developer’s console. Look for the client_email in the JSON file and copy it. Then click on the Share button on the Spreadsheet and provide access to this client email. Now, we are ready to code and access the sheet using python. The following are the steps- 1. Importing the LibrariesWe will use the gspread and oauth2client service to authorize and make API calls to Google Cloud Services. You can install the libraries using the following commands. !pip3 install gspread
! Python Code: 2. Define the scope of the applicationThen, we will define the scope of the application and add the JSON file that has the credentials to access the API. 3. Create the Sheet InstanceUse the client object and open the sheet. You just need to pass the title of the sheet as the argument. Also, you can pass the URL of the sheet if you want to do so. Access Particular 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 function. For the first sheet, pass the index 0 and so on. Basic functionalitiesThe API provides some basic functionalities such as the number of columns by using col_count and get the value in a particular cell. Here are some examples of the same. 4. Get all recordsThen, we will get all the data present in the sheet using the get_all_records function. It will return a JSON string containing the data. 5. Convert the Dictionary to the DataframeIn data science, pandas is one of the most preferred libraries to do data manipulation tasks. So we will first convert 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 free course: Pandas for Data Analysis in Python 6. Grouping BatsmanThen, we will create a groupby of the number of runs scored by a batsman and upload that dataframe in the separate sheet. Now, we will add this dataframe into the google sheets. Update Data in Google SheetsThe following are steps to update data in google sheets.
End NotesTo 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 right from your python console. We downloaded the spreadsheet data 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 case you want to brush up your spreadsheet concepts, I recommend the following article and course-
I hope this helps you in automating scripts and saving loads of your valuable time. Reach out in the comment section in case of any doubts. I will be happy to help. How do I add data to Google Sheets using Python?Here's what you would need to establish the Python to Google Sheets connection.. Python 2.6 or later.. The pip package.. API (Application Programming Interface) enabled the Google Cloud Platform project.. Authorization credentials.. A Google account.. How does Python store data in Google Sheets?Search for 'Google Drive API', enable it. Select Compute Engine service default, JSON, hit create. Open up the JSON file, share your spreadsheet with the "[email protected]" email listed. Save the JSON file wherever you're hosting your project, you'll need to load it in through Python later.
How do I upload data to Google Sheets?Import data sets & spreadsheets. On your computer, open a spreadsheet in Google Sheets.. Open or create a sheet.. At the top, click File. Import.. Choose a non-password-protected file in one of these file types: . ... . Select an import option. ... . Optional: If you import a plain text file, like . ... . Click Import.. Can I Automate Google Sheets with Python?Pygsheets is a simple python library that can be used to automate Google Sheets through the Google Sheets API. An example use of this library would be to automate the plotting of graphs based on some data in CSV files that we can export to Google Sheets.
|