I have been playing around with this script for the last couple weeks. The goal of the script is to go through a reporting inbox, pull reporting data from email attachments, copy into a google spreadsheet, and then relabel the emails to remove them from the inbox to prevent accidental double copying reports.
The script functions in this order:
- Look for new emails in the Inbox with attachments
- Copy attachment data
- Paste into Spreadsheet in the next open row
- Relabel the email with "Report" instead of "Inbox" to move all reports into a reporting folder
I have successfully accomplished steps 1 - 3, but for the life of me, I can not get the relabeling to work. When I run debug in the Google Apps console, it doesn't come back with any errors. Pasted below is the excerpt from the script doing the relabeling:
for (var i = 0; i < myLabel.length; i++) {
labels = myLabel[i].getLabels();
for (var j = 0; j < labels.length; j++) {
labels[j].addLabel("test_2");
labels[j].removeLabel("Test");
}
}
Below is the full script I am running.
function getCSV() {
// Create variable that looks for Gmails in the main inbox
var myLabel = GmailApp.getUserLabelByName("test");
Logger.log("myLabel:",myLabel);
// Create variable that is filled with all threads within Inbox label
var threads = myLabel.getThreads();
Logger.log("threads:",threads);
// Retrieves all messages in the specified thread
var msgs = GmailApp.getMessagesForThreads(threads);
Logger.log("msgs:",msgs);
// Uses active sheet the script is implemented on
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("test");
// Grabs CSV data from attachments and pastes into next available row in Spreadsheet
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();
for (var j = 0; j < messages.length; j++) {
var attachments = messages[j].getAttachments();
Logger.log("attachments:",attachments);
var csvData = Utilities.parseCsv(attachments[j].getDataAsString(), ",");
Logger.log(csvData);
for (var k = 1; k < csvData.length; k++) {
var dataPaste = sheet.appendRow(csvData[k]);
Logger.dataPaste;
}
}
}
// Removes Inbox Label and Adds Report Label
for (var i = 0; i < myLabel.length; i++) {
labels = myLabel[i].getLabels();
for (var j = 0; j < labels.length; j++) {
labels[j].addLabel("test_2");
labels[j].removeLabel("Test");
}
}
}