Getting Automated with Google Script

How to use email to avoid manual interaction

Cristian started his IT career in 2005 and in over 11 years of his career, he has had various roles in development, team leadership, business management, project management and line manager, being involved in over 25 projects, working with clients like Goldman Sachs, Credit Suisse, Voalte, Truevision. He also contributed to the growth of these clients and refining work-at-department level in organizational and process areas. Moreover, he has often managed to provide the necessary vision for colleagues to advance in career paths and evolve within Softvision.

Latest posts by Cristian

Getting things automated with Google Script will make our lives easier. However, most of the time all the data that you need to use as an input is not available in Google Drive directly. A simple way to import it, of course, would be to export it first and import to Google Drive, but that requires some manual interaction. What if we don’t want that? No manual interaction at all?

The solution is email. Most of the tools can send a report on email. If it is a text report then it’s easy to work with, and if it has an attachment (like documents or Excel) then it is even better since it can be imported automatically!

The purpose of this lab is to show you how to intercept those kinds of reports and how to import them in a Google Script so that you may then do… whatever it is that you need to do with it 🙂

/**
This function will search through email with a specific subject and read the most recent one.
In this case, it has an Excel attachment that will be saved to Google Drive and parsed.
**/
function loadContent(){
//search for the emails with data
var messages = GmailApp.search("subject:\"SEARCH SUBJECT CRITERIA\"")[0].getMessages();
//Log message date just to make sure we are using the latest report
Logger.log(messages[0].getSubject()+" --date-- "+messages[0].getDate());
var file = DriveApp.createFile(messages[0].getAttachments()[0]);
var sheet=SpreadsheetApp.openByUrl(file.getUrl()).getSheetByName('Sheet1');
//Load Range
var range=sheet.getRange("A1:B").getValues();
//Now you have all the data from the attachment available to do what need to be done
//TBD
}

Softvision is made up of Communities of talent, and a real challenge is to keep all the Community aliases in sync (often there are employees moving between Communities, new hires or other similar events). An alias is a Softvision G Suite Group Alias that contains all the employees from a specific Community.

A manual interaction is usually required by the IT support team (or somebody) on every event mentioned above, however, we will automate this using Google Script.

We will use Fusion (the Company tool) to get the employees’ data. This tool is capable of sending out periodic or event-driven reports on an email that contains an Excel document attached with the Community data.

Once we receive the email above, we will run the script (see sample code section) to intercept the data and then update our G Suite aliases immediately.

Furthermore, it is possible to setup triggers on when to run the script automatically, that will take us to a zero manual intervention solution once completed.

For the complete script or if you have any questions, feel free to reach out to me. Thanks!

Cristian started his IT career in 2005 and in over 11 years of his career, he has had various roles in development, team leadership, business management, project management and line manager, being involved in over 25 projects, working with clients like Goldman Sachs, Credit Suisse, Voalte, Truevision. He also contributed to the growth of these clients and refining work-at-department level in organizational and process areas. Moreover, he has often managed to provide the necessary vision for colleagues to advance in career paths and evolve within Softvision.