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
Hey @drookyn I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x
Good tutorial, excellent information for small and medium solutions.
Thanks :)
In my last company we used Google spreadsheet quite often as a easy backend, even for some static websites for very big clients! 😉 It's really a good way to get a cheap and somehow safe backend quiet fast 😊
Really? That sounds crazy! For something small I could imagine using it but not for something big 😅
The projects weren't that big, just the client was a really big one ;)
Thank you for the contribution. It has been approved.
In the future don't add irrelevant code to the end of your tutorials.
You can contact us on Discord.
[utopian-moderator]