从电子邮件中获取 xlsx 文件并复制文件

我从这里的一个问题中看到了这个脚本,但不能让它工作。它旨在从我的 Gmail 中获取 Excel 文件并将其复制到 Google 表格中。谷歌表格文件在这里: https://docs.google.com/spreadsheets/d/1rED5zphZKp7JIK9qaTpHLKBSMsSiLAvhBAXc6Tn0zqI/edit#gid=301517543

脚本:

function getRawData() {
  var threads = GmailApp.search('subject:myRentokil Report Delivery: Canpac | Pest Activity');
  var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];

  var xlsxBlob = attachment[0]; // Is supposes that attachment[0] is the blob of xlsx file.
  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // There is the data in 1st tab.
  var data = sheet.getDataRange().getValues();
  Drive.Files.remove(convertedSpreadsheetId); // Remove the converted file.

  var sheet = SpreadsheetApp.openById("1rED5zphZKp7JIK9qaTpHLKBSMsSiLAvhBAXc6Tn0zqI").getSheetByName("RawData");
  sheet.clearContents();
  var range = sheet.getRange(1, 1, data.length, data[0].length);range.setValues(data);

 }
stack overflow Fetch xlsx file from email and copy file
原文答案
author avatar

接受的答案

修改后的脚本

function getRawData() {
    // This gets the threads
    var threads = GmailApp.search('subject:excel attachment');
    var message = threads[0].getMessages()[0]; // returns the first message from the first thread
    var attachment = message.getAttachments()[0]; // returns the first attachment
    var xlsxBlob = attachment; // This is a blob, yes.

    var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
    var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];
    var data = sheet.getDataRange().getValues(); // gets values from first tab

    // You were overwriting some variables here, so changed to dataSheet and dataRange
    var dataSheet = SpreadsheetApp.openById("1rED5zphZKp7JIK9qaTpHLKBSMsSiLAvhBAXc6Tn0zqI").getSheetByName("RawData");
    var dataRange = dataSheet.getRange(1, 1, data.length, data[0].length);
    dataRange.setValues(data);

    // Moved this line to the end because you were deleting it before using the data inside it.
    // Changed to trash instead of remove.
    Drive.Files.trash(convertedSpreadsheetId); // Remove the converted file.
 }
  • 我评论了您的脚本以使步骤更清晰。

  • 有一些变量被初始化了两次,所以只是更改了这些名称。

  • 错误的原因是 remove 不作为 Drive API v2 中的方法存在。

    enter image description here

    https://developers.google.com/drive/api/v2/reference/files/trash

    remove 实际上似乎确实有效并删除了文件,但它总是返回错误。 trash 按预期工作。如果你想跳过垃圾箱,你可以 delete 代替。


答案: