Imagine you want to build an internal progamm with Python and need a small database. Why not use Google Sheets? In this tutorial I want to show you how to use Google Sheets in Python 3.
Requirements
For this tutorial you need
Difficulty
Let's say it's Intermediate.
Programming
Installation
First of all we have to install some dependencies.
$ pip install gspread oauth2client
After this quick installation we have to create a new file. Let's call it grocery-list.py. Insert a quick Python barebone into it and give it a try with python grocery-list.py.
#!/usr/bin/python3
def main():
print('Hello world!')
if __name__ == '__main__':
main()
Create a client
First be sure that you've shared your spreadsheet with the client_email you find in your Google API credential JSON.
Rename this JSON into client-secret.json and modify your script.
#!/usr/bin/python3
import gspread
import os.path
import sys
from oauth2client.service_account import ServiceAccountCredentials
client_secret = 'client-secret.json'
def main():
if not os.path.isfile(client_secret):
print('Client secret not found. Create one here: https://console.developers.google.com/apis')
sys.exit(1)
client = get_client()
def get_client():
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name(client_secret, scope)
client = gspread.authorize(creds)
return client
if __name__ == '__main__':
main()
At this point we have successfully created a client for the Google Drive API. Furthermore we have ensured that our client-secret.json does exist, before we try to use it.
Fetch all rows
It's time to get some data and pretty print it into the console.
#!/usr/bin/python3
# ...
import pprint
def main():
# ...
client = get_client()
sheet = client.open('grocery_list').sheet1
results = get_all(sheet)
pp = pprint.PrettyPrinter()
pp.pprint(results)
def get_all(sheet):
results = sheet.get_all_records()
return results
# ...
If we did it all right it should look something like this:
Append a row
Now we want to write a new row as last element into the spreadsheet.
#!/usr/bin/python3
# ...
def main():
# ...
last_id = results[-1].get('id')
insert_new(sheet, [(int(last_id) + 1), 1, 'food', 'Mango'], len(results) + 2)
def insert_new(sheet, row, index):
sheet.insert_row(row, index)
# ...
Execute the script with python grocery-list.py and open the spreadsheet in your browser. It should look like this:
À voilà! A simple database like structure using Python and Google Sheets.
With that in mind, happy coding.
(() => {
const colors = [
'001f3f', '0074d9', '7fdbff', '39cccc',
'3d9970', '2ecc40', '01ff70', 'ffdc00',
'ff851b', 'ff4136', '85144b', 'f012be',
];
const contents = ['%cI', '%c❤', '%cweb', '%cdev'];
const options = Array.from(
new Array(contents.length),
() => `
color:#${colors[Math.floor(Math.random() * colors.length)]};
font-size:64px;
`
);
console.log.apply(console, [contents.join('')].concat(options));
})();
Posted on Utopian.io - Rewarding Open Source Contributors