0

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

enter image description here

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

2 Answers 2

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()
Sign up to request clarification or add additional context in comments.

8 Comments

Dear @Ron M, where should the implementation be placed? I get an error that the allRows function is not defined.
I just incorporate it inside your createPdfs() which contains allRows array
Dear @RonM, I followed your advice, however what is happening is that the text SENT will be input. but if i put data in a new row and run the script, it doesnt skip the rows which have SENT assigned to them. HERE is the link to the sheets with code. Please advise on a way to skip the rows which have the SENT text in the column
Dear @RonM I Have Run the Script but 2 things. if the status cell is empty, it should merge it and send the email and pdf. If it has the SENT it should skip that row and run to the next. I get the Exceeded Maximum Stack Depth Error if I leave one or all the STATUS Cells empty. Please advise through your expertise. this is the only last issue i am having with the project
I noticed you put the changes in the wrong place. Can you share your template document as well so i could test the changes that I'm about to make?
|
1

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.