Import data through Excel sheet in Google sheet with Google Script

0
votes

I'm trying to get data from a csv file in attachment in my my Gmail, parse it and import it with Google Script in Google Sheets.

Problem is when I get content type of my attachment I have this message "application/octet-stream" and not CSV so I think algorithm doesn't keep on in if condition.

Do you have suggestion why I don't get right file type ?

Big thanks !

Here's the code :

function importCSVFromGmail() {

  var threads = GmailApp.search("Money Transfer Tracking - Tickets");
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  console.log(attachment.getContentType())

  // Is the attachment a CSV file
  if (attachment.getContentType() === "text/csv") {

    var sheet = SpreadsheetApp.getActiveSheet();
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ";");

    // Remember to clear the content of the sheet before importing new data
    sheet.clearContents().clearFormats();
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

  }

}
1
csvgoogle-apps-scriptimportgmailspreadsheet

1 Answers

0
votes

If you want to use if (attachment.getContentType() === "text/csv") {}, it is required to set the mimeType of text/csv. Also if there is the possibility that attachment has various mimeTypes, how about this modification?

Pattern 1:

If the attachment file has the filename including the extension, how about this modification? I think that this is more suitable way for your situation, because the attachment files of email often have the filename and the extension.

var attachment = message.getAttachments()[0];
attachment.setContentTypeFromExtension(); // Added
  • The important point of this modification is that the filename of the file is required to have the extension. If the file has no extension, the mimeType becomes null.

Pattern 2:

If the attachment file has the filename without including the extension, how about this modification?

var attachment = message.getAttachments()[0];
attachment.setContentType("text/csv"); // Added
  • The important point of this modification is that the mimeType of file is forced to be modified.

Note:

  • In above modifications, I introduced about the method for changing the mimeType. I'm not sure about your filename of file and detail situation. So please modify your script for your situation using above methods. If you have any questions, feel free to tell me.

References:

If this was not the result you want, I apologize.