How to Achieve Google Sheet Automation using Google API Services

In today’s world, success is heavily dependent on the pace at which we work. So automating repetitive work is one of the quickest ways to attain functional performance. But if you find automation to be daunting, you might have a negative approach of just manually doing the tasks over and over again to enter an endless loop of wasting time. So if you are someone who uses Google Sheets regularly to create and maintain data, this blog will be a lifesaver for you. As a leading automation testing services company, we are focused on dedicating our time to our core service by automating repetitive tasks. So in this blog, we will be exploring how to achieve Google Sheet Automation and get the job done in no time. Without any further ado, let’s get started.

The pace isn’t the only advantage that comes with automation. Since repetitive tasks are prone to oversight and manual errors, we will be able to avoid such issues by implementing automation. Now that we have established what we are targeting to achieve, let’s first see how to do it. We will be making use of the available Google APIs to examine and add data in Google Sheets using Python. So you wouldn’t have to spend hours of your time extracting data and then replica-pasting it to other spreadsheets.

Prerequisites:

In order to achieve Google Sheet Automation, you’ll be needing a few prerequisites such as

• A Google account.
• A Google Cloud Platform project with the API enabled.
• The pip package management tool
• Python 2.6 or greater

How to Achieve Google Sheet Automation using Google API Services?

Here are the few steps that need to be followed to start using the Google sheets API.

i. Create a project on Google Cloud console

ii. Activate the Google Drive API

iii. Create credentials for the Google Drive API

iv. Activate the Google Sheets API

v. Install a few modules with pip

vi. Open the downloaded JSON file and get the client email

vii. Share the desired sheet with that email

viii. Connect to Google Sheet using the Python code

Create a project on Google cloud console

In order to read and update the data from Google Sheets in Python, we will have to create a Service Account. The reason behind this need is that it is a special form of account that can be used to make authorized API calls to Google Cloud Services. Almost everybody has a Google account today. In case you don’t, make sure to create one and then follow all the steps and comply with the requirements to create a Google Services account.

1. You will be able to create a new project by going to the developer’s console and clicking on the ‘New Project’ button.

2. You can then assign the project name and even enter the organization name if you prefer to. Once you are done, click on the ‘Create’ button.

3. The next step after creating the project would be to enable the APIs that we require in this it. To access the different API options provided by Google, you have to click on Menu -> APIs & Services -> Library.

4. You have to search and enable the following two APIs from the library as shown below. You can enable them by just clicking on the enable button that appears.

• Google Sheets API
• Google Drive API

The Google Sheets API is the important one that will enable you to read and regulate the data in Google Sheets.

5. Now that you have enabled the required APIs to beat your automation challenge, it’s time to create the credentials for the services account. You can do that by clicking the ‘Create Credentials’ button that can be found in the Credentials menu as shown in the image.

6. Once you click on that, a drop-down list will appear. Choose the ‘Service Account’ option from the list.

7. You would have to provide the Service Account details here in order to continue. That is why we had mentioned that you would have to create one prior to starting this process. Once you have provided the info, you can create the credentials by clicking on the ‘Create and Continue’ option.

8. Similar to how we share the Google spreadsheets with other collaborators by providing them various access permissions like edit or view only, we will have to provide access to our service account as well. Since we have to both read and write in the spreadsheets, you would have to give editing access or not the view-only option.

9. Once the credentials have been created, download the JSON file for the credentials. The JSON file will contain the keys that you will need to access the API. So our Google Service account is now ready to use.

Share the desired sheet with that email

Now that the Services account credentials have been created, you have to provide the email using which you will access the spreadsheet.

Open the Google Sheet that you want to automate and click on the Share button to provide access to this client email. Now, you are all set to code and access the sheet using Python.

Connect to Google Sheet using Python Code

First up, you have to open the downloaded JSON file in PyCharm. You can then create a Python file in the same project folder and start writing your script.

We then have to install two packages (gspread and oauth2client) from PIP. To do that in PyCharm, we have to open command prompt and use the below command

pip install gspread oauth2client

Now let’s take a look at the different segments of the python code one by one to understand it easily and successfully implement it to achieve Google Sheet Automation.

1. Importing the Libraries

We will need both the gspread and oauth2client services to authorize and make API calls to Google Cloud Services.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import print

2. Define the scope of the application

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

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file",
         "https://www.googleapis.com/auth/drive"]

3. Add credentials to the account

Once the scope has been defined, you have to add the credentials to the account.

creds = ServiceAccountCredentials.from_json_keyfile_name("test1-331506-a11c2c0a84fc.json", scope)

4. Authorize the client sheet

The next stage is authorizing the client sheet.

client = gspread.authorize(creds)

5. To open a Google Sheet

There is nothing that can be done without opening the Google Sheet in the first place.

sheet = client.open("Automation").sheet1

6. Get all records

Once the sheet is open, you can get all the data present in the sheet using the get_all_records function. It will return a JSON string that contains the data.

data = sheet.get_all_records()
pprint(data)

7. To get a specific row

Though reading all the data is a great feature, that wouldn’t be needed every single. So this is the code that you can use to get data from a specific row.

row = sheet.row_values(3)  # Get a specific row
pprint(row)

8. To get a specific Column

Similarly, we will also be able to access data from a specific column.

col = sheet.col_values(2)  # Get a specific column

pprint(col)

9. To Get the value of a specific cell

We can even be very precise and access data from a specific cell too.

cell = sheet.cell(1,2).value  # Get the value of a specific cell

pprint(cell)

10. To insert the data into a sheet

We have seen how to read the data, now let’s see how we can insert data.

insertRow = [ 15, "Logesh"]

sheet.insert_row(insertRow, 15) 

11. To delete certain row

Not all data in a sheet will be needed forever and so you can even delete a row of data from your sheet.

sheet.delete_rows(14)

12. To update one cell

If at all you want to change the data in an existing cell, you wouldn’t have to delete the content and then add the new one again. Instead, you can just update the content in the cell.

sheet.update_cell(2,4, "CHANGED")  # Update one cell

13. To Get the number of rows in the sheet

Beyond reading and editing the content in the sheet, you can even get to know the number of rows in a sheet as it might be needed for your automation process.

numRows = sheet.row_count  # Get the number of rows in the s

pprint(numRows)

14. To get the length of the data

Likewise, we can even get the length of the data in the sheet if you want to use that in your automation as well.

pprint(len(data))

Source Code:

Since we have explained everything part by part, now it’ll be much easier for you to go through the source code and understand it clearly.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint

scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file",
         "https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("test1-331506-a11c2c0a84fc.json", scope)

client = gspread.authorize(creds)

sheet = client.open("Automation").sheet1

data = sheet.get_all_records()

pprint(data)

row = sheet.row_values(3)  # Get a specific row

pprint(row)

col = sheet.col_values(2)  # Get a specific column

pprint(col)

cell = sheet.cell(1,2).value  # Get the value of a specific cell

pprint(cell)

insertRow = [ 15, "Logesh"]

sheet.insert_row(insertRow, 15)  # Insert the list as a row at index 4 its will over write

sheet.delete_rows(14)

sheet.update_cell(2,4, "CHANGED")  # Update one cell

numRows = sheet.row_count  # Get the number of rows in the sheet

pprint(numRows)

pprint(len(data))

Conclusion:

We hope you now have a clear idea of how to achieve google sheet automation as per your needs and make the most out of the tool to save your valuable time. As a test automation services provider, we understand that not everything can be automated, but if you approach any automation task with a negative mindset, you’ll never be able to unravel the solutions to overcome your obstacles. So follow and implement these methods with a positive mindset and you will definitely witness performance improvement at your end.