Stop Wasting Time And Automate Google Sheets With Three Scripts

function sendEmail() {

 //setup function

 var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

 if (ActiveSheet.getName() == ‘Review Tracker‘) {

   var StartRow = 2;

   var RowRange = ActiveSheet.getLastRow() – StartRow + 1;

   var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,7);

   var AllValues = WholeRange.getValues();

   var message = “”;

   //iterate loop

   for (i in AllValues) {

     //set current row

     var CurrentRow = AllValues[i];

     if (CurrentRow[6] == “Ready for Review” && CurrentRow[7] != “sent“) {

       //define column to check if sent

       // column A = 1, B = 2, etc…

       //var EmailSent = CurrentRow[8];

       //if row has been sent, then continue to next iteration

       //if (EmailSent == “sent“) {

         //continue;}

       //set HTML template for information

       message +=

         “<p><b>Name: </b>” + CurrentRow[0] + “</p>” +

           “<p><b>Client: </b>” + CurrentRow[1] + “</p>” +

             “<p><b>Item to Review: </b>” + CurrentRow[2] + “</p>” +

               “<p><b>Link to Item: </b>” + CurrentRow[3] + “</p>” +

                 “<p><b>Deadline for Review: </b>” + CurrentRow[4] + “</p>” +

                   “<p><b>Notes: </b>” + CurrentRow[5] + “</p>” +

                      “<p><b>Status: </b>” + CurrentRow[6] + “</p>” + “</p><br><br>”;

       //set the row to look at

       var setRow = parseInt(i) + StartRow;

       //mark row as “sent

       // column A = 1, B = 2, etc…

       ActiveSheet.getRange(setRow, 8).setValue(“sent“);

     }//if review ready

   }//For loop close

   //define who to send emails to

   var SendTo = “example@email.com“;

   //set subject line

   var Subject = “New Deliverable to Review“;

   //send the actual email   if message is not empty

   if (message) {

     MailApp.sendEmail({

       to: SendTo,

       subject: Subject,

       htmlBody: message,

     });

   }//if message

 }//if sheetName Review

}//End Func

Let’s block ads! (Why?)

Seer Interactive

Add Comment