Update Spreadsheet via Email Attachments

Question

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"]*

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

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();

//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.

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

 }

After getting the CSV file from the attachment, you can programmatically import data from a csv file in your Drive into an existing Google Sheet using Google Apps Script, replacing/appending data as needed. To help you with the implementation, please check the data importation in this SO post - How to automatically import data from uploaded CSV or XLS file into Google Sheets. Hope it helps! — Teyam

Amit Agarwal Amit Agarwal · Accepted Answer · 2016-09-21T18:34:20

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
  }

Update Spreadsheet via Email Attachments

1 Answers