将CSV附件导入Google表格 [英] Import CSV attachment into google sheets

问题描述

我想做的是获取CSV附件的内容以输出到Google表格中.不知道我是否正确地执行了操作，但是我在网上找到了它，然后运行它，然后什么也没发生.

What I am trying to do, is get the contents of the CSV attachment to output onto Google Sheets. No idea if I am doing this correctly, but I found this online, ran it, and then nothing happens.

function importCSVFromGmail() {
    var threads = GmailApp.search("from:[e-mail]");
    var message = threads[0].getMessages()[0];
    var attachment = message.getAttachments()[0];

    // 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);
    }
}

推荐答案

在我的环境中，我确认从电子邮件中检索附件*.csv文件时，mimeType的检索方式为application/vnd.ms-excel.我认为什么都没有发生"的原因是这样.那么这些修改呢?我认为针对您的情况有几种解决方法.因此，请考虑将这些修改视为其中的3种.

In my environment, I confirmed that when the attached *.csv file is retrieved from the email, the mimeType is retrieved as application/vnd.ms-excel. I think that the reason of "nothing happens" is this. So how about these modifications? I think that there are several workarounds for your situation. So please think of these modification as 3 of them.

• 模式1:通过文件名设置mimeType.
• 模式2:将mimeType用作application/vnd.ms-excel
• 模式3:使用扩展名.

请在if (attachment.getContentType() === "text/csv") {之前添加以下脚本.

Please add the following script before if (attachment.getContentType() === "text/csv") {.

attachment.setContentTypeFromExtension();

模式2:

请进行如下修改.

Pattern 2:

Please modify as follows.

if (attachment.getContentType() === "text/csv") {

到:

if (attachment.getContentType() === "application/vnd.ms-excel") {

模式3:

请进行如下修改.

Pattern 3:

Please modify as follows.

if (attachment.getContentType() === "text/csv") {

到:

var fileName = attachment.getName().toUpperCase().split(".");
if (fileName.length > 1 && fileName[1] == "CSV") {

注意:

• 关于模式1，通过使用setContentTypeFromExtension()从文件名的扩展名中设置mimeType.
• 关于模式2，如果在您的环境中将CSV文件检索为其他mimeType，请修改application/vnd.ms-excel.
• 关于模式1和3，如果CSV文件的文件名没有扩展名或其他扩展名，则在这种情况下不能使用它.

Note:

• About pattern 1, the mimeType is set from the extension of the filename by using setContentTypeFromExtension().
• About pattern 2, if in your environment, CSV files are retrieved as other mimeType, please modify application/vnd.ms-excel.
• About pattern 1 and 3, if the filename of CSV file has not extension or other extension, it cannot be used under the situation.
• setContentTypeFromExtension()

如果这些不是您想要的，对不起.

If these were not what you want, I'm sorry.

这篇关于将CSV附件导入Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！