A workflow system based on email and Google Forms

Manuel Silva Robalinho
Jun 15 · 5 min read
Image for post
Image for post
https://blog.betalabs.com.br

The goal is to use the information collected by Google Forms, to apply a process of sending email with python, managing the information registered in Google spreadsheets in a massive and automatic way.

Definition:

Workflow is the sequence of steps necessary to automate business processes, according to a set of defined rules, allowing them to be transmitted from one person to another.

In theory, this management can be organized manually, but currently, most of the workflow is systematized in the context of information technologies, better known as IT systems.

What is the workflow from a technological point of view and how does it improve business processes?

Workflow technology refers to a set of tools that, when viewed from a macro-environmental perspective, bridge the gap between eventual units and the office of a particular company.

In general, workflow technology not only guarantees communication and information exchange between different departments involved in the same business process but also ensures that any and all tasks are performed by the employee for whom it is intended.

Image for post
Image for post
https://blog.betalabs.com.br

Development:

We need to develop 5 strategies to our work:

1. Create a Google Forms (very easy)

2. Define a key API to Google Drive

3. Share the information for Google Spreadsheet, that contains the collected information from Google Forms

4. Read Spreadsheet with python program to a pandas dataframe

5. Create python program to send email, with the information from a pandas dataframe

Let’s go:

1. Create a Google Forms

Google Forms is a search administration application included in the Google Drive office suite, along with Google Docs, Google Sheets, and Google Slides. Forms present all the collaboration and sharing features found in Documents, Spreadsheets, and Presentations. It is a powerful,easy and free way to create your inquiries and/or collect formatted information from your customers.

Go to https://docs.google.com/forms/ and create your form.

Image for post
Image for post
Google Forms example

Google automatically creates a spreadsheet Google Spreadsheet associated with the form, where it will put all the information received from filling out the form. It’s a spreadsheet, in the cloud. My example the spreadsheet created:

Image for post
Image for post
The spreadsheet generated with my google form

2. Define a key API to Google Drive

To access Google Drive with a program, we need to create our API. To create the API go to Google Console: https://console.developers.google.com/

A good work describes this process, here.

This configuration Google API, generates a JSON file with the access to your Google Drive.

Image for post
Image for post
My JSON File

3. Share the information for Google Spreadsheet, that contains the collected information from Google Forms

The last step gave us a key to use in our documents on Google Drive. This key is the element to whom we give sharing to our spreadsheet. Use the “client_email” information.

Image for post
Image for post
Image for post
Image for post

We can use this key to access the file in the python program.

4. Read Spreadsheet with python program to a pandas data frame

We need to import gspread and oauth2client in our python program.

The file “Client_My_Drive_API_Secret.json” is created during the configuration of the Google API. It’s a JSON file that contains the Keys to access Google Drive using a program. You need to put the JSON file in your work path.

Image for post
Image for post

Another way is to obtain a Key to the Spreadsheet.

For that we need to publish our Google Spreadsheet file: File > Publish on WEB > CSV format

Image for post
Image for post

In the Python program we access like that, to read the Spreadsheet and put then in a Pandas data frame:

Image for post
Image for post
Image for post
Image for post

5. I created a python program to send an email, with the information from a pandas data frame

I tried 2 ways, once using email.mime functions and another using Yagmail. Its easier to use yagmail.

I needed to import yagmail, and I created a cycle that reads the pandas data frame and calls the function sending email for each line.

Image for post
Image for post

In the body, we can use HTML to create a better design.

Image for post
Image for post

For the attachments only need to inform the path and name file to attach in the email. I tried to attach many types of files, including PDF, JPG, and ICS (for calendar appointments), with successful execution. One easier way to create ics files, I found the web page https://ical.marudot.com/, where we can define the event and download it to a file ics format.

This is the received email.

Image for post
Image for post

Conclusion

Process automation, digitization and industry 4.0 have a lot of innovation, often retrieving the pieces of software from each software house, incorporating much of the knowledge distributed across various platforms and languages. A practical example to manage safely and conveniently.

References:

Gain Access to Expert View — Subscribe to DDI Intel

Data Driven Investor

from confusion to clarity not insanity

Sign up for DDIntel

By Data Driven Investor

In each issue we share the best stories from the Data-Driven Investor's expert community. Take a look

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Manuel Silva Robalinho

Written by

Master's degree in Informatics. Interest in AI, Machine Learning and Deep Learning. More about me on http://mrobalinho.blogspot.com.br/

Data Driven Investor

from confusion to clarity not insanity

Manuel Silva Robalinho

Written by

Master's degree in Informatics. Interest in AI, Machine Learning and Deep Learning. More about me on http://mrobalinho.blogspot.com.br/

Data Driven Investor

from confusion to clarity not insanity

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store