GMail to GSheet App Script

It’s a typical IT practice to send alert messages via email, enabling a quicker and more informed response. That’s great for responding to individual incidents, but not to attempt to see any patterns in these messages or to generate daily reports to show trends. An easy solution is to create a free (I love free) GMail account and add it to the distribution list of the alert messages. Then add a Google App script to process each message and add selected message details to a Google Sheet. Sample code to process a message is included below.

Here’s a blow-by-blow description of what’s needed.

1. Create a new GMail account
2. Forward a few test emails to the new account
3. Log into the new Gmail account
4. Go to drive.google.com
5. Create the Google Sheet that will contain your information
6. Go to script.google.com
7. Paste in the sample code, replacing the shell code provided
8. Change the sample code to meet your needs
9. Give your project a name
10. Click the Run menu item to test out your code
11. Click the View menu item for additional debugging information
12. Click the Resource menu item and “Current project’s triggers”
13. Add a trigger to run the code at your desired interval

The final step you will probably want to add is a GMail filter to automatically archive the daily Google App Script summary email. You could add it to your code, but it’s a bit easier to just use a filter.

That’s it. Your data is now being collected in a Google Sheet awaiting your inspection, analysis, reporting and charting.

A good starting point for learning more about Google App Script can be found at:

https://developers.google.com/apps-script/

SAMPLE CODE

function processInboxToSheet() {

//  

// Grab up to 100 threads in the Inbox

//

 var start = 0;

 var threads = GmailApp.getInboxThreads(start, 100);

//

// SPREADSHEET_URL contains the URL of your Google Sheet. Replace

//    “YOUR SHEET” with your name.

// SHEET_NAME contains the name of the tab to append data to

//

 var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/YOUR SHEET/edit?usp=sharing';

 var SHEET_NAME = 'Sheet1';

 var result = [];

 var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

 var sheet = spreadsheet.getSheetByName(SHEET_NAME);

//

// Looping through the threads

// Get the message, then get the body of the email in plain text  

//

 for (var i = 0; i < threads.length; i++) {

   var messages = threads[i].getMessages();

   var content = messages[0].getPlainBody();

//

// If there is a body to the email, process its contents

//

   if (content) {

     var date;

     var time;

     var interface;

     var message;

//

// The body is returned as one long string.  I prefer to process it

// line by line, so use “.split” to load an array, each line separated

// by a new line.

//

     var array1 = [{}];

     array1 = content.split("\n");

//

// This is where your specific data gathering logic is placed

//

     for(var n in array1) {        

        if (array1[n].substring(0,11) == 'DATE/TIME =') {

           date = array1[n].substring(13,array1[n].length-9);

           time = array1[n].substring(23,array1[n].length);

        }

        if (array1[n].substring(0,8) == 'MESSAGE =') {

           message = array1[n].substring(11,array1[n].length);

        }

     }

//

// Add a row to the end of the sheet

//

     sheet.appendRow([date,time,message]);

//

// Finally, mark the message as read and move it to the archive

//

     threads[i].markRead();

     threads[i].moveToArchive();

     Utilities.sleep(500);

   }

 }

}