Bhishan Bhandari: Grab siteprice and write to google spreadsheet using python

By the end of this read you will be able to grab site price from and write it to google spreadsheet using python. Every website has it’s competition. As our website evolves, we have more competitions and the competitors website also earns good value. It is vital to know the value of our website as well as our competition’s value. is one of those websites which calculates a website’s value based on different factors.

Putting domain name of website in a text file with one domain per line will be our strategy for querying number of websites’s price. You may wish to put hundreds of websites in this txt file which are your competitions.

Python codes to extract site price and write in google spreadsheet

from bs4 import BeautifulSoup
from urllib2 import urlopen
import gdata.spreadsheet.service
import datetime
rowdict = {}
rowdict['date'] = str(
spread_sheet_id = '13mX6ALRRtGlfCzyDNCqY-G_AqYV4TpE7rq1ZNNOcD_Q'
worksheet_id = 'od6'
client = gdata.spreadsheet.service.SpreadsheetsService()
client.debug = True = ''
client.password = 'password'
client.source = 'siteprice'
with open('websitesforprice.txt') as f:
    for line in f:
        soup = BeautifulSoup(urlopen("" + line).read())
        rowdict['website'] = str(line)
        rowdict['price'] = soup.find(id="lblSitePrice").string
        client.InsertRow(rowdict,spread_sheet_id, worksheet_id)

1. Line 1 to 4

These lines are import statements. Here in this program, we are using various python libraries. Gdata is used to access google spreadsheet. We are using BeautifulSoup because it allows us to get data via id which we will use to get the price of a website. Datetime is used to get the current date. Urlopen us used to open the webpage which contains the data we want.

2.Line 5 to 14

In order to write the extracted rank to google spreadsheet programmatically we are using the gdata module. In order to write to a spreadsheet we need the spreadsheet id, worksheet id and a dictionary containing values we want to write to the spreadsheet. The dictionary contains key as the column header and value as the string that is to be written to the spreadsheet(website, price, date for our program).

Go to when logged in and create a new spreadsheet. Fill the first three columns of the first row as website, price and date respectively. All the letter should be in lower case and no whitespaces. Now when you have created a new spreadsheet, take a look to the url. The url looks something like this one

The spreadsheet id(mentioned earlier) is present in the url.

13mX6ALRRtGlfCzyDNCqY-G_AqYV4TpE7rq1ZNNOcD_Q ” in the above url is the spreadsheet id we need. By default the worksheet id is ‘ od6 ‘.

Basically line 5 to 14 are codes to access google spreadsheet.

3. Line 15 to 20

Since we’re writing a program that can extract alexa ranks for hundreds of websites and append it to google spreadsheet, therefore taking url from console input is never a good solution. We have to write the url of websites we want to take care of in a text file. Each website in a single line in the format . Make sure there is a valid website, one in each line because we will read the url from python line by line.

Line 17 makes a soup element out of the url which has the information we are looking for. The soup element is of different websites in each iteration. Line 18 stores the value of the domain in the key “website” of json rowdict. Line 19 stores the price of the website in the key price of json rowdict. You can see we use BeutifulSoup to get data via id. Finally line 20 pushes the entire json element to google spreadsheet. This piece of codes runs for the number of times equal to the line in text file.

Thanks for readingEnjoy!! . If you have any questions regarding the post, feel free to comment below.