SAP定时发送的TXT/XLS邮件附件导入Google Sheets格式统一及异常问题求助

Let's tackle your two main issues and adjust your script to ensure consistent results between TXT and XLS attachments.

1. Fix XLS Encoding Garbage & Extra Columns #

Why this happens: #

• The getDataAsString() method is built for text files, not binary formats like legacy .XLS (BIFF format). When you call this on a binary XLS file, it tries to interpret raw binary data as UTF-8 text, which results in those ? characters.
• Your current XLS workflow uses Drive.Files.insert with convert:true, but it doesn't align with how you process TXT files. Drive's auto-conversion might misinterpret the XLS structure, leading to extra columns.

Fix Steps: #

Instead of trying to read the XLS as a string, directly convert it to a Google Sheet via Drive, then extract the data in the same CSV-like structure as your TXT processing. This ensures consistency.

Here's how to adjust your XLS logic:

// Updated XLS Processing Logic
function processXLSAttachment(attachment) {
  // Upload XLS to Drive and convert to Google Sheet
  var fileMetadata = {
    title: "Temp_XLS_Conversion",
    parents: [{id: "1234"}] // Replace with your Drive folder ID
  };
  var convertedFile = Drive.Files.insert(fileMetadata, attachment, {convert: true});
  
  // Open the converted Google Sheet
  var tempSheet = SpreadsheetApp.openById(convertedFile.id).getSheets()[0];
  
  // Get all data from the sheet (matches TXT's 2D array structure)
  var dataRange = tempSheet.getDataRange();
  var xlsData = dataRange.getValues();
  
  // Clean up: Delete the temporary converted sheet
  Drive.Files.remove(convertedFile.id);
  
  return xlsData;
}

For your TXT processing, also add explicit encoding (SAP often uses Windows-1252 or ISO-8859-1 for exports) to avoid hidden encoding issues:

// Updated TXT Processing Logic
function processTXTAttachment(attachment) {
  var delimiter = "|";
  // Use explicit encoding matching SAP's export (adjust if needed)
  var txtContent = attachment.getDataAsString("Windows-1252");
  return Utilities.parseCsv(txtContent, delimiter);
}

Now you can use both functions to get a unified 2D array, then write to your target sheet the same way for both formats.

2. Explain Local Download & Resend Conversion Difference #

Why this happens: #

When you download the XLS attachment to your local machine and resend it, your email client (or OS) might alter the file:

• Some email clients encode binary files using Base64 or other formats during resending, which can corrupt the original XLS binary structure.
• Your local OS might add hidden metadata or convert the file to a different format (e.g., saving it as .XLSX instead of .XLS without you noticing).
• Drive's conversion engine treats the modified file differently than the original SAP-generated binary, leading to inconsistent results.

Fix: #

Always process the original SAP email attachment directly without downloading and resending it. This preserves the file's original binary structure, ensuring Drive conversion works as expected.

Final Unified Script Example #

Here's how to combine everything into a streamlined main function:

function main() {
  var targetFolderId = "1234"; // Replace with your target Drive folder ID
  var targetSheet = SpreadsheetApp.openById("YOUR_TARGET_SPREADSHEET_ID").getSheets()[0]; // Replace with your sheet
  
  // Fetch mails (you can loop through unread mails instead of hardcoding IDs)
  var mailXLS = GmailApp.getMessageById("182b5d38f39bf636");
  var mailTXT = GmailApp.getMessageById("182b63be8c849b97");
  
  // Process TXT
  var txtAttachment = mailTXT.getAttachments()[0];
  var txtData = processTXTAttachment(txtAttachment);
  writeToSheet(targetSheet, txtData);
  
  // Process XLS
  var xlsAttachment = mailXLS.getAttachments()[0];
  var xlsData = processXLSAttachment(xlsAttachment);
  writeToSheet(targetSheet, xlsData);
  
  // Delete processed mails
  mailXLS.moveToTrash();
  mailTXT.moveToTrash();
}

function processTXTAttachment(attachment) {
  var delimiter = "|";
  return Utilities.parseCsv(attachment.getDataAsString("Windows-1252"), delimiter);
}

function processXLSAttachment(attachment) {
  var fileMetadata = {title: "Temp_XLS_Conv", parents: [{id: "1234"}]};
  var convertedFile = Drive.Files.insert(fileMetadata, attachment, {convert: true});
  var tempSheet = SpreadsheetApp.openById(convertedFile.id).getSheets()[0];
  var data = tempSheet.getDataRange().getValues();
  Drive.Files.remove(convertedFile.id);
  return data;
}

function writeToSheet(sheet, data) {
  // Append data to the sheet (or overwrite as needed)
  sheet.getRange(sheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
}

内容的提问来源于stack exchange，提问作者BigBenne