Accessing Google Spreadsheet Data using Python

If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’. Well, Not so fast.

If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’. Well, Not so fast.

As you all are familiar with importing, exporting and manipulating comma separate files (CSV) using Python, Hereby in this article I’m going to show you the step by step guide to access Google Spreadsheets on the cloud using Python.

As the very first thing, go to Google API Manager by simply googling it and go to https://console.developers.google.com/

https://console.developers.google.com/

To kick things off first create a new project.

Figure 1.0: Creating a new Project

I’ll name my project as ‘Telemedicine’ since we will be working with a spreadsheet which includes all the tweets related to Telemedicine hashtags which I extracted earlier (Click here to see how I extracted tweets using hashtags). Define a suitable project name according to your dataset on the cloud then click CREATE to initiate the project. (You don’t have to worry about the Location* below the project name)

Figure 1.0: Define a suitable project name according to your dataset

OKAY. The first part is done, Now go to API Library and search for Google Drive.

Figure 2.0: Search for Google Drive in API Library

Then add Google Drive API to our project which will allow us to access spreadsheet inside of Google Sheets for our account.

Figure 2.1: Click on Enable

Once that’s added, we need to create some credentials to access the API so click on Add Credentials on the next screen you see after enabling the API.

Figure 3.0: Click on Create Credentials to initialize credentials to access the API

Since we’ll be accessing the API using a web server, We’ll add the Web Serveroption on this page and give access to Application Data and tell them that you’re not running your application on either GCE or GAE by selecting the option ‘No, I’m not using them’ then click on the button below.

Figure 3.1: Creating credentials

Next, we will create a service account named Employees and assigned it the role Project Editor which will allow it to access and edit all the data within the API. Clicking continue will generate a JSON file that I will rename and add it to the project as Telemedicine_secret.json.

Figure 3.2: Creating service account credentials

Then open the JSON file in a text editor (I prefer Atom) :)

Inside the file, you can locate an email address property called “client_email”, if we copy that and take it over to our spreadsheet on the cloud, we can share that particular spreadsheet with the email address we provide to give us access to it from the API.

Figure 4.0: Copy the email address property client_email

Figure 4.1: Paste that copied email as a shared email address

Reading spreadsheet data with Python

Let’s move into the terminal to install gspread and oauth2client packagesand wait till all components get installed.

$ pip install gspread oauth2client

Then I’m going to create a new python file called spreadsheet.py in my favorite editor ATOM and write the following code to import gspread and ServiceAccountCredentials from oauth2client .

import gspread
from oauth2client.service_account import ServiceAccountCredentials
  1. Then, we have to define the scope and create credentials using that scope and the content of employees_secret.json file.
  2. Then I’ll create a gspread client authorizing it using those credentials.
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('employees_secret.json', scope)
client = gspread.authorize(creds)


3. NOW, We can access our google sheets so we’ll call a client.open and pass it the spreadsheet name and getting access to sheet1

sheet = client.open('telemedicine_data').sheet1

4. Now we can set our employees equal to all of the records inside that sheet and print them out to the terminal.

telemedicine = sheet.get_all_records()
print(telemedicine)

Let’s go the terminal try to run our program and we’ll get a glorious list of perfectly formatted content like in the image below (See the highlighted text and you can find all the columns in our dataset).

python spreadsheet.py

Figure 5.0: Display results as a list

Whoa! It wasn’t what you’re expecting, wasn’t it? Well, trust me I got the perfect solution for that!

We can clean up the result by using pprint module, using that we can create a prettyprinter that we can use to display the result and its a much nicer way to display the output.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('employees_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open('employee_reviews').sheet1
pp = pprint.PrettyPrinter()
employees = sheet.get_all_records()
pp.pprint(employees)



Figure 5.1: Formatted results using pprint

If you want to go through the full documentation of gspread click here and in the meantime I’ll follow you up with these cool tricks.

Filtering Data

#to get all the values inside the file
sheet.get_all_values()
#to get exact row values in a second row (Since 1st row is the header)
sheet.row_values(2)
#to get all the column values in the column 'place'
sheet.col_values(16)
#to extract a particular cell value
sheet.cell(1, 1).value



Figure 6.0: sheet.col_values(16) to get all the row values in column 16 which is ‘place’


Figure 6.1: sheet.row_values(2) to get all the first row values


OK! Reading is all done. YES! But, Can we do more? Oh Yes!



Insert / Update and Delete from Spreadsheet

And also, you can enter a random row and can give values to each cell by separating each word using double quotations.

row = ["I'm","inserting","a","new","row","into","a,","Spreadsheet","using","Python"]
index = 3
sheet.insert_row(row, index)

Figure 6.2: Spreadsheet after inserting our new row

Furthermore, It’s also possible to alter data inside a specific cell. Look at how it is done, I’ll change the header column name ‘id’ to ‘telemedicine_id’:

sheet.update_cell(1, 1, "telemedicine_id")

Figure 6.3: After updating the header name which is cell(1,1)


Finally, In conclusion to this article, I’m going to wrap up proving that it is not essential to create databases to simple internal apps. Spreadsheets might be your best answer!

You can access the full code here.

Photo by Paul Gilmore on Unsplash


Thank you!

If you liked this post, share it with all of your programming buddies!

Follow me on Facebook | Twitter


Learn More

Complete Python Bootcamp: Go from zero to hero in Python 3

Machine Learning A-Z™: Hands-On Python & R In Data Science

Python and Django Full Stack Web Developer Bootcamp

Complete Python Masterclass

The Python Bible™ | Everything You Need to Program in Python

Python Tutorial for Beginners (2019) - Learn Python for Machine Learning and Web Development

MongoDB with Python Crash Course - Tutorial for Beginners

Learning Python: From Zero to Hero

A Complete Machine Learning Project Walk-Through in Python

MySQL Databases With Python Tutorial

An A-Z of useful Python tricks

To VBA and beyond - building a RESTful backend using plain Microsoft Excel macros

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

This article consists of three main parts, an introduction to webserver internals, getting Excel to answer http requests and adding some special sauce to make it a RESTful backend. If you can't wait to read through the source, you can check out the repository webxcel, which I will refer to throughout this article.

Webservers

When using express or ASP.NET, we usually think about http routes and request bodies, but we never really care about how requests are handled inside the framework. Deep down, every http request consists of a TCP connection, where the client and server exchange various messages and eventually close the connection. A very basic request might look like this:

POST /api/cities HTTP/1.1
Host: localhost
User-Agent: Mozilla/5.0 (Windows; U; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727)
Content-Type: application/json
Content-Length: 29

{
"name": "Springfield"
}

This post request sends a JSON payload to /api/cities, and we'd usually expect the server to create a city named "Springfield". If our backend is a simple express server, it might look something like this:

const express = require("express"),
app = express();

function handler(req, res) {
// create city

res.status(201)
.send("woo hoo!"); //
}

app.post("/api/cities", handler);
app.listen(8080);

Before executing handler, express parses the request, extracts the request method and requested resource to determine what action to perform. It also parses the request headers and - depending on the Content-Type - the request body, too. This looks somewhat similar to the following piece of pseudo-js:

// ...

const requestText = socket.readToEnd(),
request = parseRequest(requestText),
contentType = request.headers.get("Content-Type");

if (canParseRequestBody(contentType)) {
request.body = parseRequestBody(body, contentType);
}

const handler = registeredHandlers.findHandler(request.method, request.url),
response = new HttpResponse();;

if (handler) {
handler(request, response);
}

sendResponse(response);

// ...

The express example above also showed adding arbitary text to the response using res.send() and setting the response's status using res.status(). As you can see in the pseudo-js, after calling the request handling function, the underlaying framework will convert the response object to an HTTP response similar to the following and send it back to the client:

HTTP/1.1 201 Created
X-Powered-By: Express
Content-Length: 8

woo hoo!

In our client, we'd then evaluate the status code, headers and response text after parsing everything again.

Hello, this is Excel

Now, how do we make Excel answer our requests? Microsoft Office comes with a really cool toolbelt called macros, which people now hate because of ransomware. You could argue that macros are obsolete by now and coding them in Visual Basic isn't cool when you could use any modern programming language instead, but the concept behind them is pretty neat in fact.

VBA macros

Macros were originally conceived to save the user from repeating the same task over and over again. For this, users could record macros, which would then repeat what they did earlier. Internally the macro host would create a "script" of what's happening and interpret it later. These scripts happen to be generated in Visual Basic, or - to be more specific - Visual Basic for Applications (VBA). To interact with the application, macro hosts "inject" functionality into the VBA interpreter, like the Range function in Excel, which can be used to access a collection of cells. As VBA is extremely easy to learn if you don't have a programming background, users quickly adopted and combinded injected functions and classes to Subs and Functions to e.g. automatically generate cell values based on more complex calculations.

Crafting Excel files for version control

A major problem when developing macros is version control. Office files are usually zip containers, so adding them in binary would prevent any sane way of diffing. Extracting and re-zipping the project would be the way to go, if macros weren't binary encoded in a separate container using a special format called OLE. There are some OLE macro extractors like decalage2's oletools out there, but strangely I didn't find any library to create these containers the easy way.

Instead of reading the specification and creating our own library, we may try something else first: we can control Excel (or any other Office application) using .NET and let Excel do all the hard work for us. Using this approach, we need our macros' code in plain-text files, and an importer, which starts Excel and imports our macros. We'll be using PowerShell for this, which comes with full access to the .NET framework and because we don't have to compile these scripts.

In PowerShell, we can create an Excel instance, which we can then use to create workbooks and import our macros. You can take a look at the build script build.ps1 in the repository.

Escaping interop hell - using Windows Sockets in VBA

After we got our version control problems out of our way, we can get straight to the core of creating our server. As mentioned earlier, building a webserver requires handling TCP connections. The bad news is, VBA doesn't come with a TCP implementation by default and I can't really think of a reason why it should. But don't worry, Microsoft thought of somebody needing questionable features, so they baked C interop into VBA as well.

Everybody who's done C interop from a high-level language like C# knows the pain of AccessViolationException when incorrectly marshalling parameters. In VBA it's basically the same, except that both the debugger and the IDE aren't really meant to develop interop-heavy applications, and thus debugging isn't as easy as you might be used to.

The "easiest" way of getting a TCP server running using only interop and no external libraries (like a C# library which implements the HTTP server already - that'd be too easy for us), is to use Windows Sockets (winsocks). If you haven't used winsocks yet, this is what it basically looks like in C++:

// we can skip all variable declarations as they're not that important here

// setup winsocks
WSAStartup(mode, &wsa);

// create a server socket, this is similar to bsd sockets
server = socket(AF_INET, SOCK_STREAM, 0);

// bind the server socket to an address and port, which it'll listen to later
addr.sin_port = htons(8080);
result = bind(server, &addr, sizeof(sockaddr_in));
// usually we'd check the result and handle errors, but that's not important here

// start listening on the server socket and allow queuing up backlog clients
result = listen(server, backlog);
// check result, see above

// get the first client socket in the backlog queue
client = accept(server, &clientAddr, sizeof(sockaddr));

// at this point, the connection is active and we can send/receive data
send(client, message, messageLength, flags);

// cleanup after we're done
closesocket(client);
closesocket(server);
WSACleanup();

The first good news is: we can translate this straight to VBA by importing all required methods in a module and simply call all of them in the right order to get a TCP server up and running.

Since we have a working TCP connection by now, we can continue our server development by parsing incoming HTTP requests. As shown above, requests consist of a protocol line, the headers and a request body. Parsing the protocol line is probably the easiest, we can split it into three parts: the request method, the resource and http version.

Before actually splitting the line, we should make sure we're dealing with an http request. To do so, we can use VBA's text comparison feature Like, which checks if some text matches a very simple pattern, similar to regular expressions. By evaluating

' prevent comparison errors if clients send lower case requests
Dim upperLine As String
upperLine = UCase(line)

' this is somewhat similar to
' /.* HTTP/1.1/.test(upperLine)
' in js
If Not upperLine Like "* HTTP/1.1" Then
' we're concentrating on http 1, since version 2 is a bit more complex to implement
Err.Raise StatusCode.ErrorHttpRequestInvalidFormat
End If

' now we know the request is an http request and can continue parsing it

we can make sure to only process http requests and extract the request method and resource. Splitting the headers is a piece of cake as well, we just need to use Split(line, ":", 2) on each header line, where 2 represents the maximum count of parts the split function should return, and we're set. To keep it simple, we're not going to parse the request body for now. Since we want our server to return a very simple response, we're just going to echo the request.

Similar to express, we'll handle requests using response objects. Our response class contains headers, a status and a body. Using this class, we can create a simple echo server by reading all incoming text, parsing the request and sending our response:

Dim server As TcpServer
Set server = New TcpServer

' listen for incoming connections on port 8080
server.BindTo 8080

' accept an incoming connection ...
Dim client As TcpClient
Set client = server.AcceptTcpClient()

' ... and receive the request text
Dim requestText As String
requestText = client.ReceiveString()

Dim request As HttpRequest
Set request = New HttpRequest

request.Parse requestText

Dim response As HttpResponse
Set response = New HttpResponse

' send "200 OK" and the body
response.StatusCode = 200
response.Body = "Called " & request.Url & " with this body:" & vbCrLf & vbCrLf & request.Body

Dim responseText As String
responseText = response.ToString()

' actually send the response back to the client
client.SendString responseText

' and do some cleanup
client.Dispose
server.Dispose

Webservers like nginx and apache can be configured to send the server version, so we're going to do the same with Excel. In our response class, we're using a ToString method to convert our object to a string containing all response information. When examining our server with the axios node.js module, we'll receive a very satisfying response:

> const axios = require("axios");
> axios.post("http://localhost:8080", "it works").then(response => {
... console.log(response.status, response.statusText);
... console.log(response.headers);
... console.log(response.data);
... });

// outputs
200 'Nobody Needs This Anyway'

{ 'content-length': '39',
connection: 'close',
server: 'Microsoft Excel/16.0' }

Called /hello with this body:

it works

As you can see, we're also adding a header Connection: close to our response, but most servers usually send Connection: keep-alive. This is due to the http specification, which allows reusing the current socket for future requests, and webservers use this to gain some extra performance. Since our webserver isn't going to be as fast as any other server anyway, we might as well skip this and close the sockets, which is easier than keeping connections open, too.

It's blocking the gui, how do I stop it now?

We got an echo server working, great! But it only works for one request and we'll have to restart the macro everytime a client requests something, so let's put it in a loop and we're good to go.

Well, not exactly. If we execute a while (true) { } style loop in a macro, we'll see a lot of white and a "Microsoft Excel (not responding)" kind of titlebar. This is due to how Excel handles macro execution. As you might guess, macros are executed on the main thread, so whatever we'll do, our server will prevent us from accessing Excel or even stopping the macro.

In our macro, however, we can do as much as we want, e.g. implement a kill-switch. Our kill-switch will be a file, which we'll create when the server starts and which the server will monitor. If the file gets deleted, the server stops, easy as that.

But we're not done here just yet. Calling accept to get a client socket also blocks the macro execution until a client connects to our server. Searching for "winsocks accept timeout" takes us onto another C++ adventure: porting the FD_SET and FD_ZERO macros to VBA to use the select method, which in turn gives us the count of available client sockets.

After we successfully ported these C++ macros to VBA, we can pass a timeval object to select and check if there's a client before blocking with accept. Adding this to our server, we're finally able to do as many requests as we wish, plus we can stop the server using our kill-switch. Awesome!

Update 2017-10-09: As Michiel van der Blonk pointed out, calling DoEventsfrom VBA will pause the macro execution until Excel finished processing its event queue. Adding this to our server loop allows us to access Excel while the server is running.

Creating a modular server architecture

If we want to get on the same level as express or any real-world webserver, we must to be able to configure http routes. VBA doesn't contain any form of inline functions, but it does contain basic inheritance. We can use this feature to create an abstract IWebController base class, which we then subclass for our specific controllers.

Besides actually handling requests, each controller should also contain a method like MatchesUrl, which the server can use to find the appropriate controller for a request. Encapsulating this in a WebControllerCollection and adding such a collection to our server, we're now able to add any business logic to our server (like a FileSystemWebController to serve static files).

Getting some REST

The title of this article promised a RESTful backend in Excel, and to this point we only got a basic http server. Since Excel is basically a set of tables, we might use this to our advance and read/modify the table's data using an IWebController subclass.

CREATE TABLE

When we create a table in any real-world relational database, we're using something like this:

CREATE TABLE cities (
id INT PRIMARY KEY,
name VARCHAR(200),
fk_states VARCHAR(200)
);

In Excel, we can use worksheets as tables and the current workbook as our database. But how do we create columns? We can't change the column headings from "A", "B", "C", ... to anything else, so our best bet is to use the first line for our columns. Defining the primary key in a column needs to be easy as well. The easiest way to show that something is important, is to make it red or bold. Excel supports many different red tones, so marking our primary keys bold is probably the best idea:

Reading all entities from a table is easy as well, we just need to iterate over all rows until the primary key column is empty. Inserting works the same way, except we're looking for the first empty primary key cell to insert our record.

For any create or update REST action we'll need to parse the request body. Since most frontend frameworks use JSON exclusively, we'll need a new JsonParser, which emits JsonObjects and JsonArrays. To keep it simple, we're using a hand-written recursive-descent top-down parser, which counts braces/brackets and then recursively calls the appropriate parse method.

Now that we have tables with primary keys and a JSON parser, we can go ahead and create REST endpoints in a WorkbookWebController. To not iterate over all our tables on every request, we can add a route prefix like /workbook (e.g. /workbook/cities). In the ProcessRequest method of our controller, we can then analyze which sheet was requested and which REST method we should perform. This yields a basic REST backend, which can return all entries in a table, and return, update or delete a single entity.

Let's call it WRM

Doing basic REST stuff is not good enough though: real web frameworks like ASP.NET map relationships of entities. If we'd have above schema in a database used by an ASP.NET app, the underlying persistence framework would resolve all foreign keys (e.g. fk_states) and map these to their actual entities. We can create something similar in Excel, using not an object relationship mapper, but rather a worksheet relationship mapper (WRM).

In our WRM, we can read all table entries, but before returning the data to the client or inserting it to our tables, we're iterating over all columns and try to resolve each column starting with "fk_". Everytime we find such a column, we'll get the matching entity of the foreign table and use it instead of the raw value. Once everything is resolved, we might get something like this when accessing /workbook/cities from the above schema:

[
{
"id": "1",
"city": "Seattle",
"states": {
"short_name": "WA",
"full_name": "Washington"
}
},
{
"id": "2",
"city": "Springfield",
"states": null
}
]
Putting it all together

We can now combine the contents of this article and build highly complex database schemas in Excel, which we can access using REST methods. As quickly noted before, our Excel server also supports serving static files, so it makes it an ideal platform to prototype our future web applications - at least if you're prototyping on Windows (maybe macOS support will come one day).

To showcase webxcel's ease-of-use, the repository contains a React todo app with an Excel backend in the example folder.


By :  Michael Neu


Python GUI Programming Projects using Tkinter and Python 3

Python GUI Programming Projects using Tkinter and Python 3

Python GUI Programming Projects using Tkinter and Python 3

Description
Learn Hands-On Python Programming By Creating Projects, GUIs and Graphics

Python is a dynamic modern object -oriented programming language
It is easy to learn and can be used to do a lot of things both big and small
Python is what is referred to as a high level language
Python is used in the industry for things like embedded software, web development, desktop applications, and even mobile apps!
SQL-Lite allows your applications to become even more powerful by storing, retrieving, and filtering through large data sets easily
If you want to learn to code, Python GUIs are the best way to start!

I designed this programming course to be easily understood by absolute beginners and young people. We start with basic Python programming concepts. Reinforce the same by developing Project and GUIs.

Why Python?

The Python coding language integrates well with other platforms – and runs on virtually all modern devices. If you’re new to coding, you can easily learn the basics in this fast and powerful coding environment. If you have experience with other computer languages, you’ll find Python simple and straightforward. This OSI-approved open-source language allows free use and distribution – even commercial distribution.

When and how do I start a career as a Python programmer?

In an independent third party survey, it has been revealed that the Python programming language is currently the most popular language for data scientists worldwide. This claim is substantiated by the Institute of Electrical and Electronic Engineers, which tracks programming languages by popularity. According to them, Python is the second most popular programming language this year for development on the web after Java.

Python Job Profiles
Software Engineer
Research Analyst
Data Analyst
Data Scientist
Software Developer
Python Salary

The median total pay for Python jobs in California, United States is $74,410, for a professional with one year of experience
Below are graphs depicting average Python salary by city
The first chart depicts average salary for a Python professional with one year of experience and the second chart depicts the average salaries by years of experience
Who Uses Python?

This course gives you a solid set of skills in one of today’s top programming languages. Today’s biggest companies (and smartest startups) use Python, including Google, Facebook, Instagram, Amazon, IBM, and NASA. Python is increasingly being used for scientific computations and data analysis
Take this course today and learn the skills you need to rub shoulders with today’s tech industry giants. Have fun, create and control intriguing and interactive Python GUIs, and enjoy a bright future! Best of Luck
Who is the target audience?

Anyone who wants to learn to code
For Complete Programming Beginners
For People New to Python
This course was designed for students with little to no programming experience
People interested in building Projects
Anyone looking to start with Python GUI development
Basic knowledge
Access to a computer
Download Python (FREE)
Should have an interest in programming
Interest in learning Python programming
Install Python 3.6 on your computer
What will you learn
Build Python Graphical User Interfaces(GUI) with Tkinter
Be able to use the in-built Python modules for their own projects
Use programming fundamentals to build a calculator
Use advanced Python concepts to code
Build Your GUI in Python programming
Use programming fundamentals to build a Project
Signup Login & Registration Programs
Quizzes
Assignments
Job Interview Preparation Questions
& Much More

Guide to Python Programming Language

Guide to Python Programming Language

Guide to Python Programming Language

Description
The course will lead you from beginning level to advance in Python Programming Language. You do not need any prior knowledge on Python or any programming language or even programming to join the course and become an expert on the topic.

The course is begin continuously developing by adding lectures regularly.

Please see the Promo and free sample video to get to know more.

Hope you will enjoy it.

Basic knowledge
An Enthusiast Mind
A Computer
Basic Knowledge To Use Computer
Internet Connection
What will you learn
Will Be Expert On Python Programming Language
Build Application On Python Programming Language