Recent Additions
listLabelsIncludingYears()
listLabelsIncludingYears()
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:
- Gmail API
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]]);
}
}
}
}
Copy If Date is Less Than Today
Copy If Date is Less Than Today
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++;
}
}
}
Multiple Forms on a Spreadsheet Sending different emails for each form
Multiple Forms on a Spreadsheet Sending different emails for each form
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;
}
}