google-apps-script - 脚本执行后锁定工作表中的行并且最后一列有文本 SENT

Question

处理项目，合并行以创建收据的 PDF 并通过电子邮件发送。该项目运行良好。这是特定于工作表的文件夹的链接 https://drive.google.com/drive/folders/1pnDSS9DOz7hdCh9hl1AszcQOIAmR2Taw?usp=sharing

问题我想在脚本运行并且最后一列指示已发送时锁定行。我已经进行了全面研究，但在脚本执行后发送电子邮件并且最后一列显示 SENT 后无法找到锁定它的方法。 在此处输入图像描述

REQUEST请求帮助以帮助脚本在脚本执行并且最后一列已测试 SENT 后锁定行

下面是项目的完整代码，因为我不确定在哪里插入这个新的代码字符串。

// Config
// ------

// 1. Create a GDoc template and put the ID here
var TEMPLATE_ID = '1LbxCGFUk0I-9RzxkULk-coAXxST_4uXtHFB3o8cclF8'

// 2. You can specify a name for the new PDF file here, or leave empty to use the 
// name of the template or specify the file name in the sheet
var PDF_FILE_NAME = ''

// 3. If an email address is specified you can email the PDF
var EMAIL_SUBJECT = 'Receipt Payment for <<Fullname>>, '
var EMAIL_BODY = 'Dear <<Fullname>>, Please find the attached payment receipt as a confirmation to your payment. Thank you, Kind Regards,'

// 4. If a folder ID is specified here this is where the PDFs will be located
var RESULTS_FOLDER_ID = '1VnJM9GSWz5Vs5-UEt3CAqH0N8iv-W7eo'

// Constants
// ---------
// You can pull out specific columns values 
var FULLNAME_COLUMN_NAME = 'Fullname'
var EMAIL_COLUMN_NAME = 'Email'
var RECEIPTNO_COLUMN_NAME = 'ReceiptNo'

// The format used for any dates 
var DATE_FORMAT = 'dd/MM/yyyy';

/**
 * Eventhandler for spreadsheet opening - add a menu.
 */

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu('[ Create PDFs ]')
    .addItem('Create a PDF for each row', 'createPdfs')
    .addToUi()

} // onOpen()

/**  
 * 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[8]!='SENT'){
      Logger.log("Send an email");
      createPdf(templateFile, headerRow, row)
    
      //Set status column to SENT
      activeSheet.getRange(index+2,9).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 RECEIPT = 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 === FULLNAME_COLUMN_NAME) {
    
      ID = activeCell
      
    } else if (headerValue === EMAIL_COLUMN_NAME) {
    
      recipient = activeCell

    } else if (headerValue === RECEIPTNO_COLUMN_NAME) {
      RECEIPT = 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(RECEIPT + ' ' + ID)
    
  } 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('<<Fullname>>', ID);
  var body = EMAIL_BODY.replace('<<Fullname>>', ID);

  // Email the new PDF

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

}// createPdfs.createPdf()

标签： google-apps-scriptgoogle-sheets