Import CSV from an email attachment in Gmail with a specific label

Question

I receive 12 emails every day containing CSV data (Temp01,Temp02.....) that I have to import into 12 different sheets (CapTemp01, CapTemp02...).

Each email is labeled from 01 to 12 (label CapTemp01, 02, 03...).

The problem is that the script applies to the whole thread, not just the message, which is problematic because all these emails are part of the same thread.

For example, the current script imports CSV data on email labeled 05 regardless of the GmailApp.search(query)

function importCapTemp05() {
  var labelName = "CapTemp05";
    var start = 0;
    var max = 20;
    var threads = GmailApp.search("is:unread label:" + labelName, start, max);
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  // Is the attachment a CSV file
  if (attachment.getContentType() === 'text/csv') {
    var sheet = SpreadsheetApp.openById("1SRo3iBaza4l0G9mlg-jZ589lwaORtx_jfPgX3gR73P0").getSheetByName("CapTemp05_G");
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ',').slice(1);

    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    var column = sheet.getRange("B2:B");
                column.setNumberFormat("@")
                var ranges = sheet.getRange("B2:B");
                var data = ranges.getValues();

                for (var row = 0; row < data.length; row++) {
                    for (var col = 0; col < data[row].length; col++) {
                        data[row][col] = (data[row][col]).toString().replace(".", ",");
                    }
                }
                ranges.setValues(data);
            }
    GmailApp.markMessageRead(message);
  }

Maybe using Gmail.Users.Messages.list

Does anyone know how to import CSV data only to a message with a specific label?

Thanks

What are the names of the attachments? and how do they correlate to sheet names? — Cooper, Apr 19 '23 at 20:11
They are temperature sensors. E.g. for sensor 08 the name of the attachment: CapTemp08 (the place)_export_(the date).csv. In gmail the label is CapTemp08 and the sheet name is the same: CapTemp08. — Galameo, Apr 21 '23 at 03:45
Related question: [search messages with Gmail API in Google Apps Script](https://stackoverflow.com/a/36008044/1330560) — Tedinoz, Apr 23 '23 at 01:04

score 0 · Answer 1 · answered Apr 23 '23 at 02:55

This answer follows the broad logic of the OP script. It is old-school in that it uses conventions loops and is light-on with Javascript which might improve the efficiency of the script.

Note: the script focuses on a single label ("CapTemp05") and single sheet ("CapTemp05_G"). The OP is silent on how each day's csv file should be treated, so I have added each day's CSV data to the bottom of sheet.

The logic of the script is:

get threads based on GmailApp.search("is:unread label:CapTemp05")
- loop through the threads and get the messages
  - loop through the messages and get the attachments
  - get the mime type - att[a].getContentType().
    In the OP script, the mime type is "text/csv". None of my tests returned this type; "txt" files returned text/plain.
    My testing of csv files returned a mime type of application/vnd.ms-excel.
    In any event, the user to identify the mime type of each attachment with:
    Logger.log("DEBUG: a:"+a+", type"+att[a].getContentType()+", name: "+att[a].getName())
  - test for the desired mime type:
    if (att[a].getContentType() === 'application/vnd.ms-excel'){
    then parse the csv: var csvData = Utilities.parseCsv(att[a].getDataAsString());
  - paste the file into the sheet: range.setValues(csvData);

function gMailImportAttchment() {
  var labelName = "CapTemp05";
  var start = 0;
  var max = 20;
  var threads = GmailApp.search("is:unread label:CapTemp05");
  // Logger.log(threads) // DEBUG
  // loop through the threads
  for (var i=0;i<threads.length;i++){
    // get the messages for the thread
    var mess = threads[i].getMessages()
    // loop through each message to find an attachment
    for (var m=0;m<mess.length;m++){
      var att = mess[m].getAttachments()
      // Logger.log("DEBUG: i="+i+", item: "+threads[i]+", messages: "+mess+", m:"+m+", attachments: "+att+", number of attachments: "+att.length)
      // make sure that there is an attachment
      if (att.length>0){  
        // loop through the attachments    
        for (var a=0;a<att.length;a++){
          var aName = att[a].getName()
          // Logger.log("DEBUG: a:"+a+", type"+att[a].getContentType()+", name: "+att[a].getName())
          // Is the attachment a CSV file
          if (att[a].getContentType() === 'application/vnd.ms-excel'){
          var csvData = Utilities.parseCsv(att[a].getDataAsString());
          // Logger.log("DEBUG: csv length: "+csvData.length+", csv width: "+csvData[0].length)
          // define the range to save the attachment
          var ss = SpreadsheetApp.getActiveSpreadsheet()
          var sheet = ss.getSheetByName('CapTemp05_G')
          var lr = sheet.getLastRow()
          var range = sheet.getRange(lr+1, 1, csvData.length, csvData[0].length)
          // Logger.log("DEBUG: the range is : "+range.getA1Notation())
          //range.clearContent()
          range.setValues(csvData);
          // Logger.log("DEBUG: data saved")
          }          
        }
      }  
    }
  }
}

Import CSV from an email attachment in Gmail with a specific label

1 Answers1