Using Python 🐍 with Google Sheets as a database

<p dir="auto"><strong>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. <hr /> <h2>Requirements <p dir="auto">For this tutorial you need <ul> <li><a href="https://www.python.org/downloads/release/python-363/" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Python 3 <li><a href="https://console.developers.google.com/apis" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Google API credentials & active Drive API <li><a href="https://docs.google.com/spreadsheets/" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">A spreadsheet <h2>Difficulty <p dir="auto">Let's say it's <code>Intermediate. <h2>Programming <h3>Installation <p dir="auto">First of all we have to install some dependencies. <pre><code>$ pip install gspread oauth2client <p dir="auto">After this quick installation we have to create a new file. Let's call it <code>grocery-list.py. Insert a quick Python barebone into it and give it a try with <code>python grocery-list.py. <pre><code>#!/usr/bin/python3 def main(): print('Hello world!') if __name__ == '__main__': main() <h3>Create a client <p dir="auto">First be sure that you've shared your spreadsheet with the <code>client_email you find in your Google API credential JSON.<br /> Rename this JSON into <code>client-secret.json and modify your script. <pre><code>#!/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() <p dir="auto">At this point we have successfully created a client for the Google Drive API. Furthermore we have ensured that our <code>client-secret.json does exist, before we try to use it. <h3>Fetch all rows <p dir="auto">It's time to get some data and pretty print it into the console. <pre><code>#!/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 # ... <p dir="auto">If we did it all right it should look something like this:<br /> <br /><br /> <img src="https://images.hive.blog/768x0/https://stuff.flovolution.com/blog/grocery-list-1.PNG" alt="grocery-list-1" srcset="https://images.hive.blog/768x0/https://stuff.flovolution.com/blog/grocery-list-1.PNG 1x, https://images.hive.blog/1536x0/https://stuff.flovolution.com/blog/grocery-list-1.PNG 2x" /> <h3>Append a row <p dir="auto">Now we want to write a new row as last element into the spreadsheet. <pre><code>#!/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) # ... <p dir="auto">Execute the script with <code>python grocery-list.py and open the spreadsheet in your browser. It should look like this:<br /> <br /><br /> <img src="https://images.hive.blog/768x0/https://stuff.flovolution.com/blog/grocery-list-2.PNG" alt="grocery-list-2" srcset="https://images.hive.blog/768x0/https://stuff.flovolution.com/blog/grocery-list-2.PNG 1x, https://images.hive.blog/1536x0/https://stuff.flovolution.com/blog/grocery-list-2.PNG 2x" /><br /> <br /><br /> <br /><br /> À voilà! A simple database like structure using Python and Google Sheets. <hr /> <p dir="auto">With that in mind, happy coding. <pre><code>(() => { 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)); })(); <p dir="auto"><br /><hr /><em>Posted on <a href="https://utopian.io/utopian-io/@drookyn/using-python-with-google-sheets-as-a-database" target="_blank" rel="noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Utopian.io - Rewarding Open Source Contributors<hr /><p>