Site icon EASY2DIGITAL

Chapter 7: Manipulate Data in Google Sheets Using Easy2Digital APIs and Google Sheets Key

In the previous chapter, we talked about how to scrape more than 50 videos from a Youtube search query keyword. And we discussed how to grab the performance of each video, such as view, comment, like, etc. However, it’s not the end of automation power. It’s because we aim to research, filter Youtubers, and automate the collaboration invitation process. At least, you can save and manage the fetched list of Youtubers in a datasheet on a cloud drive instead of in the CSV file. Then you can set up and easily integrate it with other platforms.

So in this Python Tutorial, I will continue to use the Python script from the Python Tutorial Chapter 6. I would walk you through how to create a Robot user account, and leverage Google Sheet API. It is for saving all fetched data in a Google Sheet In your web scraping python script. By the end of this Python Tutorial, you can learn what modules you need to set up. Then you can experience just looking at a spreadsheet that is automatically listing all videos in a preset format.

Table of Contents: Manipulate Data in Google Sheets Using Easy2Digital APIs and Google Sheets Key

Create a Robot Service Account in Google API Console

As well as Youtube API, Google Sheet API is of course part of the Google API family. So first thing first, we need to create a credential, and it is the service account this time.

Basically, this creation purpose is to name the robot account, grant the access level to the robot (Normally it can be edited level), and download a JSON script that will be used in the coming script writing. For one part regarding the service account role, it can be ignored and it’s unnecessary to fill in in this Python Tutorial.

Add the Robot Account to a Google Sheet

After the account is created, we go to google cloud and create a new google sheet. The cloud drive can be the free version, which doesn’t matter. And in the share button, we add the robot account address and set it as the editor.

Install Google Client Library on your Macbook

Again, whatever API you are going to create and adopt in your Python Script, just remember to check the API documentation, which normally includes installation codes required, script samples, etc. Here is the same, we can go to the Google sheet API and check out the Python language

The Mac and Windows operating system are almost the same, so I take Macbook as an example. We go to the terminal and type in this code. The reason I typed in pip3 is that my Macpro’s default python version is 2.7, and I didn’t associate them together. But of course, you can do something different with me.

pip3 install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

After a while, the installation is done.

And try to check the installed packages by typing in the pip3 list, the results show that they’re in place.

Import the Google API module

As well as talking about Beautifulsoup, requests, CSV writing, etc, we need to input the modules that include functions we need to create the script feature. If we try to check out the API documentation, there is a sample for our reference to use, but the sample is not fully fitting our script creation purpose. So based on our purpose, I would select one module and remove the rest, which can allow us to use build (), and create the Google sheet API object.

from __future__ import print_function

import pickle

import os.path

from googleapiclient.discovery import build

from google_auth_oauthlib.flow import InstalledAppFlow

from google.auth.transport.requests import Request

Use OAuth 2.0 for Server to Server Application

The Google OAuth 2.0 system supports server-to-server interactions such as those between a web application and a Google service. We just created a service account, which is an account that belongs to the application instead of to an individual end-user. Your application calls Google APIs on behalf of the service account, so users aren’t directly involved. This scenario is sometimes called “two-legged OAuth,” or “2LO.” In fact, it is a robot account, we would deep dive into more details in the coming chapters, such as Wechat chatbot, etc.

Below are the required module, object, and variables above the lines of code.

from google.oauth2 import service_account

SCOPES = ['https://www.googleapis.com/auth/sqlservice.admin']

SERVICE_ACCOUNT_FILE = '/path/to/service.json'

credentials = service_account.Credentials.from_service_account_file(

        SERVICE_ACCOUNT_FILE, scopes=SCOPES)

But we need to modify a few places. First of all, we need to select the scope of the API call, we can refer to the Google Sheet API scopes. And here we need to select read and write.

Then, we need to update the location path of the service account JSON file which was downloaded when we created it in the Google API console, which tells Python we would use a Robot account and represent to get access and edit the Google sheet data.

Also, I would suggest changing the variable name “credential” to creds, which is easier for us to separate the credential in any other API objects set up by default. 

Last but not least, we need to add creds = None before the creds, which ensures the creds are not defined with any value.

Call the Sheets API

In the API documentation script sample, there are three lines of coding that are used to call the sheets API, which we could copy and paste into our Python script. The only thing we need to update the Google sheet ID.

SAMPLE_SPREADSHEET_ID = 'xxxxxxxxxxxx……...'

service = build('sheets','v4',credentials=creds)

sheet = service.spreadsheets()

As you might remember, we created a Google sheet and authorize the robot account on the editor level, so we go to that spreadsheet, copy the sheet ID from the page URL and paste this code to the SAMPLE_SPREADSHEET_ID value.

Append the Video Data Values to the Google Sheet

So now things are almost going to be ready. What we need to do is to feed the scraped video data to the google sheet.

First of all, as you might remember in the previous chapter about CSV file writing, we create a line of code after the data fetching section, which is for formatting data to appear in columns. Similar to the CSV write method, we also need to create a variable. But the difference here is that it doesn’t have an object and method to follow, so we need to create it by ourselves, like saying we create a variable named in video50_scrape.

video50_scrape = [[channel_title, video_title, yt_link,vid_view,vid_like,vid_dislike,vid_favorite,vid_comment]]

Then, we go to the Google sheet API reference and check what parameter and value we need to pass in if we aim to append the data to the google sheet.

As we can see in the append from spreadsheet values, we must pass in 

So now we can create two variables called updatesheet_request, and updatesheet_response. One is for appending the data, the other one is for executing the update sheet. This way is better for other people to understand the logic.

updatesheet_request = sheet.values().append(spreadsheetId=SAMPLE_SPREADSHEET_ID,range="Youtubers_Ring_Spotlight_Camera_Query!A2",valueInputOption="USER_ENTERED", insertDataOption="INSERT_ROWS", body={"values": video50_scrape})

updatesheet_response = updatesheet_request.execute()

Google Sheet API Usage Limit

Now we can try to command b, and you should be happy to see all the video-fetched data are automatically updated in the Google sheet. And you just need to change the q value, and the video data would continue to update after. If it’s necessary, you can add one more column via Python, which shows you what keyword query you use. This task leaves you guys to finish, I’m sure you already can do it.

Lastly, Google Sheet API has usage limits, which have a limit of 500 requests per 100 seconds, so you might need to control the frequency of the execution, or you can refer to my next articles regarding how to handle API limitations. I’ll discuss more this section in the work automation collection.

Easy2Digital APIs – Data Manipulation in Google Sheets

If you find the script might be complicated and also requires you to update scripts and fix bugs on and off, you can leverage Easy2Digital Youtube Bot API. Here is the token endpoint as follows:

https://www.buyfromlo.com?token=&googleSheetID=&googleSheetJsonFeed=&sheetTab=&range=&toWhere

By using this API endpoint, you just need to the elements as follows:

The scraped result is the same as the one shown above.

For more details regarding Marketing APIs, please check out this page.

Easy2Digital Marketing APIs Documentation

Full Python Script of Data Manipulation in Google Sheets Using Easy2Digital APIs and Google Sheets Key

If you would like to have a free Easy2Digital Token and the full version of the Python Script of Manipulate Data in Google Sheets Using Easy2Digital APIs and Google Sheets Key, please subscribe to our newsletter by adding the message Chapter 7. We would send you the script immediately to your mailbox.

Contact us

So easy, right? I hope you enjoy reading Chapter 7: Manipulate Data in Google Sheets Using Easy2Digital APIs and Google Sheets Key. If you did, please support us by doing one of the things listed below, because it always helps out our channel.

The next chapter is about how to use a script scraping your competitor Shopify website products via API as the article embedded below. Enjoy! ?

Chapter 8: Build a Shopify Scraper to Fetch Competitor Webshop Product Data Using Easy2Digital APIs

FAQ:

Q1: What is the Google Sheets API?

A: The Google Sheets API is a RESTful API that allows developers to read, write, and modify Google Sheets data programmatically.

Q2: What can I do with the Google Sheets API?

A: With the Google Sheets API, you can automate tasks like creating new spreadsheets, updating existing sheets, formatting cells, and retrieving data from sheets.

Q3: How can I access the Google Sheets API?

A: To access the Google Sheets API, you need to enable the API in the Google Cloud Platform Console and obtain an API key or OAuth 2.0 credentials.

Q4: What programming languages are supported by the Google Sheets API?

A: The Google Sheets API provides client libraries for various programming languages including Python, Java, PHP, and .NET.

Q5: Can I use the Google Sheets API for free?

A: The Google Sheets API has a free quota that allows you to make a certain number of requests per day at no cost. However, if you need to make a large number of requests, you may need to upgrade to a paid plan.

Q6: Is the data transferred through the Google Sheets API secure?

A: Yes, the data transferred through the Google Sheets API is encrypted using HTTPS, ensuring secure communication between your application and Google’s servers.

Q7: Can I share my Google Sheets API credentials with others?

A: No, you should never share your Google Sheets API credentials with others. These credentials are meant to be kept confidential and should only be used by your application.

Q8: Are there any limitations to using the Google Sheets API?

A: Yes, there are certain limitations to using the Google Sheets API. For example, there is a limit on the number of requests you can make per day and the size of data you can retrieve or modify.

Q9: Can I integrate the Google Sheets API with other Google APIs?

A: Yes, you can integrate the Google Sheets API with other Google APIs like the Google Drive API to create powerful applications that leverage multiple Google services.

Q10: Where can I find documentation and resources for the Google Sheets API?

A: You can find detailed documentation, code samples, and other resources for the Google Sheets API on the official Google Developers website.

Exit mobile version