In one of my recent django projects, I had to read some data from database and write it to a google spreadsheet. This needs to be done on regular intervals using a cron job.
Gspread
Gspread saved me from struggling with google docs API. It is much easier to read and write in google sheets from you code, if you are using Gspread.
You can install Gspread using the command
pip install gspread
Or if you want to install the latest code, you can install it from the github repo using the command
pip install https://github.com/burnash/gspread/archive/master.zip
This is going to be a much longer blog. Be patient to read it and try it.
1 - Authentication
You can authenticate using your google username and password or using OAuth2.
Method 1.1: Using username and password
If you going to use your google username and password, then it is a fairly straightforward method. You can use the following code to do that
import gspread
gc = gspread.login('youremail@gmail.com', 'yourpassword')
If you are using google's 2-factor authentication, you can generate an app-specific password and use it as password for authentication. Eventhough this is a very simple method, you have to expose your account password in code which is not recommended.
Method 1.2: Using google oauth token
You can also login using Google oauth token which will not reveal sensitive information such as your password. But this will involve some work. Here the steps are explained clearly.
Sign up at google developer console and create a project.
You can give a name of your choice and project ID will provided by google.
Go to Enable an API section to enable APIs of your choice
In our case, we should enable Drive API and it provides 10,000,000 requests per day for free.
Now go to Credentials under APIs & auth. Click Create new Client ID.
Then select your email and give an product name to proceed. Rest of the fields are optional.
In the next screen, select your application type and provide authorized URLs. In our case, application type can be Web application and authorized and redirect uri can be http://localhost:8080/.
In the next screen, you will be able to see your Client ID and Client Secret.
Then click Create Client ID and select Service account option and click create.
This will prompt you to download a file with file extension *.p12. Download and save this file which will be required for further authentication.
After you download the file, a secret key will be shown to you. Please copy and keep it safe. This will not be shown again.
We are done with google API site. The follwing steps needs to be followed in your system. Now install dependent python libraries using the following command
pip install python-gflags oauth2client
Now copy the following code as a python script or you can download the file get_oauth2_token.py here.
from oauth2client.client import OAuth2WebServerFlow
from oauth2client.tools import run
from oauth2client.file import Storage
CLIENT_ID = '<Client ID from Google API Console>'
CLIENT_SECRET = '<Client secret from Google API Console>'
flow = OAuth2WebServerFlow(
client_id = CLIENT_ID,
client_secret = CLIENT_SECRET,
scope = 'https://spreadsheets.google.com/feeds https://docs.google.com/feeds',
redirect_uri = 'http://example.com/auth_return'
)
storage = Storage('creds.data')
credentials = run(flow, storage)
print "access_token: %s" % credentials.access_token
In the above script, replace CLIENT_ID and CLIENT_SECRET from the details from your Google developer console account.
After replacing the details, run the python script using the command
python get_oauth2_token.py
Running this command will open your default browser and if you are not logged into google account already, you will be prompted to login. Then you need to grant access to your google account in the next screen.
After you accept access permission you will see the message The authentication flow has completed. in your browser. A file named creds.data will be stored in the same location as your python script. Open the file and find refresh_token in that. The content of the file will be in python dictionary format. Make sure that you copy the refresh token during first time itself. If not you have to repeat the whole process again.
Now we need to write a function for google authentication using the credentials we obtained upto this point.
import requests, gspread
from oauth2client.client import SignedJwtAssertionCredentials
def authenticate_google_docs():
f = file(os.path.join('your-key-file.p12'), 'rb')
SIGNED_KEY = f.read()
f.close()
scope = ['https://spreadsheets.google.com/feeds', 'https://docs.google.com/feeds']
credentials = SignedJwtAssertionCredentials('username@gmail.com', SIGNED_KEY, scope)
data = {
'refresh_token' : '<refresh-token-copied>',
'client_id' : '<client-id-copied>',
'client_secret' : '<client-secret-copied>',
'grant_type' : 'refresh_token',
}
r = requests.post('https://accounts.google.com/o/oauth2/token', data = data)
credentials.access_token = ast.literal_eval(r.text)['access_token']
gc = gspread.authorize(credentials)
return gc
You can get google authectication credentials in a variable like this
gc = authenticate_google_docs()
Now we are at the same point both in method 1.1 and method 1.2. Either you use method 1.1 or method 1.2 the google authentication is stored in the variable gc.
2 - Open Google Spreadsheet
You can open google spreadsheet by supplying spreadsheet Title or URL or Key as given below.
sh = gc.open("My Expenses") # Open by name
sh = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE') # Open by key which can be extracted from spreadsheet's url
sh = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl') # Open by URL
3 - Select Worksheet
You can get a list of all available worsheets or open a worksheet by Name or Index or Sheet order.
worksheet = sh.get_worksheet(0) # Select worksheet by index
worksheet = sh.worksheet("January") # By title
worksheet = sh.sheet1 # By sheet order
worksheet_list = sh.worksheets() # Get list of worksheets
4 - Create Worksheet
You can create a new worksheet in the selected spreadsheet using the following command.
worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")
5 - Delete Worksheet
You can delete a worksheet using the below given command and passing worksheet retrieved in "Select Worksheet".
sh.del_worksheet(worksheet)
6 - Get Cell Value
You can get call value either using cell label or using cell coordinates with the commands given below
val = worksheet.acell('B1').value # With label
val = worksheet.cell(1, 2).value # With coords
7 - Get all values from row or column
If you want to values from an entire row or entire column you can use the following commands
values_list = worksheet.row_values(1)
values_list = worksheet.col_values(1)
8 - Get all values from worksheet as list of lists
You can get all the values from the selected worksheet as list of lists using the following command
list_of_lists = worksheet.get_all_values()
9 - Find cell with given value
You can search for a value in the selected worksheet.
cell = worksheet.find("Mail") # Find a cell with exact string
mail_re = re.compile(r'(Google|Yahoo) Mail') # Use regex to search
cell = worksheet.find(mail_re)
10 - Find all matched cells
You can get all matched cells with given value.
cell_list = worksheet.findall("Mail")
mail_re = re.compile(r'(Google|Yahoo) Mail')
cell_list = worksheet.findall(mail_re)
11 - Get cell properties
You can get properties of the selected cell. Will help you use search function.
value = cell.value # Get cell values
row_number = cell.row # Get row number
column_number = cell.col # Get column number
12 - Update cell
You can update cell(s) using the following commands
worksheet.update_acell('B1', 'Gspread!') # Update using cell label
worksheet.update_cell(1, 2, 'Gspread!') # Update using cell coordinates
# Update cell range
cell_list = worksheet.range('A1:C7')
for cell in cell_list:
cell.value = 'O_o'
worksheet.update_cells(cell_list) # Update in batch
Now we have learnt about using Gspread to access Google spreadsheets in Python. Feel free to post your comments.