Python and Google Sheets Integration (Zapier free alternative)

Hey guys, everthing great?
Hope you’re all developing great apps with glide!

First of all, I’d like to share my quick story with you:

My developer story

I’m a back-end developer and have absolutely no experience with front-end programming.

For those who doesn’t know, the back-end development is the “logical” programming of an application and the front-end development is the “visual” development of an application.

That being said, I had a huge problem with my startup MVP, because it needed to be a complete application, and all I could develop wasn’t ready for customers to use, because it was basically an API. In other words, a bunch of code.

Glide helped me with that!
And now I’m capable of programming my own back-end logic and using glide as a front-end.
I’m writing this post to people that want to be capable of doing more with Glide, and unleash it’s power.

Today, I’ll teach you how to connect Google Sheets to Python to enhance Glide capabilities even more. For the tutorial, I’m going to code a little e-mail sender example that I think would be useful to many Glide users.

But Gabriel, why it would be useful to me to connect these two if Glide is meant to be a low-code solution for building apps? Isn’t this contradictory?

Well, I don’t think so. If you read my story it will become clear to you that Glide has much more to offer than being just a low-code solution. And in some cases, Glide actually helps people to understand logical programming better. If you need more power from Glide and feels restricted by Google Sheets, this is perfect for you!

Use-cases:

There are so many uses for that integration that I could stand here typing all day long, but I’ll show some examples:

  • Zapier free alternative, as Python has a lot of packages to almost any appliance.
    In my app, I use it to send e-mails and integrate with Twilio, to send WhatsApp messages.
    It can even provide free notifications, if used with telegram’s free bot API!
  • Use another many different sources of data, such as databases, websites, and so on.
  • Manage members with subscription, integrating it with payment gateways.
  • Deploy scheduled scripts to delete unused rows, register and unregister users, and perform ANY sheet manipulation you need.

Requirements:

Let’s go!

1) Install Python Core
Download it in the link above and install it if you don’t have it already.

How to install Python Core

1.1) Click on the yellow download button:

1.2) Go through installation process by clicking “Install Now”, and be sure to check the "Add Python 3.8 to PATH"
image

2) Install gspread Package

How to install gspread package

After installing Python, open up a Command Prompt by pressing WIN+R and typing “cmd”, then enter.
(If you’re on another platform, open your terminal in your way.)

On the terminal, type “pip install gspread”, and then press enter.
image

If the system doesn’t recognize your command, it’s probably because Python was not added to Path (System environment variables), so go back to step 1 and install Python correctly.

You’ll see a bunch of commands running until it installs completely.

3) Enable Google Developers API and Create a Service Account
This step is going to grant your Python script access to your sheet.

How to enable Google Developers API and Create a Service Account

3.1) Go to Google Developers Console and log-in with your Google account that contains your Glide sheet.

3.2) Create a project.

Name it whatever you want and click create.
image

After a little while, your project will be created and will be your active project.

3.3) Then, you have to enable APIs and services.

3.4) You’re going to be redirected to the API Library. There, you need to activate both Google Drive API and Google Sheets API. It’s simple as clicking on them and pressing enable.


image

3.5) All good! Now we need to have access to that resources. So click on the sandwich menu icon and select APIs & Services > Credentials.
image

On the credentials page, click on create credentials and then go to service account.

Name it and write a description, then click create.
image

Grant it the Project > Editor role.
image

On the next page, go directly to create key button.

Select JSON and press create.
image

A file will be downloaded to your computer. Keep it in a safe place, we’re going to use it soon.
image

WARNING: Do NOT share this JSON file with ANYNONE.
This gives full access to your Google Drive. BE CAREFUL.

Press done.
image

Copy the service account e-mail to your clipboard.
image

And share your Glide sheet with that e-mail, with editor privileges.
image

4) Python Code!

How to do the code in Python

So now you’re going to do some code in Python. If it’s your first time doing that, I strongly recommend you watching some YouTube videos with Python basics. But I guarantee you, it’s simpler than it seems. Python is a high-level, easy comprehension programming language.

You can also take a look at gspread documentation, there you’ll have a lot of examples of how it works with Google Sheets.

For the tutorial, I’m going to code a little e-mail sender example that I think would be useful to many Glide users.

Oh, I almost forgot: It’s highly recommended using an editor such as Visual Studio Code, which is free, by the way.

4.1) Importing necessary packages
So, here, I’m going to import gspread package and some e-mail-sending packages.
image

4.2) Connecting with your sheet
Here, you need to set the scope, the service account and the sheet variables exactly as this
I recommend placing the .json file you downloaded earlier on the same folder as the script.

You can find your spreadsheet key in the spreadsheet URL, after the “/d/” part:

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

gc = gspread.service_account(filename='your-key-here.json')

sh_glide = gc.open_by_key('your-spreadsheet-key-here')

image

Then, you’ll need to select the worksheet that you want to work on, by using

worksheet = sh_glide.worksheet("Worksheet name here")
Note that if you change your worksheet name you must change it here too in order for it to work.

You can select multiple of them, just assign to different variables.

image

4.3) Iterating through cells
Now, we’re going to create a loop that iterates each row of our spreadsheet to send each user a e-mail.
To keep it simple, I’m going to use this structure:
image

So basically I’ll call a gspread function that returns me a list of rows.
values_list = worksheet.get_all_records()
image

If I print values_list, it ouputs this, which is a list of dictionaries. (If you aren’t familiar with that, don’t worry.)
image

Now, I’m going to iterate through every element of this list and assign each column to specific variables.

image

4.4) E-mail sending
To send the e-mails, I recommend you setting the configuration variables before the loop (for row in values_list) part. You’ll need SMTP, login and password for your desired e-mail service.
image

And then, there’s not much to explain about the code added, it just sends e-mails.
image

So, if you do everything right, you’ll be sending e-mails for free in just a click.

5) Automation

How to automate the script execution

There are several ways to automate this script. If you host it in a free tier instance of AWS or Google Cloud, you can use Linux crontab. If you’re running windows, you can use scheduled tasks. Because the tutorial is already huge, I won’t get into that. Maybe on a next episode :stuck_out_tongue:

Guys, let me know what you think of tutorials like this.
I know it is huge but I wanted to show you some of the infinite possibilities that Glide offers by using Google Sheets as the data source.

Feel free to contact me if you have any doubts or face any problems, I’ll be more than happy to help!

This is my thanks to Glide community, and I expect to be very active here on the forum.

See you soon, guys!

13 Likes

Finally a fellow Python developer!

I am running a Notebook to crawl data and push them to Google Sheets using crontab, every 15 minutes. The problem is that without spending money I can only run it while my computer is on.

Without touching AWS, do you know of an alternative to allow me to run the Notebook when my computer is off. Thank you!

Hey @ThinhDinh!

I don’t know of any other service that does that for free.

But both AWS and Google Cloud have free tiers.

I have at least 10 Python scripts running on free instances on AWS, paying absolutely nothing for that.
It is super simple to set-up and it gives you 24/7 uptime, again, for free!

If you need help setting that up, I could send you a quick tutorial!

3 Likes

Hey Gabriel thanks for the information, if you have time to send me a quick tutorial on setting up an AWS and run it with Notebook I would appreciate it very much!

I run the Notebook to crawl articles from sports websites like Guardian, Sky Sports, Telegraph etc. Not much problem with the programming, just with how to automatically run it periodically.

I live in Vietnam so if I want to advertise it to people from Western countries, I need to run it in the hours that I sleep as well, which are the afternoon - evening in your places.

Thank you in advance for that, and if you have any problems with Glide or Google sheet formulas/scripts I’m more than willing to help.

Awesome! I crawl a lot of websites too :stuck_out_tongue:
It’s an easy way of getting data, for sure!

I’ll make the tutorial here on this post soon, so other users can see it too, ok?

You’ll be the first one to know :smiley:

3 Likes

Incredible tutorial Gabriel!

Just, tell me, what is or are the advantage of using python instead of Google scripts & api? More things to do / more use cases?

I am going to test few things with python. I haven’t write any python line for years :grinning:

1 Like

Of course Gabriel, it would absolutely solve me a headache and I can apply more Python to my solutions for clients in the future!

1 Like

Thank you @Christophe_HK!

Python is much more powerful than Google Scripts because it has a huge number of packages that solves so many problems. If you google “How to _____ in Python”, you’ll see that in most cases, it already have a package that fulfills your requirements.

And if you go deeper and deeper, you’ll face machine learning, crawling, more options on scheduling, integrations with literally anything and much more.

Because Python is a complete and powerful programming language, it offers truly infinite possibilities.

And apart of that, I find Google Scripts a little difficult and with limited functionalities :frowning:

3 Likes

Python’s power, with its super community, can enable a lot of things to be done when combining with what we have here. I use Google Scripts a lot as well, but when a Python alternative is viable for integration I will definitely want to do it over Scripts.

3 Likes

Crystal clear :+1:t2::clap:t2:

1 Like

One further reason for me to opt for Python rather than Scripts is because I feel StackOverflow gives better answers for Python lol.

2 Likes

THIS.

1 Like

Thank you very much.

@Gabriel_Sobral thx for sharing. Very inspirational :grinning:

Have you any experience in using services responding in json and using this in google sheets?

Thanks @Krivo!

Sure, Python has the “requests” library, that makes calling APIs and WebServices easy.
Take a look on it’s documentation here.

You can handle retrieved data the way you want it!

1 Like

@Gabriel_Sobral I have written a few utilities using Python but so far have been able to do what I need to do for my custom Glide apps in Google Script. Using GS I can set up triggers to run these scrapping routines periodically. I also have used Python to write a few Alexa apps using Python running on AWS servers and/or using Amazon S3. I hadn’t thought of using AWS to run python code that includes the ability to update Sheets data. That would be interesting if I couldn’t figure out a way to do it in Google Script, or there was a killer library in Python that I could use for the functionality I needed.

Anyway bottom line is that I would be interested in seeing your tutorial and an example of some python code you are running on AWS servers that also updates data in a Google Sheet.

3 Likes

@George_B first of all, sorry for the delay, those days have been such a hurry!

Sure! As @ThinhDinh is interested too, I’ll make a tutorial and edit this post with it.

I’ll go through configuring an instance and using Linux crontab to schedule a automated Python script execution.

Hold tight!

@Edit
I plan to post it by Friday!

2 Likes

No worries Gabriel, can’t wait to see it! I would love to integrate AWS/Google Cloud into my flow so that it runs when I’m asleep!

1 Like

Is it possible to send WhatsApp notifications for free from glide using python.

@Mrinal_Chakraborty
Actually no.

That’s because any WhatsApp API is paid as WhatsApp itself charges this service.

But the notification sending script itself can be coded and hosted for free in AWS and GCP.

I have a Python script that performs an integration with Twilio, and sends WhatsApp notifications through my WhatsApp business account. That script itself is free, but Twilio is not.

Be careful with solutions that offers free WhatsApp notifications API! it’s probably a scam, as it violates WhatsApp guidelines.

1 Like