Collate and perform calculations on daily CSV reports in gmail

Question

I receive a number of emails to my Gmail account which contain CSV files with numerical data.

The CSV format is identical and they are sent using SendGrid on a daily basis.

I have to manually collate and process the CSV files in iPython and generate summary calculations.

Is there a simple google script I can deploy which will automatically save these CSV files to my Google Drive so I can run a batch script to process them at once?

I am relatively familiar with Python and scripting, but I can't seem to locate any basic tutorials on Google Scripts for this.

Thanks in advance.

I found this Retrieve csv attachment file from gmail and place the data in a google spreadsheet

and this Trigger Google Apps Script by email

but was hoping for an good and easy to follow tutorial.

score 0 · Answer 1 · answered Nov 28 '15 at 20:25

OK, this was a lot simpler than I thought

  var targetFile = "1**************vr4PchIo1wcpTEQU"; //               Output file
 var targetSheet = "booking_weekly";  // output sheet


function Main() {
  /Different reports and client abbreviations 
  reportname = '_booking_weekly'

     clientnames = ['OO','TP','TG',]

      for(var i = 0;i <clientnames.length;i++){
           clientname = clientnames[i]

    /Set the search string up
        searchstring = 'subject:' + clientname + reportname
        var searchedThreads = GmailApp.search( searchstring)[0];
        var id = searchedThreads.getId(); 
        var attachtextLength = GmailApp.getMessageById(id).getAttachments()[0].getDataAsString().length;
        if (attachtextLength <= 2000){
            var attachtext = GmailApp.getMessageById(id).getAttachments()[0].getDataAsString();
            UpdateSheet(attachtext,clientname,reportname);
           }
        }

}


function UpdateSheet(dataString,client,report) {
      //ommited CSVTo Array function as it is in the Tutorials
  var dataArray = CSVToArray(dataString);
  var myFile = SpreadsheetApp.openById(targetFile);
  var mySheet = myFile.getSheetByName(targetSheet);
  var lastRowValue = mySheet.getLastRow();
  for (var i = 0; i < dataArray.length; i++) {
        mySheet.getRange(i+lastRowValue+1, 3, 1,     dataArray[i].length).setValues(new Array(dataArray[i]));
  //Pass client name to sheet            
  mySheet.getRange(i+lastRowValue+1, 1, 1, 1).setValue([client]);

   //Passs reportname to sheet
   mySheet.getRange(i+lastRowValue+1, 2, 1, 1).setValue([report]);
  }
   mySheet.deleteRows(lastRowValue+1)
}

Collate and perform calculations on daily CSV reports in gmail

1 Answers1