Appending to last column

[John Hummel]

I am trying to be my script to work properly. I want someone to fill out a form and then the script will take some of that data and attach it to a template document. Then I want to append the url for that document to the next column on the Form Submission tab and then send the document link to the person who completed the form. Right now I have most of these steps completed but I cannot not figure out how to append to the next column of the last row and then how to stop the script from running from the beginning each time. I need it to look for the last row and to see if the column that would have the appended url in it is blank and then start the process.  This is what I have so far. The code for storing the URL saves it to another tab right now. 

// create a menu

function onOpen() {

 var menuEntries = [ {name: "Evidence of Completion", functionName: "CreateSum"}];

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 ss.addMenu("Evidence of Completion", menuEntries);

}

function CreateSum() {

 

 // specify doc template and get values from spread

 var sleepINT = 1500

 var templateid = "1fpQdaFNTZzTMm9xc-fJBYSixLvpRi4Aoh7B87XI61k0"; // template file id

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 ss.toast("ENGINE INITIALIZING & Feeding the unicorns");

 Utilities.sleep(sleepINT);

 var sheet = ss.getActiveSheet();

  var lastRow = sheet.getLastRow();

   var lastCol = sheet.getLastColumn();

 var data = sheet.getRange(1, 1, lastRow, lastCol).getValues();; // starting with row 2 and column 1 as our upper-left most column,

                                                   // get values from cells from 1 row down, and 15 columns along - hence (2,1,1,15)

 //sheet.getRange("F7").setValue('=IMAGE("https://s-media-cache-ak0.pinimg.com/564x/58/5d/8f/585d8f802867c25df8f1ecc0cf7cadc8.jpg",1)');                                                

  ss.toast("10%: data captured");

  Utilities.sleep(sleepINT);

 

 

 

 // Make a copy of the invoice template, then Fill up it up with the data from the spreadsheet.

 //NOTE: body.replace method does not have to be in any specific order.

 

 for (var i in data) {

   var row = data[i];

 

    var docid = DriveApp.getFileById(templateid).makeCopy().getId();

   var doc = SlidesApp.openById(docid);

   

   var slide = doc.getSlides()[0];

   var emailAddress = row[13];

 

   slide.replaceAllText("<<Name of Participant>>", row[1]);

   slide.replaceAllText("<<Date of Activity>>", row[2]);

   slide.replaceAllText("<<Title of Professional Development>>", row[3]);

   slide.replaceAllText("<<IEIN>>", row[12]);

 

    doc.saveAndClose();

   

   ss.toast("30%: template data replaced");

   Utilities.sleep(sleepINT);

   

   //copy the modified template to the specified folder, then delete the first copy we made (to modify it)

    var file = DriveApp.getFileById(doc.getId());

   var newfolder = DriveApp.getFolderById("1DrpjK899mttj2EjAyR_1y6JGBa91ebeD");

   var oldfolder = DriveApp.getFolderById("1BlOqa__lzpZDBu8e8U-bnNERrleAysLg");

   newfolder.addFile(file);

   oldfolder.removeFile(file);

   

   ss.toast("40%: Summary has been put in correct folder");

   Utilities.sleep(sleepINT);

   

   //customize the title for the summary

 

    var range = sheet.getRange(1, 1, lastRow, lastCol).getValues();

   var usernamefordoctitle = sheet.getRange("N2").getValue();

   var name = doc.getName();

   var locName = row[3];

   doc.setName('Evidence of Completion for ' + usernamefordoctitle);

   ss.toast("50%: named new summary");

   Utilities.sleep(sleepINT);

    //create and organize pdf version

   var pdffolder = DriveApp.getFolderById("1u9fHPdcXsnUq1dqQhDmVr4N0gJv4Gf57");

   var pdfFILE = DriveApp.getFileById(doc.getId()).getAs('application/pdf');

   pdfFILE.setName(doc.getName() + ".pdf");

   var theFolder = pdffolder;

   var theFile = DriveApp.createFile(pdfFILE);

   theFolder.addFile(theFile);

   ss.toast("60%: PDF generated");

   Utilities.sleep(sleepINT);

   

   var email_status = sheet.getRange("N2").getValue();

   

   if (email_status !== "" ) {

       //send a pdf copy to customer

        var URL = theFile.getUrl();

       var pdfEMAIL = DriveApp.getFileById(doc.getId()).getAs('application/pdf').getBytes();

       var message = "Hi " + usernamefordoctitle + "!, please kindly find your Evidence of Completion.\nMany Thanks!\nMe \n"  + URL;

       var emailAdd = usernamefordoctitle;

       var emailTo = emailAdd; // add customer email here

       var subject = "Evidence of Completion for " + usernamefordoctitle  ;

   

        //var attach = {fileName:"Evidence of Completion " + usernamefordoctitle + '.pdf',content:pdfEMAIL, mimeType:'application/pdf'};

     MailApp.sendEmail(emailTo, subject, message,);

       ss.toast("70%: emailed customer");

       Utilities.sleep(sleepINT);

       

       }

       

   else {

       

       ss.toast("No email sent");

       

       }

     }

       

 

  //Add values to worksheet

 var URL = theFile.getUrl();

 var tss = SpreadsheetApp.openById('1kgV4jR47v0SbVm0AUcC4GvuDDEQVtlmQZ3V_jeFgD8c');

 var ts = tss.getSheetByName('Evidence Sheets');

var lastrange = ts.getRange(1,1,ts.getLastRow,ts.getLastColumn);

 last.appendRow([theFile,URL]);

 ss.toast("80%: updated worksheet")

 Utilities.sleep(sleepINT);

 ss.toast("90%: feeding the unicorns some more")

 Utilities.sleep(sleepINT);

 ss.toast("100%: high-fiving the neighbor")

 Utilities.sleep(sleepINT);

 

}