This function goes into all of your email labels and produces a dataset on the active sheet that counts the number of message with each label for every month. I'm not sure what the person that requested wanted to do with it but here it is.
Requirements:
function listLabelsIncludingYears() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
var response = Gmail.Users.Labels.list('me');
var msA=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
var miA=['J','F','M','A','M','J','J','A','S','O','N','D']
var mA=[0,0,0,0,0,0,0,0,0,0,0,0];
var yObj={yA:[]};
if (response.labels.length) {
sh.clearContents();
sh.appendRow(['Label Name','Year','Ms'])
sh.appendRow(['','',miA.join(',')])
for (var i = 0; i < response.labels.length; i++) {
var label=response.labels[i];
var label_messages = Gmail.Users.Messages.list('me', {'labelIds': [label.id]});
var msgs=label_messages.messages;
if(msgs) {
var lA=[0,0,0,0,0,0,0,0,0,0,0,0];
for(var j=0;j<msgs.length;j++) {
var m=GmailApp.getMessageById(msgs[j].id).getDate().getMonth();
var y=GmailApp.getMessageById(msgs[j].id).getDate().getFullYear();
if(yObj.hasOwnProperty(y)) {
yObj[y][m]+=1;
}else{
yObj[y]=[0,0,0,0,0,0,0,0,0,0,0,0,0];
yObj.yA.push(y);
yObj[y][m]+=1;
}
lA[m]+=1;
}
sh.appendRow([label.name,y,lA.join(',')]);
}
}
SpreadsheetApp.flush();
sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn()).sort({column:2,ascending:true});
sh.getRange(2,3,sh.getLastRow()-1,1).splitTextToColumns(',');
sh.autoResizeColumns(1,sh.getLastColumn())
SpreadsheetApp.flush();
sh.appendRow(['***********************']);
sh.appendRow(['***********************']);
sh.appendRow(['Year','Month','Total Count']);
yObj.yA.sort();
for(var k=0;k<yObj.yA.length;k++) {
for(var i=0;i<12;i++) {
sh.appendRow([yObj.yA[k],msA[i],yObj[y][i]]);
}
}
}
}
This function moves copies rows from source sheet to destination sheet and simultaneously deleting the source row from the source sheet.
function copyIfLessThan() {
var Sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var Sheet2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var Range=Sheet.getRange(4,1,Sheet.getLastRow(),1);
var Values=Range.getValues();
var dt=new Date();
var dv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
var d=0;
for(var i=0;i<Values.length;i++) {
if(new Date(Values[i][0]).valueOf()<dv){
var RangetoCopy=Sheet.getRange(i-d+4,1,1,27);
var DestRange=Sheet2.getRange(Sheet2.getLastRow()+1,1,1,27);
RangetoCopy.copyTo(DestRange);
Sheet.deleteRows(i-d+4);//removing rows not elements of Values Array
d++;
}
}
}
function coopermenu() {
SpreadsheetApp.getUi().createMenu('Cooper Menu')
.addItem('Create Trigger', 'createFormSubmitTrigger')
.addToUi();
}
function createFormSubmitTrigger(funcname) {
var funcname=funcname||'form1Submit';
if(!isTrigger(funcname)) {
ScriptApp.newTrigger(funcname).forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
}
}
function form1Submit(e) {
Logger.log(JSON.stringify(e));
Logger.log('Sheet: %s',e.range.getSheet().getName());
var sheetname=e.range.getSheet().getName();
switch(sheetname) {
case 'Form Responses 1':
if(e.values && e.values[1] && e.values[2]) {
var html='<table>';
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','To:',getGlobal('form1Email'),' ');
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','Subject:',getGlobal('form1Subject'),' ');
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ','TimeStamp:',e.values[0]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ','Location:',e.values[1]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ','Name:',e.values[2]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[3]),RoG(e.values[3]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[4]),RoG(e.values[4]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[5]),RoG(e.values[5]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[6]),RoG(e.values[6]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[7]),RoG(e.values[7]));
html+='</table>';
Logger.log(html);
GmailApp.sendEmail(getGlobal('form1Email'), getGlobal('form1Subject'), '', {htmlBody:html});
}
break;
case 'Form Responses 2':
if(e.values && e.values[1] && e.values[2]) {
var html='<table>';
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','To:',getGlobal('form2Email'),' ');
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','Subject:',getGlobal('form2Subject'),' ');
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ','TimeStamp:',e.values[1]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ','Location:',e.values[0]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ','Name:',e.values[2]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[3]),RoG(e.values[3]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[5]),RoG(e.values[5]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[6]),RoG(e.values[6]));
html+='</table>';
Logger.log(html);
GmailApp.sendEmail(getGlobal('form2Email'), getGlobal('form2Subject'), '', {htmlBody:html});
}
break;
}
}
function RoG(s) {
if(s) {
if(s=='Pass') {
return '<span style="color:#00ff00;font-weight:bold">Pass</span';
}else if(s=='Fail') {
return '<span style="color:#ff0000;font-weight:bold">Fail</span>';
}
}
return s;
}
function RoGpf(s) {
if(s) {
if(s=='Pass') {
return '<span style="color:#00ff00;font-weight:bold">Pass/Fail</span';
}else if(s=='Fail') {
return '<span style="color:#ff0000;font-weight:bold">Pass/Fail</span>';
}
}
return s;
}
A slightly different version
function form1Submit(e) {
Logger.log(JSON.stringify(e));
Logger.log('Sheet: %s',e.range.getSheet().getName());
var sheetname=e.range.getSheet().getName();
switch(sheetname) {
case 'Form Responses 1':
if(e.values && e.values[1] && e.values[2]) {
var html='<table>';
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','To:',getGlobal('form1Email'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','Subject:',getGlobal('form1Subject'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','TimeStamp:',e.values[0]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Location:',e.values[1]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Name:',e.values[2]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[3]),RoG(e.values[3]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[4]),RoG(e.values[4]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[5]),RoG(e.values[5]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[6]),RoG(e.values[6]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[7]),RoG(e.values[7]));
html+='</table>';
Logger.log(html);
GmailApp.sendEmail(getGlobal('form1Email'), getGlobal('form1Subject'), '', {htmlBody:html});
}
break;
case 'Form Responses 2':
if(e.values && e.values[1] && e.values[2]) {
var html='<table>';
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','To:',getGlobal('form2Email'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td colspan="2">%s</td></tr>','Subject:',getGlobal('form2Subject'));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',' ',' ' );//empty line
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','TimeStamp:',e.values[1]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Location:',e.values[0]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>',' ','Name:',e.values[2]);
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[3]),RoG(e.values[3]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[5]),RoG(e.values[5]));
html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>',' ',RoGpf(e.values[6]),RoG(e.values[6]));
html+='</table>';
Logger.log(html);
GmailApp.sendEmail(getGlobal('form2Email'), getGlobal('form2Subject'), '', {htmlBody:html});
}
break;
}
}