从 gmail 检索 csv 附件文件并将数据放入 google 电子表格
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:
- Determine who sent the email. If its the source that I need then follow to step 2.
- Look at the subject of the email if its the subject that I need then proceed to step 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.
- 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;
}
}
相关文章