从每月电子邮件(在Gmail中)将XLSX文件导入指定的Google表格 [英] Importing XLSX file from the monthly e-mail (in Gmail) to the designated Google Sheet

问题描述

我会定期从客户那里收到XLSX文件，并且希望自动执行从Gmail(自动标记)将文件导入Google表格的过程.到目前为止，我设法使它适用于CSV文件，但是XLSX文件似乎比较棘手.有人可以帮助调整我在CSV文件中使用的代码吗?

I receive an XLSX file from our client on regular basis, and I would like to automate the process of importing it from Gmail (it's automatically labeled) into Google Sheets. So far I managed to make it work for CSV files, but XLSX files seem to be trickier. Can someone help to adjust this code I have for CSV files?

function getCSV() 
{
  var thread = GmailApp.getUserLabelByName(‘Reconciliation’).getThreads(0,1);
  var messages = thread[0].getMessages();
  var len = messages.length;
  var message=messages[len-1] //get last message
  var attachments = message.getAttachments(); // Get attachment of first message

  var csv =  attachments[0].getDataAsString();
  var data = Utilities.parseCsv(csv);

  var sheet = SpreadsheetApp.openById("some id").getSheetByName(‘Data’);
  sheet.clearContents();
  var range = sheet.getRange(1, 1, data.length, data[0].length);
  range.setValues(data);
}

推荐答案

• 您想将电子邮件附带的xlsx文件中的数据放入现有的电子表格中.

如果我的理解是正确的，那么该修改如何?请认为这只是几个答案之一.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

使用此脚本时，请在Advanced Google Services和API控制台上启用Drive API.您可以在此处查看.

When you use this script, please enable Drive API at Advanced Google Services and API console. You can see about this at here.

1. 检索xlsx文件的斑点.
2. 将xlsx格式转换为Google Spreadsheet.
3. 从转换后的电子表格中检索值.
4. 删除转换后的文件.
5. 将值放入现有电子表格的Data表中.
1. Retrieve a blob of xlsx file.
2. Convert xlsx format to Google Spreadsheet.
3. Retrieve values from the converted Spreadsheet.
4. Remove the converted file.
5. Put the values to the sheet of Data in the existing Spreadsheet.

修改后的脚本:

请进行如下修改.

Modified script:

Please modify as follows.

var csv =  attachments[0].getDataAsString();
var data = Utilities.parseCsv(csv);

到:

var xlsxBlob = attachments[0]; // Is supposes that attachments[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.

注意:

• 在此修改中，它假设以下几点.如果您的情况与以下几点不同，请进行修改.

  Note:

  • In this modification, it supposes the following points. If your situation is different from the following points, please modify it.
    1. attachments[0]是xlsx文件的blob.
    2. 关于xlsx文件，要放入的数据位于第一个标签中.
    1. attachments[0] is the blob of xlsx file.
    2. About the xlsx file, the data you want to put is in a 1st tab.

  • 文件:插入Drive API v2

  如果我误解了你的问题，但是这没有用，我表示歉意.

  If I misunderstood your question and this didn't work, I apologzize.

  这篇关于从每月电子邮件(在Gmail中)将XLSX文件导入指定的Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！