Automating your life with python and google sheets.

The other day, my wife and I were sitting on a bus on our way to a shopping mall. I noticed my wife was glued to her phone. I wondered and asked what is it she was looking at. She told me that she was searching for promotions in products she is interested to buy. However, she gets distracted with the ads that appear constantly and the irrelevant promotions that she has to scroll over and over again.

Surely, there must be a better way. I wondered. Is it not possible to look at only the promotions we are interested in? By streaming through those photos of ads and irrelevant products on our devices, we are wasting both our time and exhausting our data consumption. In fact, we may ended up not buying anything on the web, because we get too distracted or frustrated that forgot what we wanted to buy!

I told her, we can automate this process of looking at promotions. We can set it up to look at only what we wanted using python programming and google sheets. All these can be done using our handphones, no computers needed.

She told me, it will be a great help, if this can be done. So, I decided to work on it. I am using visual studio code to write my python scripts. You can download this free software from this link, Download Visual Studio Code — Mac, Linux, Windows

Also, you will need to download some libraries in order to let python work its magic. I will be using the following as seen below. You can do pip install to install these libraries directly in the terminal in visual studio code.

We will be webscraping the website that my wife uses to look at promotions. From there, we will export the data into google sheets. Once the data is in google sheets, we will extract only those promotions she is interested in. After that, we will set up an email trigger to send her an email, featuring only on those promotions that she is interested in and that these promotions is still on going with its validity date. It means there is no need to for her to go to the website to check on promotions any more. Hmm.. life is so much better when you can use technology to automate your tasks.

Webscraping is done with the module beautifulsoup in python. It does require a basic understanding of html. For those interested to find out more, do read up the docs. Beautiful Soup Documentation — Beautiful Soup 4.9.0 documentation (crummy.com) See below for webscraping promotion texts.

Using python, I will convert these texts into a dataframe (i.e table format) with just a couple lines of code. Once this is done, we will export this dataframe to google sheets. See below

For those who don’t know, google sheets is like microsoft excel. It has many functionality like excel and allows you to view your spreadsheet in your phone with a free google sheets app from the playstore.

Google sheets has many inbuilt functions. I will be using google scripts to remove duplicates of repeated promotions, filter only those promotions we are interested and trigger an email to my wife’s email account. For privacy considerations, I will blank out her email contact here (see below). To setup an email trigger, do check out the google guide on this. See link Tutorial: Sending emails from a Spreadsheet | Apps Script (google.com)

My wife has told me she wanted a starbucks coffee to start her day. Also, she is in the mood for some pastas for dinner. Using these keywords, I have wrote a google sheet formula to filter those words under the column “Promos wanted”. Those promotions with these keywords will be marked as 1. Thereafter, I wrote a google script to scan through the column “Promos wanted” to trigger an email on the promotions to her email account as seen in the email sent column. Once this email is triggered once, it will not be trigger again. In other words, no spam mail. See below for the promotion emails sent to her inbox.

The google spreadsheet has logged in all the promotions for the year. It presents a whole spectrum of possibilities. If you have been looking for promotions, you can check the spreadsheet for promotions held during the year. This allows you to plan your buying accordingly to maximise your purchasing needs. If you are a marketing manager from an organisation, you can check with the merchants on doing joint promotions and plan your promotions at the same time with strong branding companies.

To run this python script on your phone, you can download a python app from playstore. Do remember to download the libraries needed as well. I had used pydroid3 app for this exercise and copied my python script to my wife’s handphone. Now, she just need to activate the app, run the script as and when she is looking for promotions. She will get a nice email informing her on promotions she is looking for the very next day.

Now, she don’t spend time looking at promotions. In fact, when I saw her glued to her handphone again, I asked what is she looking at. She told me, she is watching Netflix. She asked, if there is a way to recommend movies she is interested. I told her, it has already been done by the algo working behind the scenes. Technology has influenced and impacted many aspects of our lives.

Do connect me, through my linkedin account Chew Lin Kiat | LinkedIn for future updates.