Recent Additions


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

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

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'),'&nbsp;');
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','&nbsp;','&nbsp;' );//empty line
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','Subject:',getGlobal('form1Subject'),'&nbsp;');
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','&nbsp;','&nbsp;' );//empty line
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','TimeStamp:',e.values[0]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','Location:',e.values[1]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','Name:',e.values[2]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[3]),RoG(e.values[3]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[4]),RoG(e.values[4]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[5]),RoG(e.values[5]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[6]),RoG(e.values[6]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',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'),'&nbsp;');
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','&nbsp;','&nbsp;' );//empty line
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','Subject:',getGlobal('form2Subject'),'&nbsp;');
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','&nbsp;','&nbsp;' );//empty line
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','TimeStamp:',e.values[1]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','Location:',e.values[0]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;','Name:',e.values[2]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[3]),RoG(e.values[3]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[5]),RoG(e.values[5]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',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>','&nbsp;','&nbsp;','&nbsp;' );//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>','&nbsp;','&nbsp;','&nbsp;' );//empty line
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>','&nbsp;','TimeStamp:',e.values[0]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>','&nbsp;','Location:',e.values[1]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>','&nbsp;','Name:',e.values[2]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[3]),RoG(e.values[3]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[4]),RoG(e.values[4]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[5]),RoG(e.values[5]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[6]),RoG(e.values[6]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',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>','&nbsp;','&nbsp;','&nbsp;' );//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>','&nbsp;','&nbsp;','&nbsp;' );//empty line
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>','&nbsp;','TimeStamp:',e.values[1]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>','&nbsp;','Location:',e.values[0]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td><strong>%s</strong></td></tr>','&nbsp;','Name:',e.values[2]);
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[3]),RoG(e.values[3]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[5]),RoG(e.values[5]));
        html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td></tr>','&nbsp;',RoGpf(e.values[6]),RoG(e.values[6]));
        html+='</table>';
        Logger.log(html);
        GmailApp.sendEmail(getGlobal('form2Email'), getGlobal('form2Subject'), '', {htmlBody:html});
      }
      break;
  }   
}