Btukfyl

// This script sends an e-mail to the rep when a pick up is complete, stamps the pick up data in the Report tab, and adds the job to the Check In tab



function pickUpEmail() {



  var ss = SpreadsheetApp.getActiveSpreadsheet(); // gets active spreadsheet

  var checkin = ss.getSheetByName("Check In"); //gets the Check In tab

  var app = ss.getSheetByName("App"); /// gets the App tab

  var report = ss.getSheetByName("Report"); //gets the Report tab

  var activatedSheetName = ss.getActiveSheet().getName();

  var activeCell = app.getActiveCell(); //gets the active cell

  var activeRow = activeCell.getRow(); //gets the row of the active cell

  var activeColumn = activeCell.getColumn(); //gets the column of the active cell

  var job = app.getRange(activeRow, 1).getValue(); //gets the job #

  var jobRaw = app.getRange(activeRow,49).getDisplayValue(); //gets the raw job # (no EWR- prefix)

  var duration = app.getRange(activeRow,50).getDisplayValue() //gets the duration

  var date = app.getRange(activeRow, 3).getValue(); //gets the pick up date

  var contact = app.getRange(activeRow, 5).getValue(); //gets the contact

  var address = app.getRange(activeRow, 6).getValue(); //gets the address

  var rep = app.getRange(activeRow, 52).getValue(); // gets the rep

  var arrived = app.getRange(activeRow, 13).getDisplayValue(); //gets the arrival time

  var departed = app.getRange(activeRow, 14).getDisplayValue(); //gets the departed time

  var status = app.getRange(activeRow, 45).getDisplayValue(); //gets the pick up status

  var type = app.getRange(activeRow,53).getDisplayValue(); //gets the job type

 

  // Condition 1: if sheet is "App"

  if (activatedSheetName == "App") {

 

   

//Reps

    

if (rep == "MM") {

var repname = "Mike";

var repEmailAddress = "mmolter@cratersandfreightersnynj.com";}

  

if (rep == "AG") {

var repname = "Ariel";

var repEmailAddress = "agalayda@cratersandfreightersnynj.com";}



if (rep == "JM") {

var repname = "Joe";

var repEmailAddress = "mmolter@cratersandfreightersnynj.com";}



if (rep == "CH" || rep =="CA") {

var repname = "Christina";

var repEmailAddress = "mmolter@cratersandfreightersnynj.com";}

    

if (rep == "CK") {

var repname = "Caroline";

var repEmailAddress = "ckopko@cratersandfreightersnynj.com";}    



if (rep == "TR") {

var repname = "Tom";

var repEmailAddress = "tom@cratersandfreightersnynj.com";}



if (rep == "RP") {

var repname = "Robert";

var repEmailAddress = "rpoirier@cratersandfreightersnynj.com";}

  

  

  

  

  if (type == "Pick up") {var emailText = "picked up!";}

   if (type == "Delivery") {var emailText = "delivered!";}

 

    // Condition 2: if active column is 14 (N)

  if (activeColumn == 14) {



 

  

  // Paste to Check ins

 

    // Condition 3: if the status is "Completed"

    

  if (status == "Completed"){

  

  

  var inputRow = 1

  checkin.insertRowAfter(inputRow);

  var jobPaste = checkin.getRange(2,2).setValue(job);

  var datePaste = checkin.getRange(2,4).setValue(date);

  var repPaste = checkin.getRange(2,3).setValue(rep);

      

    

  // Paste to Pick up Report 

  report.insertRowAfter(inputRow);

 report.getRange(2,1).setValue(date); // Date

 report.getRange(2,2).setValue(job); //Job Number

 report.getRange(2,3).setValue(arrived); //Arrived

 report.getRange(2,4).setValue(departed); //Departed

 report.getRange(2,5).setValue(duration); //Status

 report.getRange(2,6).setValue(type); //Type

    

// this whole section below take photos from the AppSheet folder, searches for the job # in the Shared Company Photos, and either makes a new folder if not existing, or copies them to it

  var par_fdr = DriveApp.getFolderById("0B6lqZvelcxkeaWNZMmhKd0JDbjQ"); // Destination folder "Shared Company Photos"

  var fdr_name = jobRaw;



try {

  var newFdr = par_fdr.getFoldersByName(fdr_name).next();

  newFdr.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);

}

catch(e) {

  var newFdr = par_fdr.createFolder(fdr_name);

  newFdr.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);

}

 

  var search1  = "title contains ";

  var search2 = "'" + jobRaw + "'";

  var searchFor= search1+search2;

  var fileIds=;

  var files = DriveApp.getFolderById("16OkPTUa98pYPrDgJDATzRKTbq5h8_PcX").searchFiles(searchFor); // Source folder "App_Images"

  var URL= ;

  var link= "https://drive.google.com/uc?export=download&id=";

 

  while (files.hasNext()) {

    var file = files.next();

      

var copyFiles = file.makeCopy(jobRaw+"_on site",newFdr);

var fileId = copyFiles.getId();// To get FileId of the file

    fileIds.push(link+fileId+"<P>");



  //  file.setTrashed(true);

}  

  

  

   

var sig = "<p>Thank you,<p><b>The Road Crew<br><font color='#351C75'>Craters & Freighters</font color></b><br>333 Cedar Ave.<br>Middlesex, NJ 08846<br>732-563-9200-Tel<br>732-563-2221-Fax<br>";

  

    

  var message = 

"<font face='tahoma'>Hi "+ repname+","+ 

"<P>Your job<b> " +job+"</b> has been "+emailText+

"<p>"+contact+

"<br>"+address+

"<p><b>Arrived: </b>" +arrived+

"<br><b>Departed: </b>" +departed+

"<p><b>Time on site: </b>" +duration+

"<div id='pics' style='display: none;'><b>Pictures:</b><p>"+fileIds+

"</div>"+sig;



  

var bcc1 = "tom@cratersandfreightersnynj.com,mmolter@cratersandfreightersnynj.com,rpoirier@cratersandfreightersnynj.com,kkull@cratersandfreightersnynj.com";

var subject = job;

subject += " "+emailText;

  MailApp.sendEmail(repEmailAddress, subject + "", message, {htmlBody: message, name: 'C&F Road Crew', bcc: bcc1});

    

    

  // end of conditions 1,2,3

  }}}

  

  }

{

  "timeZone": "America/New_York",

  "dependencies": {

    "enabledAdvancedServices": [{

      "userSymbol": "Drive",

      "serviceId": "drive",

      "version": "v2"

    }, {

      "userSymbol": "Calendar",

      "serviceId": "calendar",

      "version": "v3"

    }],

    "libraries": [{

      "userSymbol": "AddRow",

      "libraryId": "164YbqtkyuQe4ObDI-oxi8pY7pvoXxYhJq_J5i51pcxtu0QrdlhHdj11_",

      "version": "2"

    }]

  },

  "webapp": {

    "access": "ANYONE_ANONYMOUS",

    "executeAs": "USER_DEPLOYING"

  },

  "exceptionLogging": "STACKDRIVER",

  "executionApi": {

    "access": "ANYONE"

  }

}