Mail attachment sent by SAP to Google Sheets

Question

Briefly, from an attachment, I am looking to retrieve the data and import it into a Google Sheets spreadsheet.

Context:

An email is sent from SAP once an hour containing an attachment. This attachment can be in ".TXT" format but also in ".XLS" format but the data remains the same (no encoding parameter is available)
A script retrieves the content of the attachment.
If the attachment is in TXT format, then the data is imported into a Google Sheets. If the attachment is in XLS format, then the attachment is converted and the data is imported into a Google Sheets.
The email containing the attachment is deleted.

Problem:

If the attachment is in TXT format and I display the data as a string, I get this in the console:

After convert in Spreadsheet :

But for XLS format and I display the data as a string I get this in the console:

I presume that we can observe those "?" chars due to encoding issues ?

After convert in Spreadsheet :

Why some columns appeared and how to fix it ?

Note:

If I download the XLS file on my computer and then send it back by email and convert it, I don't get the same result as if I converted it directly from the email box. Why is this?

After Convert In Spreadsheet

My scripts :

function main(){
  var mailXLS;
  var mailTXT;
  var mailXLSDownloaded;
  var attachmentXLS;
  var attachmentTXT
  var attachmentXLSDownloaded;
  var dataXLS;
  var dataTXT;
  var dataXLSDownloaded;

  
  mailXLS = GmailApp.getMessageById( "182b5d38f39bf636" ); // XLS mail.
  mailTXT = GmailApp.getMessageById( "182b63be8c849b97" ); // TXT mail.
  mailXLSDownloaded = GmailApp.getMessageById( "182b6510aa9153d6" ); // XLS Downloaded mail.

  attachmentXLS = mailXLS.getAttachments()[0]; // Retrieve the attachment. (XLS file)
  attachmentTXT = mailTXT.getAttachments()[0]; // Retrieve the attachment. (TXT file)
  attachmentXLSDownloaded = mailXLSDownloaded.getAttachments()[0]; // Retrieve the attachment. (XLS Downloaded file)

  dataXLS =  attachmentXLS.getDataAsString(); // Data from XLS file.
  dataTXT =  attachmentTXT.getDataAsString(); // Data from TXT file.
  dataXLSDownloaded = attachmentXLSDownloaded.getDataAsString(); // Data from XLS Downloaded file.

  Logger.log( dataXLS );
  Logger.log( dataTXT );
  Logger.log( dataXLSDownloaded );
}

IF attachment is TXT:

  // IF attachment is TXT : 
  var delimiter;
  var body;
  var spreadsheet;

  delimiter = "|";
  body = Utilities.parseCsv( dataTXT, delimiter ); // 2-D Array.
  spreadsheet = SpreadsheetApp.create(); // new Spreadsheet.
  spreadsheet.getSheets()[0].getRange( 1, 1, body.length, body[0].length ).setValues( body ); // Retrieve data.

IF attachment is XLS:

  // IF attachment is XLS : 
  var params;
  params = {
    "title" : name,
    "parents" : [{ "id" : "1234"}]
  };
  Drive.Files.insert( params, attachment, { "convert" : true} );

Looking for someone to help me in order to get the same Data format for TXT & XLS

Thanks in advance for reading !

user_15693860 · Answer

  dataXLS =  attachmentXLS.getDataAsString("UTF-16"); // Data from XLS file.
  var body = Utilities.parseCsv(dataXLS,"	");
  var spreadsheet = SpreadsheetApp.create("test"); // new Spreadsheet.
  spreadsheet.getSheets()[0].getRange( 1, 1, body.length, body[0].length ).setValues( body ); // Retrieve data.

The issue was the encoding of the XLS file, Apps Script needed accurate encoding format in the instruction blob.getDataAsString() default is UTF-8 and it must be UTF-16.

You just need to add "UTF-16" like blob.getDataAsString("UTF-16")

Mail attachment sent by SAP to Google Sheets

1 Answers