I am working on a mini project which creates a PDF for each row then emails it to the respective emails as well as saving it on Drive. Just having once challenge, which even after research havent found a solution.
PROBLEM When I run the scripts, after adding data in new rows, the merge and send start again all from the first row. REQUEST I would like a solution script where if email is sent, then the column of status says SENT followed by if I run the script again it will skip the rows with status sent.
This is the sheets data
here are the codes which merge and send the emails
// self note:Doc to merge template
var TEMPLATE_ID = '1a1iQGpCo3c4djPcYGtcTZg1uSi1-KA_ZzpzxAcNFcvU'
// self note: to specify name code below
var PDF_FILE_NAME = (FILE_NAME_COLUMN_NAME = 'File Name' + ' '+'testing')
// self note: If Email available will email
var EMAIL_SUBJECT = 'The email subject'
var EMAIL_BODY = 'The email body'
// Self note: where PDF saved on drive
var RESULTS_FOLDER_ID = '1jZJ9u1BGZu8bfwsFJGcZFz4pkAHBG0K-'
// Self Note: Constants
// Self Note:You can pull out specific columns values
var FILE_NAME_COLUMN_NAME = 'File Name'
var EMAIL_COLUMN_NAME = 'Email'
var NAME_COLUMN_NAME = 'Name'
var DATE_FORMAT = 'yyyy/MM/dd';
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu('[ Create PDFs ]')
.addItem('Create a PDF for each row', 'createPdfs')
.addToUi()
} // onOpen()
/**
* self note: pick fields, each row in the active sheet to doc to creat pdf
*/
function createPdfs() {
var ui = SpreadsheetApp.getUi()
if (TEMPLATE_ID === '') {
ui.alert('TEMPLATE_ID needs to be defined in code.gs')
return
}
// self noteSet docs sheet access
var templateFile = DriveApp.getFileById(TEMPLATE_ID)
var activeSheet = SpreadsheetApp.getActiveSheet()
var allRows = activeSheet.getDataRange().getValues()
var headerRow = allRows.shift()
// self Note: Create a PDF for each row
allRows.forEach(function(row) {
createPdf(templateFile, headerRow, row)
/**
* Create a PDF
*
* @param {File} templateFile
* @param {Array} headerRow
* @param {Array} activeRow
*/
function createPdf(templateFile, headerRow, activeRow) {
var headerValue
var activeCell
var ID = null
var recipient = null
var copyFile
var numberOfColumns = headerRow.length
var copyFile = templateFile.makeCopy()
var copyId = copyFile.getId()
var copyDoc = DocumentApp.openById(copyId)
var copyBody = copyDoc.getActiveSection()
for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++) {
headerValue = headerRow[columnIndex]
activeCell = activeRow[columnIndex]
activeCell = formatCell(activeCell);
copyBody.replaceText('<<' + headerValue + '>>', activeCell)
if (headerValue === FILE_NAME_COLUMN_NAME) {
ID = activeCell
} else if (headerValue === EMAIL_COLUMN_NAME) {
recipient = activeCell
}
}
// Self Note: PDF file create
copyDoc.saveAndClose()
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))
copyFile.setTrashed(true)
// Self note: Rename the new PDF file
if (ID !== null){
newFile.setName(ID)
} else if (PDF_FILE_NAME !== '') {
newFile.setName(PDF_FILE_NAME)
}
// Self note PDF put in folder
if (RESULTS_FOLDER_ID !== '') {
DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile)
DriveApp.removeFile(newFile)
}
// Self Note: Email the new PDF
if (recipient !== null) {
MailApp.sendEmail(
recipient,
EMAIL_SUBJECT,
EMAIL_BODY,
{attachments: [newFile]})
}
} // createPdfs.createPdf()
})
ui.alert('New PDF files created')
return
// Private Functions
/**
* Format the cell's value
*
* @param {Object} value
*
* @return {Object} value
*/
function formatCell(value) {
var newValue = value;
if (newValue instanceof Date) {
newValue = Utilities.formatDate(
value,
Session.getScriptTimeZone(),
DATE_FORMAT);
} else if (typeof value === 'number') {
newValue = Math.round(value * 100) / 100
}
return newValue;
} // createPdf.formatCell()
} // createPdfs()
