Day: May 22, 2015

function MailMerge4() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
 SalarySheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Salary")), 
 TemplateID='1bjpQnJikYMGYNaJQhetpZpkHyjI7iqBqDufprzMSo4k', 
 header = "A1:G1",
 data="A2:G6", 
 dataRows = SalarySheet.getRange(data).getValues(),
 headerRow = SalarySheet.getRange(header).getValues(),
 numColumns = SalarySheet.getRange(header).getNumColumns(),
 
 emailaddress= "",
 subject = "",
 docUrl = ""; 

 for (var i=0 ; i < dataRows.length ; i++) {
 emailText="";
 subject = "[ลับ] กองคลัง : แจ้งการโอนเงินให้คุณ " + dataRows[i][0] + " " + dataRows[i][1];
 var id =DriveApp.getFileById(TemplateID).makeCopy(subject).getId(),
 doc=DocumentApp.openById(id),
 docBody=doc.getBody();
 for (var j = 0 ; j < numColumns -1 ; j++) { 
 docBody.replaceText('{' + headerRow[0][j] + '}' , dataRows[i][j]); 
 } 
 
 emailaddress=dataRows[i][2];
 
 doc.addViewer( emailaddress);
 docUrl=doc.getUrl();
 doc.saveAndClose();
 
 var attachment = DocumentApp.openById(id);
 MailApp.sendEmail( emailaddress, 
 subject ,
 "เปิดอ่านเอกสารลับได้ที่ \n" + docUrl
 ); 
 try { 

 dataRows[i][numColumns-1] = new Date(); 
 } catch (e) {
 
 dataRows[i][numColumns-1] = e.message;
 } 
 }
 
 SalarySheet.getRange(data).setValues(dataRows);
}

function MailMerge5() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
 SalarySheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Salary")), 
 TemplateID='1WzzAwF5cDtQD0kcrLKRdP0ZO6-6MCqc_VXRUxzgq_gI', 
 header = "A1:H1",
 data="A2:H6", 
 dataRows = SalarySheet.getRange(data).getValues(),
 headerRow = SalarySheet.getRange(header).getValues(),
 numColumns = SalarySheet.getRange(header).getNumColumns(),
 
 emailaddress= "",
 subject = "",
 docUrl = ""; 

 for (var i=0 ; i < dataRows.length ; i++) {
 emailText="";
 subject = "[ลับสุดยอดดดดด] กองคลัง : แจ้งเอกสารลับให้คุณ " + dataRows[i][0] + " " + dataRows[i][1];
 var id =DriveApp.getFileById(TemplateID).makeCopy(subject).getId(),
 doc=DocumentApp.openById(id),
 docBody=doc.getBody();
 for (var j = 0 ; j < numColumns -1 ; j++) { 
 docBody.replaceText('{' + headerRow[0][j] + '}' , dataRows[i][j]); 
 } 
 
 emailaddress=dataRows[i][2];
 
 doc.addViewer( emailaddress);
 docUrl=doc.getUrl();
 doc.saveAndClose();
 
 var now = new Date();
 
 
 MailApp.sendEmail( emailaddress, 
 subject ,
 "เปิดอ่านเอกสารลับสุดยอดได้ที่ \n" + docUrl
 ); 
 try { 
 
 dataRows[i][numColumns-2] = id; 
 dataRows[i][numColumns-1] = now; 
 } catch (e) {
 
 dataRows[i][numColumns-1] = e.message;
 } 
 }
 
 SalarySheet.getRange(data).setValues(dataRows);
 ScriptApp.newTrigger('destroySecretDocument')
 .timeBased()
 .after(2 * 60 * 1000)
 .create();
 
}

function destroySecretDocument() {
 var ss = SpreadsheetApp.getActiveSpreadsheet(),
 SalarySheet=SpreadsheetApp.setActiveSheet(ss.getSheetByName("Salary")),
 DataRange=SalarySheet.getDataRange().getValues();
 
 for(var i=1; i< DataRange.length; i++){
 var id=DataRange[i][6];
 var emailAddress=DataRange[i][2];
 DocumentApp.openById(id).removeEditor(emailAddress);
 DriveApp.removeFile(DriveApp.getFileById(id));
 }
}