Goals of the problem:
- Retrieve the message from Gmail using the email address.
- Retrieve the CSV files from the attachment files and put them on a sheet in Google Spreadsheet. Remove 1st 2 rows from the CSV data. (Remove the past data on Google Sheets and update it with the new CSV data whenever received from the specific email address).
- Achieve this using Google Apps Script.
Already tried: Automate a CSV file received over Gmail from a specific email id to a specific Google Sheet
But nothing is solving the above 3 problems in specific.
function myFunction() {
const messageId = "###"; // Please set the message ID of Gmail.
const sheetName = "Sheet1"; // Please set the sheet name you want to put the values.
const delimiter = ","; // If your CSV data uses the specific delimiter, please set this.
const skipRows = 2; // 2 is from your question.
// 1. Retrieve message.
const message = GmailApp.getMessageById(messageId);
// 2. Retrieve attachment files.
const attachments = message.getAttachments();
if (attachments.length == 0) {
console.log("No attachment files.");
return;
}
// 3. Create an array for putting to Spreadsheet from the CSV data of attachment files.
const values = attachments.reduce((ar, e) => {
if (e.getContentType() == MimeType.CSV || e.getName().includes(".csv")) {
ar = [...ar, ...Utilities.parseCsv(e.getDataAsString(), delimiter).splice(skipRows)];
}
return ar;
}, []);
if (values.length == 0) {
console.log("No values.");
return;
}
// 4. Put the values to Spreadsheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); // and, you can also use. const sheet = SpreadsheetApp.openById("###spreadsheetId###").getSheetByName(sheetName);
sheet.getRange(sheet.getLastRow() 1, 1, values.length, values[0].length).setValues(values);
}
Tried the above code but it shows an error Exception: Invalid argument: at myFunction(Code:8:28)
FYI, I also want to use Sheet ID instead of Sheet Name
CodePudding user response：
Try (put the spreadsheet ID, the subject -if you have multiple words please specify as in the example hereafter- and the email of the sender hereafter):
const getGmailAttachment = () => {
const ssID = '############'
const searchQuery = 'from:######@gmail.com in:inbox has:attachment subject:##### subject:###';
const threads = GmailApp.search(searchQuery, 0, 1);
threads.forEach(thread => {
const message = thread.getMessages()[Number(thread.getMessageCount() - 1)];
const attachments = message.getAttachments();
attachments.forEach((attachment, i) => {
if (i == 0) {
console.log(attachment.getName())
const sheet = SpreadsheetApp.openById(ssID).getSheets()[0];
sheet.getDataRange().clearContent()
const csvData = Utilities.parseCsv(attachment.getDataAsString()).splice(2); // except 2 first rows
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
});
});
};