通过电子邮件附件更新电子表格 [英] Update Spreadsheet via Email Attachments

问题描述

我已经建立了一个流程，可以将CSV格式的电子邮件从我们的ERP发送到GA帐户，并在该帐户上粘贴标签.我想解析附件并更新电子表格以用作另一个GA项目的数据源.尝试做任何事情时，我总是会牢牢抓住内在循环，因为那可能是我的大脑处于连接状态.基本上，我在电子表格中设置了标签，并为附件的CSV文件设置了一组静态名称，如下所示:

I have set up a process to email CSVs from our ERP to a GA account and apply a label to the. I would like to parse the attachments and update a spreadsheet to be used as a data source for another GA project. I am always getting stick on the inner loops when trying to do anything as maybe that is the was my brain is wired. Basically I have set tabs in a spreadsheet and a set of static names for the attached CSV files like so:

 *//Static array values will never change
  var sheetNames = ["Wage Codes", "Phase Codes", "Cost Types", "Department"]
  var csvName = ["Wage_Codes.csv", "Phase_Codes.csv", "Cost_Types.csv", "Department_Codes.csv"]*

我想遍历每个数组，从csvName导入数据并将单元格值设置为sheetNames

I would like to loop through each array importing the data from csvName and setting the cell values into the sheetNames

我可以从附加的CSV文件中获取数组，并使用以下命令将它们读入Logger:

I can get the arrays from the attached CSV files and read them into Logger using this:

var label = "Timesheet Data"

var thread = GmailApp.getUserLabelByName(label).getThreads();

var thread = GmailApp.getUserLabelByName(label).getThreads();

//静态数组值永远不会改变 var sheetNames = [工资代码"，阶段代码"，成本类型"，部门"] var csvName = ["Wage_Codes.csv"，"Phase_Codes.csv"，"Cost_Types.csv"，"Department_Codes.csv"]

//Static array values will never change var sheetNames = ["Wage Codes", "Phase Codes", "Cost Types", "Department"] var csvName = ["Wage_Codes.csv", "Phase_Codes.csv", "Cost_Types.csv", "Department_Codes.csv"]

 //strip CSV attachemnts from emails and put in GDrive
  var count = thread[0].getMessageCount();
  for (var i = 0 ; i < count; i++) {
  var attachments = thread[0].getMessages()[i].getAttachments()[0];
    var csvData = Utilities.parseCsv(attachments.getDataAsString(), ",");

   Logger.log(csvData);

我可以使用以下方法逐个下载:

And I can do this on an individually downloaded basis by using this:

    function importDeptCodes() {
  var ss = SpreadsheetApp.openById('sheetID').getSheetByName('Cost Types');
  var file = DriveApp.getFilesByName('Cost_Types.csv').next();
  var arry = Utilities.parseCsv(file.getBlob().getDataAsString());
  ss.getRange(1, 1, arry.length, arry[0].length).setValues(arry);
  Logger.log(arry)
}

在第一个代码示例中将其放入内部循环是我的大脑大声放屁的地方.

Putting it into an inner loop in the first code sample is where my brain is farting loudly.

使用以下代码，它在yhe循环达到一个级别时将全部导入4次，并且仅以现在的方式将所有内容导入到部门表中

USing the following code it is importing everything 4 time when yhe j loop is up one level and it only imports to the EVERYTHING to the department sheet the way it is now

    function importCodes() {
   var sheetNames = ["Wage Codes", "Phase Codes", "Cost Types", "Department"]
   var csvName = ["Wage_Codes.csv", "Phase_Codes.csv", "Cost_Types.csv", "Department_Codes.csv"]
   var ss = SpreadsheetApp.openById('sheetID');

  for (var i = 0; i < sheetNames.length; i++) {
    var ss = SpreadsheetApp.openById('sheetID');
    var sheet = ss.getSheetByName(sheetNames.valueOf()[i]);

       Logger.log(sheet.getName())
}
   for (var j=0; j < csvName.length; j++) { 
     var file = DriveApp.getFilesByName(csvName.valueOf()[j]).next();
     var arry = Utilities.parseCsv(file.getBlob().getDataAsString());
     sheet.getRange(1, 1, arry.length, arry[0].length).setValues(arry);
  Logger.log(sheet)
  }

 }

推荐答案

您可以从附件中获取CSV文件名，找到相应的工作表并导入数据.

You can get the CSV file name from the attachment, find the corresponding sheet and import the data.

  var attachment = thread[0].getMessages()[i].getAttachments()[0];
  var fileIndex = csvName.indexOf(attachment.getName()); 
  if (fileindex !== -1) {
    var sheet = ss.getSheetByName(sheetNames[fileIndex]);
    // write the import code here
  }

这篇关于通过电子邮件附件更新电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！