How to import multiple uniquely named CSV attachments from GMAIL to GOOGLESHEETS

Question

I want to import 3 CSV attachments from a single GMAIL email. Attachment names are as follows: - Sales.csv - Labor.csv - ServerPerformance.csv I believe it can be done some how using the following:

var title = attachment.getName();

if (attachment.getContentType() === "text/csv" && title === "Sales.csv") {  

but could use some help with proper execution.

Below is the script I'm currently using to import based off of GMAIL Labels and CSV attachment name but it is only importing the first attachment (Sales.csv), so I'd like to have 1 label "South Loop" with 3 separate attachments to be imported from a single email to google sheets.

function SLSalesImportFromGmail() { 
  //gets first(latest) message with set label
  var threads = GmailApp.getUserLabelByName('South Loop').getThreads(0,1);
  if (threads && threads.length>0) {
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];
  var title = attachment.getName();

  // Is the attachment a CSV file
  attachment.setContentTypeFromExtension();
  if (attachment.getContentType() === "text/csv" && title === "Sales.csv") {                            
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("South Loop Sales");
    //parses content of csv to array
    var dataString = attachment.getDataAsString();
    var csvData = CSVToArray(dataString);
    // Remember to clear the content of the sheet before importing new data
    sh.clearContents().clearFormats();                                         
    //pastes array to sheet
    var lastRowValue = sh.getLastRow();
    for (var i = 0; i < csvData.length; i++) {
       sh.getRange(i+lastRowValue+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    } 
    if( message.getSubject().indexOf('END OF DAY SALES DETAILS') !== -1) {
    SLlogTodaysSales();
    }
  if (attachment.getContentType() === "text/csv" && title === "Labor.csv") {                            
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("South Loop Labor");
    //parses content of csv to array
    var dataString = attachment.getDataAsString();
    var csvData = CSVToArray(dataString);
    var range = sh.getRange("A:K");
    // Remember to clear the content of the sheet before importing new data
  range.clear();
    //pastes array to sheet
    var lastRowValue = sh.getLastRow();
    for (var i = 0; i < csvData.length; i++) {
       sh.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    } 
    if( message.getSubject().indexOf('END OF WEEK LABOR DETAILS') !== -1) {
    SLlogWeeksLabor();
    }
  if (attachment.getContentType() === "text/csv" && title === "ServerPerformance.csv") {    
    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName("South Loop Server Report");
    //parses content of csv to array
    var dataString = attachment.getDataAsString();
    var csvData = CSVToArray(dataString);
    var range = sh.getRange("A:M");
    // Remember to clear the content of the sheet before importing new data
  range.clear();                                    
    //pastes array to sheet
    var lastRowValue = sh.getLastRow();
    for (var i = 0; i < csvData.length; i++) {
       sh.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
    } 
    if( message.getSubject().indexOf('END OF DAY') !== -1) {
    SLlogTodaysServers()
    }
  }
  }
  }
  //marks the Gmail message as read, unstars it and deletes it using Gmail API (Filter sets a star)
  message.markRead();
  message.unstar();
  Gmail.Users.Messages.remove("me", message.getId()); // Added
    }
}
//The code formats the code so it can be entered into the Google Script

function CSVToArray( strData, strDelimiter ){ 
  // Check to see if the delimiter is defined. If not,
  // then default to comma.
  strDelimiter = (strDelimiter || ",");

  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +


      // Quoted fields.
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +


      // Standard fields.
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );


  // Create an array to hold our data. Give the array
  // a default empty first row.
  var arrData = [[]];

  // Create an array to hold our individual pattern
  // matching groups.
  var arrMatches = null;

  // Keep looping over the regular expression matches
  // until we can no longer find a match.
  while (arrMatches = objPattern.exec( strData )){

    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[ 1 ];

    // Check to see if the given delimiter has a length
    // (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know
    // that this delimiter is a row delimiter.
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){

      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );

    }

    // Now that we have our delimiter out of the way,
    // let's check to see which kind of value we
    // captured (quoted or unquoted).
    if (arrMatches[ 2 ]){

      // We found a quoted value. When we capture
      // this value, unescape any double quotes.
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );

    } else {

      // We found a non-quoted value.
      var strMatchedValue = arrMatches[ 3 ];

    }

    // Now that we have our value string, let's add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }

  // Return the parsed data.
  return( arrData );
  var label = GmailApp.getUserLabelByName("South Loop");
  label.deleteLabel();
}
function SLlogTodaysSales() {
  var todaysSales = SpreadsheetApp.getActive().getRange('South Loop Sales Log!SLSalesImport');
  var logSheet = todaysSales.getSheet();
  logSheet.appendRow(
    todaysSales.getValues()
    .reduce(function(a, b) { return a.concat(b); }) // flatten the 2D array to 1D
  );
}
function SLlogWeeksLabor() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('South Loop Labor Log');
  var rg=sh.getRange('SLLaborImport');
  var vA=rg.getValues();
  sh.getRange(sh.getLastRow()+1,1,vA.length,vA[0].length).setValues(vA);
}
function SLlogTodaysServers() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('South Loop Server Log');
  var rg=sh.getRange('SLServerReport');
  var vA=rg.getValues();
  sh.getRange(sh.getLastRow()+1,1,vA.length,vA[0].length).setValues(vA);
}

Answer 1

So looking at your code I don't see you iterating through the attachments.

You only are getting the first attachment and then analyzing that without caring for the others.

  var attachment = message.getAttachments()[0];

So instead of doing this, take all the attachments with the getAttachments() and make a for loop to analyze every one of them.

var attachments = message.getAttachments();
for(var i=0; i < attachments.length; i++){
   var attachment = attachments[i]; 

   //....
   // The rest of your code needs to go inside this for loop
}

Answer 2

The issue you have is that you're only looking at the first attachment because of this statement var attachment = message.getAttachments()[0];. Instead, just drop the [0] and loop through all of your attachments.

In an effort to make this clearer, I got rid of a bunch of your code for this example. Please review my notes after, as well.

function SLSalesImportFromGmail() {
  var ss = SpreadsheetApp.getActive(); // Get the spreadsheet file once

  //gets first(latest) message with set label
  var threads = GmailApp.getUserLabelByName('South Loop').getThreads(0,1);
  if (threads && threads.length > 0) {
    var message = threads[0].getMessages()[0];

    // Get all of the attachments and loop through them.
    var attachments = message.getAttachments(); 
    for (var i = 0; i < attachments.length; i++) {
      var attachment = attachments[i];
      var title = attachment.getName();

      // Is the attachment a CSV file
      attachment.setContentTypeFromExtension();
      var table = Utilities.parseCsv(attachment.getDataAsString());
      if (attachment.getContentType() === "text/csv") {
        // Update the specified sheets
        // Clears the sheet of values & formatting and inserts the new table
        // using the Apps Script built-in CSV parser.
        switch (title) { 
          case "Sales.csv":
            ss.getSheetByName("South Loop Sales").getRange("A:M").clear();
            ss.getSheetByName("South Loop Sales").getRange(1, 1, table.length, table[0].length).setValues(table);
            break;
          case "Labor.csv":
            ss.getSheetByName("South Loop Labor").getRange("A:M").clear();
            ss.getSheetByName("South Loop Labor").getRange(1, 1, table.length, table[0].length).setValues(table);
            break;
          case "ServerPerformance.csv":
            ss.getSheetByName("South Loop Servers").getRange("A:M").clear();
            ss.getSheetByName("South Loop Servers").getRange(1, 1, table.length, table[0].length).setValues(table);            
            break;
        }
      }      
    }
    if (message.getSubject().indexOf('END OF DAY') !== -1) {
      SLlogTodaysSales();
      SLlogTodaysServers();
    }
    if (message.getSubject().indexOf('END OF WEEK') !== -1) {
      SLlogTodaysSales();
      SLlogTodaysServers();
      SLlogWeeksLabor();
    }
    // Marks the Gmail message as read, unstars it and deletes it using Gmail API (Filter sets a star)
  message.markRead();
  message.unstar();
  Gmail.Users.Messages.remove("me", message.getId()); // Added
  }
}

Notes:

• You're using a custom CSVToArray() function to convert the CSV file into an array, but Google already provides a method for this with Utilities.parseCsv(). It should suffice in most cases.
• There is no need to call both clearContents() and clearFormats(), because clear() does both in a single call.
• I'm aware that you have additional logic (e.g. "END OF DAY" emails & slightly different rules for Labor & ServerPerformance). As mentioned before, I removed all of that to demonstrate how to loop through the attachments. You can use an if or switch statement to include that logic in your script, instead of necessarily using the updateSheet() closure that I wrote.