如何将数据从Gmail邮件附件复制到电子表格？ [英] How to copy data from Gmail message attachment to a spreadsheet?

问题描述

我正在尝试从共享电子邮件的特定文件夹中的CSV报告中获取数据，并用新数据替换Google表格中特定标签中的旧数据。

I'm trying to get the data out of a CSV report, located in a specific folder from a shared email and replace the old data from a specific tab in Google Sheets with new ones.

我已经尝试过不同的解决方案，但是似乎没有一个可以让我满意。下面的代码是我生成的最干净的代码，但是由于某种原因，它什么也不做。

I already tried different solutions, but none seems to get me where I want. The code below is the cleanest that I produced but, for some reason, is not doing anything.

我遵循的步骤：

1. 打开文件夹所在的电子邮件（这是共享电子邮件，在Gmail中管理）


2. 搜索文件夹所在的文件夹（标签：rpt-its-all-data ）


3. 接收上一封收到的电子邮件


4. 打开电子邮件/附件


5. 获取数据


6. 按ID打开Goog​​le表格


7. 使用当前日期更新 INFO标签中B1单元格中的日期（我什至没有尝试过将其排序）


8. 打开选项卡ALLDATA


9. 在选项卡ALLDATA中粘贴值

1. Open email where the folder is located(it is a shared email, managed in Gmail)
2. Search for the folder where is located (label:rpt-its-all-data)
3. Take the last email received
4. Open email/attachment
5. Take the data
6. Open Google sheets by ID
7. Update date in cell B1 in tab INFO with the current date(I didn't even try to sort this one out)
8. Open tab ALLDATA
9. Paste values in tab ALLDATA

这是一个Google表格的副本以查看格式： https：// docs.google.com/spreadsheets/d/1GyGxSwUtITeje0-Qx63rB8xChMSbLvn1FDCOKOJITio/edit?usp=sharing

This is a copy of the Google Sheets to see the format: https://docs.google.com/spreadsheets/d/1GyGxSwUtITeje0-Qx63rB8xChMSbLvn1FDCOKOJITio/edit?usp=sharing

我已经尝试了以下代码：

I already tried this code:

function importCSVFromGmail() {
    var threads = GmailApp.search("id:AH1rexR2yzlanbk9GyP52tyVKSnXcMDz_miTs1-lLXtJwJB56l2r label:rpt-its-all-data");
    var message = threads[0].getMessages()[0];
    var attachment = message.getAttachments()[0];

    // Is the attachment a CSV file
    if (attachment.getContentType() === "text/csv") {
        var id = "1F_Z_00-ThdTRzlZ3nYd_pieJHkUjgye3pI_KJfydoP4";
        var name = "ALLDATA";
        var sheet = SpreadsheetApp.openById(id);
        var tab = sheet.getSheetByName(name);
      
        // Clear the content of the sheet
        tab.clearContents().clearFormats();
        tab.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
      
        var csvData = tab.Utilities.parseCsv(attachment.getDataAsString(), ",");
    }
}

使用上面的代码，我没有得到任何结果。我不确定为什么会这样。

With the above code, I didn't get any results. I'm not sure why this is happening.

我认为查询中的邮件ID不能正确调用该电子邮件，但是我找不到其他方法

I don't think that the mail ID in the query is correct to call that email, but I couldn't find any other way to do it.

推荐答案

我通过以下更改修改了代码，使其可以按预期工作：

I modified your code with these changes so it can work as intended:

1）您遇到错误，因为必须先声明csvData变量，然后才能使用它。另外，parseCsv（）函数来自Utilities类，您可以直接使用此类（您按如下方式调用它：tab.Utilities.parseCsv）[1]。另一个问题是您无法使用消息ID [2]搜索消息，因为您应该发出获取请求[3]。

1) You were having errors because the csvData variable had to be declared before you can use it. Also, the parseCsv() function is from the Utilities class and you can directly use this class (you were calling it like this: tab.Utilities.parseCsv) [1]. The other problem was that you can't search messages using the message id [2], for that you should make a get request [3].

2）我放了一个If来验证邮件是否包含附件。

2) I put an If to validate that the message has an attachment.

3）我更改了条件中的条件，以验证它是否是文件名中带有文件扩展名的csv文件，因为gmail API接收的csv文件的MIME类型为 application / octet-stream（我想这是因为您可以将其打开为

3) I changed the condition in your If to validate if it's a csv file with the file extension in its file name, because the gmail API takes a csv file with "application/octet-stream" as mime type (i guess this is because you can open it as a Google Sheets in gmail).

4）我修改了获取消息的方式，以获取最后一个线程的最后一条消息，之前它获取的是第一条消息

4) I modified how you were getting the message to get the last message of that last thread, previously it was getting the first message of each thread and not the last one.

5）在INFO表中设置日期，如果需要特定格式[1]，则可以检查formatDate函数。 / p>

5) Set the date in the INFO sheet, you can check the formatDate function if you want a specific format [1].

function importCSVFromGmail() {
  var threads = GmailApp.search("label:rpt-its-all-data");
  var message = threads[0].getMessages().pop();

  var attachment = message.getAttachments()[0];

  if (attachment != null) {
    var attachName = attachment.getName();

    // Is the attachment a CSV file
    if (attachName.substring(attachName.length-4) === ".csv") {
      var id = "1bvENjFYC48LSYDMPtI4FIOKIChiuIrg5O4WRziFeAhU";
      var name = "ALLDATA";
      var sheet = SpreadsheetApp.openById(id);
      var tab = sheet.getSheetByName(name);
      var tabInfo = sheet.getSheetByName("INFO");
      tabInfo.getRange("B1").setValue(new Date());

      // Clear the content of the sheet
      tab.clearContents().clearFormats();
      var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");
      tab.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
    }
  } 
}

[1] https://developers.google.com/apps-脚本/参考/实用程序/实用程序#formatDate（日期，字符串，字符串）

[2] > https://support.google.com/mail/answer/7190

[3] https://developers.google.com/gmail / api / v1 / reference / users / messages / get

这篇关于如何将数据从Gmail邮件附件复制到电子表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！