Google sheets scripts automate script to skip row

Question

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()

Answer 1

You can refer to this sample implementation:

allRows.forEach(function(row, index) {

    //Check if current row status is not SENT
    if(row[8]!='SENT'){
      Logger.log("Send an email");
      createPdf(templateFile, headerRow, row);

      //Set status column to SENT
      activeSheet.getRange(index+2,9).setValue("SENT");
    }
  });

• In setting the value of status column (column index 9), you need to add 2 offset as your row index in Sheet.getRange(row,column) since you removed the header row and the array starts at zero index

(UPDATED ANSWER):

/**  
 * Take the fields from each row in the active sheet
 * and, using a Google Doc template, create a PDF doc with these
 * fields replacing the keys in the template. The keys are identified
 * by having a % either side, e.g. %Name%.
 */

function createPdfs() {

  var ui = SpreadsheetApp.getUi()

  if (TEMPLATE_ID === '') {    
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }

  // Set up the docs and the spreadsheet access

  var templateFile = DriveApp.getFileById(TEMPLATE_ID);
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var allRows = activeSheet.getDataRange().getValues()
  var headerRow = allRows.shift()

  // Create a PDF for each row
  Logger.log(allRows);
  allRows.forEach(function(row, index) {
    Logger.log(row);
    if(row[6]!='SENT'){
      Logger.log("Send an email");
      createPdf(templateFile, headerRow, row)
    
      //Set status column to SENT
      activeSheet.getRange(index+2,7).setValue("SENT");
    } 
  })


  ui.alert('New PDF files created')

  return
  

  
} // createPdfs()



/**
* 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()

/**
 * Create a PDF
 *
 * @param {File} templateFile
 * @param {Array} headerRow
 * @param {Array} activeRow
 */

function createPdf(templateFile, headerRow, activeRow) {
  //Check if current row status is not SENT  
  var headerValue
  var activeCell
  var ID = null
  var NAME = 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()
      
  // Replace the keys with the spreadsheet values and look for a couple
  // of specific values

  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

    } else if (headerValue === NAME_COLUMN_NAME) {
      NAME = activeCell
    }
  }
  
  // Create the PDF file
    
  copyDoc.saveAndClose()
  var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))  
  copyFile.setTrashed(true)

  // Rename the new PDF file

  if (ID !== null) {
  
    newFile.setName(ID + ' ' + NAME)
    
  } else if (PDF_FILE_NAME !== ''){

    newFile.setName(PDF_FILE_NAME)
  }
  
  // Put the new PDF file into the results folder
  
  if (RESULTS_FOLDER_ID !== '') {
  
    DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile)
    DriveApp.removeFile(newFile)
  }

  // Update template and replace the variable with NAME
  var subject = EMAIL_SUBJECT.replace('<<Name>>', NAME);
  var body = EMAIL_BODY.replace('<<Name>>', NAME);

  // Email the new PDF

  if (recipient !== null) {
  
    MailApp.sendEmail(
      recipient, 
      subject, 
      body,
      {attachments: [newFile]})
  }

}// createPdfs.createPdf()

Answer 2

Try rewriting it like this:

var TEMPLATE_ID = '1a1iQGpCo3c4djPcYGtcTZg1uSi1-KA_ZzpzxAcNFcvU'
var PDF_FILE_NAME = (FILE_NAME_COLUMN_NAME = 'File Name' + ' ' + 'testing')
var EMAIL_SUBJECT = 'The email subject'
var EMAIL_BODY = 'The email body'
var RESULTS_FOLDER_ID = '1jZJ9u1BGZu8bfwsFJGcZFz4pkAHBG0K-'
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()
}

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] })
  }
}

function createPdfs() {
  var ui = SpreadsheetApp.getUi()
  if (TEMPLATE_ID === '') {
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }
  var templateFile = DriveApp.getFileById(TEMPLATE_ID)
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var allRows = activeSheet.getDataRange().getValues()
  var headerRow = allRows.shift();
  allRows.forEach(function (row) {
    createPdf(templateFile, headerRow, row)
  })
  ui.alert('New PDF files created')
  return
}

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;
}