google-apps-script - 导入 CSV 时的问号符号

Question

我正在使用以下代码从电子邮件中导入 CSV：

function RetrieveAttachment() 
{
  var threads = GmailApp.search("Report*")
  var msgs = GmailApp.getMessagesForThreads(threads);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var today= new Date();today.setDate(today.getDate());
  var today= Utilities.formatDate(today, "GMT+1", "dd.MM.yyyy");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import");
  
  for(var i = 0; i < msgs.length; i++)
  {
    for(var j = 0; j < msgs[i].length; j++)
    {
      var msgdate = Utilities.formatDate(new Date(msgs[i][j].getDate()), "GMT+1", "dd.MM.yyyy");
      
      if(msgdate == today)
      {
        var attachments = msgs[i][j].getAttachments();
        
        for(var k = 0; k < attachments.length; k++)
        {
          var attachmentName = attachments[k].getName();
          var stringValue = attachmentName.search("*");
          
          if(stringValue > -1)
          {
            var attachmentData = attachments[k].getDataAsString();
            var parseCsv = Utilities.parseCsv(attachmentData, ",");
            
            sheet.clearContents();
            sheet.getRange(1,1, parseCsv.length, parseCsv[0].length).setValues(parseCsv);
          }
        }
      }
    }
  }
}

通常这工作得很好，但是我通过电子邮件收到一个特定的 csv，只有这两个问号被写在电子表格中：

我检查了调试器，显然正在读取 csv 的全部内容，但没有正确解析：

我认为这是一些编码问题，但是我尝试使用 base64Encode/base64Decode 实用程序没有任何成功。

有人对如何解决这个问题有任何线索吗？

标签： google-apps-scriptgoogle-sheets

Answer 1

原始 CSV 文件是 UTF-16 和制表符分隔，我解决了它指定编码和不同的分隔符：

var attachmentData = attachments[k].getDataAsString("UTF-16");
var parseCsv = Utilities.parseCsv(attachmentData, "\t");

这里是新代码

function RetrieveAttachment() 
{
  var threads = GmailApp.search("Report*")
  var msgs = GmailApp.getMessagesForThreads(threads);
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var today= new Date();today.setDate(today.getDate());
  var today= Utilities.formatDate(today, "GMT+1", "dd.MM.yyyy");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import");
  
  for(var i = 0; i < msgs.length; i++)
  {
    for(var j = 0; j < msgs[i].length; j++)
    {
      var msgdate = Utilities.formatDate(new Date(msgs[i][j].getDate()), "GMT+1", "dd.MM.yyyy");
      
      if(msgdate == today)
      {
        var attachments = msgs[i][j].getAttachments();
        
        for(var k = 0; k < attachments.length; k++)
        {
          var attachmentName = attachments[k].getName();
          var stringValue = attachmentName.search("*");
          
          if(stringValue > -1)
          {
            var attachmentData = attachments[k].getDataAsString("UTF-16");
            var parseCsv = Utilities.parseCsv(attachmentData, "\t");
            
            sheet.clearContents();
            sheet.getRange(1,1, parseCsv.length, parseCsv[0].length).setValues(parseCsv);
          }
        }
      }
    }
  }
}