从 gmail 检索 csv 附件文件并将数据放入 google 电子表格 [英] Retrieve csv attachment file from gmail and place the data in a google spreadsheet

问题描述

I currently receive emails from a specific source with multiple attachments specifically csv attachments. What I need is to retrieve the data from the csv attachment. I've looked into creating a Google App Script which I hear can get the job done based on my research but perhaps there is a better way if so please advice.

I wrote a pseudo code of what I would like the script to do:

1. Determine who sent the email. If its the source that I need then follow to step 2.
2. Look at the subject of the email if its the subject that I need then proceed to step 3.
3. If step 1 and 2 are good then the next step is to retrieve the data from one of the csv attachments(based on the name) this is because there could be more than one attachment in the email.
4. Open the attachment copy the data and paste it in either a google spreadsheet or excel spreadsheet which is created dynamically OR save the attachment to my google drive in a specific folder but either one could work. The trick here is to loop through all the emails in my inbox in past month and achieve the above task.

Thanks everyone for your help and I hope I was clear in my specifications.

Links I found to be helpful to me but not quite exactly what I need.

Create time-based Gmail filters with Google Apps Script

Trigger Google Apps Script by email

解决方案

After researching and working along with the google apps script documentation I was able achieve my goal for my task at hand. Please see my code below with comments and hopefully this can help. Thanks,

function RetrieveAttachment() {
  // variables being used i, j, k, n, m, a, d, x
  var threads = GmailApp.search('*SubjectName*') //search gmail with the given query(partial name using * as a wildcard to find anything in the current subject name).
  var msgs = GmailApp.getMessagesForThreads(threads); //retrieve all messages in the specified threads. 
  //var sheet = SpreadsheetApp.create('test_filename', 2, 8); //creates a new spreadsheet in case I need to create it on a separate file.
  //you can get the id from your own google spreadsheet in your browser bar.
  var sheet = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx').getSheetByName('your sheet name');
  sheet.clearContents(); //clears all the data in the specified tab, the code below will recreate the dataset once again.

  for (var i = 0; i < msgs.length; i++) {
    for (var j = 0; j < msgs[i].length; j++) {
      var emailDate = msgs[i][j].getDate();
      var attachments = msgs[i][j].getAttachments();
      for (var k = 0; k < attachments.length; k++) {

        /*search for the attachment by name, stringLen returns the start position number of the word 'filename' ignoring any previous characters, counting starts at 0.
        e.g. "test_filename", will output the number 6 "test_" will ends at 5 and 6 will start at "f" for filename. Than we use substring to get the actual name out of
        attachment name then we use the stringLen variable as a starting position and also as an end position plus the number of characters in word I'm searching for
        to get the attachment name, 8 is used because this is how many letters are in the string. Finally we create the stringValue variable and compare to see which
        attachments meet the criteria that we are looking for and return only that attachment.*/
        var attachmentName = attachments[k].getName();
        var stringLen = attachmentName.search('filename');
        var stringValue = attachmentName.substring(stringLen,stringLen + 8);

        if (stringValue == 'filename') {
          var attachmentData = attachments[k].getDataAsString();
          var attachmentClean = attachmentData.split('"').join(',');
          var attachmentCleanA = attachmentClean.split(',');

          /*input headings into the spreadsheet. This is depending on how many columns or fields the attachment file has. The numbers after the "attachmentCleanA" is the
          position number of the field you want is located i.e [1][4][7][10]. The reason it skips the numbers is because the getDataAsString() method returned an array with
          multiple separations that had to be parsed by quotations. So [2][3] had an empty string value.*/

          sheet.getRange('A1').setValue(attachmentCleanA[1]); //Field One
          sheet.getRange('B1').setValue(attachmentCleanA[4]); //Field Two
          sheet.getRange('C1').setValue(attachmentCleanA[7]); //Field Three
          sheet.getRange('D1').setValue(attachmentCleanA[10]); //Field Four
          //Extra fields if you want to add.
          sheet.getRange('E1').setValue('Email Date'); //Email Date
          sheet.getRange('F1').setValue('Email Month'); //Email Month
          sheet.getRange('G1').setValue('Email Year'); //Email Year
          sheet.getRange('H1').setValue('Source Name'); //Attachment Name

          var n = LastRow(sheet); //calls the LastRow function to get the next empty cell.
          var m = attachmentCleanA.length + n;
          /*attachmentCleanA.length alone is not useful as a limit in the loop because the n variable ends up being bigger than the actual attachmentCleanA.length.
          To fix this I added the "attachmentCleanA" + "n" variable so that the n variable will always be less than the attachmentCleanA.length expression.*/

          var range = sheet.getRange('A1:H30000'); //this has to match the number of columns in the above sheet.getRange().setValue methods.
          var d = 11;

          /*now we loop through each string in the array and place it in each individual row and column. The first string position you want may vary depending
          on the file you have. The file I have has the first item and is positioned in the 12th position of the array. The reason variable d shows 11 is because 
          it will be added before the actual extraction of the value "d++" */
          RowLoop:
          for (var x = n; x < m; x++) {
            for (var a = 1; a < 5; a++) {
              var cell = range.getCell(x, a);
              d++;
              //the reason of the if function is so that when I run into an empty string in the array I can simply ignore it and continue to the next string.
              if (attachmentCleanA[d] !== "" && attachmentCleanA[d] !== undefined) {
                cell.setValue(attachmentCleanA[d]);
              }
              else if (attachmentCleanA[d] == "") {
              /*the a-- is used so that when I find and empty string in the array I don't want to skip to the next column but continue to stay there until I find 
              a none empty string.*/
                a--;
              }
            }
            /*email date - the reason of the if function is because in my situation it was producing more values at the end of the loop. So I made it stop if in 
            column A doesn't have a value*/
            var setDate = range.getCell(x, 5);
            if (range.getCell(x, 1).getValue() !== "") {
              setDate.setValue(emailDate);
            }
            else if (range.getCell(x, 1).getValue() == "") {
              break RowLoop;
            }
            //source name
            var attachmentLen = attachmentName.search('filename');
            var attachmentValue = attachmentName.substring(0, attachmentLen-1);
            var setAttachmentName = range.getCell(x, 8);
            setAttachmentName.setValue(attachmentValue);

            //email year
            var setYear = range.getCell(x, 7);
            setYear.setValue(emailDate.getFullYear());

            //email month
            var setMonth = range.getCell(x, 6);
            var monthName = MonthFunc(emailDate.getMonth());
            setMonth.setValue(monthName);
          }
        }
      }
    }
  }
}

function LastRow(sheetName) {
  //retrieve the last row position after each attachment data file has been put into the spreadsheet
  var column = sheetName.getRange('A:A');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] !== "" ) {
    ct++;
  }
  return (ct)+1; // add 1 to get the row which is empty
}

function MonthFunc(inputMonth) {
  //this function returns the short name of the month.
  var monthNumber = inputMonth
  switch (monthNumber) {
    case 0:
      return "Jan";
      break;
    case 1:
      return "Feb";
      break;
    case 2:
      return "Mar";
      break;
    case 3:
      return "Apr";
      break;
    case 4:
      return "May";
      break;
    case 5:
      return "Jun";
      break;
    case 6:
      return "Jul";
      break;
    case 7:
      return "Aug";
      break;
    case 8:
      return "Sep";
      break;
    case 9:
      return "Oct";
      break;
    case 10:
      return "Nov";
      break;
    case 11:
      return "Dec";
      break;
  }
}

这篇关于从 gmail 检索 csv 附件文件并将数据放入 google 电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！