Upload Spreadsheet to Google Sheets With Python

Overview

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

Introduction

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

For instance, we oft spend hours daily extracting information and and so copy-pasting to spreadsheets and creating reports leading to excessive fourth dimension 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. At that place are multiple advantages of report automation like you lot would be able to salvage time on data collection and removing typos and focus would be more than on the analysis part.

In this article, we will come across a step by step process to gear up a Google service account. We volition make utilise of the Google APIs to read google spreadsheets information using python and we will as well update the information in the spreadsheet using python. We are going to read the cricket commentary information from the spreadsheet and find out the number of runs scored past each batsman and and so upload the results into a split spreadsheet.

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

Table of Contents

1. Create Google Service Account
2. Read Information from Google Sheets
3. Update Data in Google Sheets

Create Google Service Account

In lodge to read and update the information from google spreadsheets in python, nosotros 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 accept a google account. If you have a Google account, y'all tin can follow these steps to create a Google service account.

1. Become to the developer's panel.  Now, you will encounter something like this. Click on the Create Project button.
2. Then provide the projection name and the organisation name which is optional. Then click on the create button.
3. Now, that our project 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 volition add ii APIs for our project.
   • Google Sheets API
   • Google Bulldoze API
4. So, in the search bar, search for these APIs and click on the enable button.
5.  Google Sheets API will look something similar this. It will allow you to access Google Spreadsheets. You would exist able to read and modify the content present in the Spreadsheets.
   Google Drive API will look something like this. It volition allow you to access the resource from Google Drive.
6. In one case you have enabled the required APIs in your projection then it's fourth dimension to create credentials for the service account. Click on the Create Credentials button to proceed.
   

7. Now, select Google Drive API in the type of API required question. We will be calling the API from a non UI based platform and so select Other non-UI (e.1000. cron job, daemon). Select the Awarding Data in the side by side question as we do not crave any user data to run our application. And as well we are not using any cloud-based compute engine for our awarding. Finally, click on the What credentials practise I need? button.
8. Then, share the google spreadsheets with other people and provide permission like edit or view only. Similarly, we will provide admission to our service business relationship. We will give it the consummate access so that we will exist able to read as well 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 set to use. In the side by side section, we will read and modify the data in the spreadsheet.

Read Information from Google Sheets

We volition read the commentary information of the Bharat Bangladesh cricket friction match. You tin access the data here.

We have a ball by brawl information of the complete friction match in the spreadsheet. Now, we will do a very basic chore and calculate how many runs are scored by each of the batsmen. We tin can do this past using a uncomplicated groupby in pandas. And finally, nosotros volition upload the results in a separate sheet.

Provide access to the Google Sheet

Now, we need to provide access to the google sheet then 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 re-create it.

Then click on the Share push on the Spreadsheet and provide access to this client email.

Now, nosotros are ready to code and access the sail using python. The following are the steps-

i. Importing the Libraries

We will utilize the gspread and oauth2client service to authorize and make API calls to Google Deject Services.

Y'all can install the libraries using the following commands.

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

2. Define the scope of the application

Then, we volition define the telescopic of the awarding and add the JSON file that has the credentials to access the API.

iii. Create the Sheet Instance

Use the client object and open the canvass. You simply need to pass the title of the sheet as the argument. Also, you tin can pass the URL of the sheet if you desire to do so.

Access Particular Canvas: We accept multiple sheets in a single spreadsheet. You can access particular google spreadsheets with python past providing the index of that canvass in the get_worksheet function. For the kickoff sheet, laissez passer the index 0 and so on.

Basic functionalities

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

four. Get all records

So, nosotros will go all the data nowadays in the sheet using the get_all_records function. It will return a JSON string containing the data.

5. Catechumen the Dictionary to the Dataframe

In information science, pandas is ane of the most preferred libraries to do data manipulation tasks. So we will first convert the JSON cord to the pandas dataframe.

In case you are not comfy with the pandas, I would highly recommend y'all to enroll in this free grade: Pandas for Data Analysis in Python

six. Grouping Batsman

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

Now, nosotros will 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 Dissever Canvas

   Firstly, we volition create a separate sheet to store the results. For that, use the add_worksheet office and pass the number of rows and columns required and the title of the canvas. Later on that get the case of the 2d canvass by providing the index which is 1.

   Once you lot run this control, y'all will see that a separate canvass is created.

   

2. Update values to the canvas

   Then, convert the runs dataframe into the 2-D list and utilise the role to add together values in the sheet. With this unmarried line of code, you can update the sheet. Then, y'all will 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 right from your python console. We downloaded the spreadsheet data and converted information technology 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 lot want to brush upwards your spreadsheet concepts, I recommend the post-obit commodity and form-

• Microsoft Excel: Formulas & Functions
• ten+ Uncomplicated Still Powerful Excel Tricks for Data Analysis

I hope this helps you in automating scripts and saving loads of your valuable fourth dimension. Reach out in the annotate section in example of any doubts. I will exist happy to help.

hyderbuteatelf.blogspot.com

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

Share this post