How to copy data from Gmail message attachment to a spreadsheet?

Question

I'm trying to get the data out of a CSV report, located in a specific folder from a shared email and replace the old data from a specific tab in Google Sheets with new ones.

I already tried different solutions, but none seems to get me where I want. The code below is the cleanest that I produced but, for some reason, is not doing anything.

Steps that I followed:

1. Open email where the folder is located(it is a shared email, managed in Gmail)
2. Search for the folder where is located (label:rpt-its-all-data)
3. Take the last email received
4. Open email/attachment
5. Take the data
6. Open Google sheets by ID
7. Update date in cell B1 in tab INFO with the current date(I didn't even try to sort this one out)
8. Open tab ALLDATA
9. Paste values in tab ALLDATA

This is a copy of the Google Sheets to see the format: https://docs.google.com/spreadsheets/d/1GyGxSwUtITeje0-Qx63rB8xChMSbLvn1FDCOKOJITio/edit?usp=sharing

I already tried this code:

function importCSVFromGmail() {
    var threads = GmailApp.search("id:AH1rexR2yzlanbk9GyP52tyVKSnXcMDz_miTs1-lLXtJwJB56l2r label:rpt-its-all-data");
    var message = threads[0].getMessages()[0];
    var attachment = message.getAttachments()[0];

    // Is the attachment a CSV file
    if (attachment.getContentType() === "text/csv") {
        var id = "1F_Z_00-ThdTRzlZ3nYd_pieJHkUjgye3pI_KJfydoP4";
        var name = "ALLDATA";
        var sheet = SpreadsheetApp.openById(id);
        var tab = sheet.getSheetByName(name);
      
        // Clear the content of the sheet
        tab.clearContents().clearFormats();
        tab.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
      
        var csvData = tab.Utilities.parseCsv(attachment.getDataAsString(), ",");
    }
}

With the above code, I didn't get any results. I'm not sure why this is happening.

I don't think that the mail ID in the query is correct to call that email, but I couldn't find any other way to do it.

Answer 1

I modified your code with these changes so it can work as intended:

1) You were having errors because the csvData variable had to be declared before you can use it. Also, the parseCsv() function is from the Utilities class and you can directly use this class (you were calling it like this: tab.Utilities.parseCsv) [1]. The other problem was that you can't search messages using the message id [2], for that you should make a get request [3].

2) I put an If to validate that the message has an attachment.

3) I changed the condition in your If to validate if it's a csv file with the file extension in its file name, because the gmail API takes a csv file with "application/octet-stream" as mime type (i guess this is because you can open it as a Google Sheets in gmail).

4) I modified how you were getting the message to get the last message of that last thread, previously it was getting the first message of each thread and not the last one.

5) Set the date in the INFO sheet, you can check the formatDate function if you want a specific format [1].

function importCSVFromGmail() {
  var threads = GmailApp.search("label:rpt-its-all-data");
  var message = threads[0].getMessages().pop();

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

  if (attachment != null) {
    var attachName = attachment.getName();

    // Is the attachment a CSV file
    if (attachName.substring(attachName.length-4) === ".csv") {
      var id = "1bvENjFYC48LSYDMPtI4FIOKIChiuIrg5O4WRziFeAhU";
      var name = "ALLDATA";
      var sheet = SpreadsheetApp.openById(id);
      var tab = sheet.getSheetByName(name);
      var tabInfo = sheet.getSheetByName("INFO");
      tabInfo.getRange("B1").setValue(new Date());

      // Clear the content of the sheet
      tab.clearContents().clearFormats();
      var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");
      tab.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    }
  } 
}

[1] https://developers.google.com/apps-script/reference/utilities/utilities#formatDate(Date,String,String)

[2] https://support.google.com/mail/answer/7190

[3] https://developers.google.com/gmail/api/v1/reference/users/messages/get