我需要在脚本运行后将状态列设置为"created"

Question

我有一段代码，可以分割谷歌工作表中的行，并将这些信息放入谷歌文档中，然后转换为pdf。它会将pdf文件移动到目标文件夹中。我现在所需要的是，当这一切完成时，我如何让脚本将我的状态列(AH列(更新为"已创建"，这样它就不会发送重复的内容。(对不起，我对应用程序脚本很陌生(

代码如下：

PDF Creator - Email all responses
=================================

当您单击"创建PDF>为每行创建PDF"时，此脚本为所附GSheet中的每一行构造一个PDF。中的值"文件名"列用于命名文件，如果存在value-通过电子邮件发送给"电子邮件"列中的收件人。

// Config
// ------

//  1. Create a GDoc template and put the ID here
var TEMPLATE_ID = '1R5Z_7........'
// 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 = 'The email subject ---- UPDATE ME -----'
var EMAIL_BODY = 'The email body ------ UPDATE ME ---------'
// 4. If a folder ID is specified here this is where the PDFs will be located
var RESULTS_FOLDER_ID = '1DHiPL......'
// Constants
// ---------
// You can pull out specific columns values 
var FILE_NAME_COLUMN_NAME = 'File Name'
var EMAIL_COLUMN_NAME = 'Email Address'
var NAME_COLUMN_NAME = 'First Name'
var JOB_COLUMN_NAME = 'Job Description'


// The format used for any dates 
var DATE_FORMAT = 'yyyy/MM/dd';
/**
* 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
allRows.forEach(function(row) {
createPdf(templateFile, headerRow, row)
// Private Function
// ----------------
/**
* 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()
// 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
}
}
// Create the PDF file
copyDoc.saveAndClose()
var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))  
copyFile.setTrashed(true)
// Rename the new PDF file
if (PDF_FILE_NAME !== '') {
newFile.setName(PDF_FILE_NAME)
} else if (ID !== null){
newFile.setName(ID)
}
// Put the new PDF file into the results folder
if (RESULTS_FOLDER_ID !== '') {
DriveApp.getFolderById(RESULTS_FOLDER_ID).addFile(newFile)
DriveApp.removeFile(newFile)
}
// 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

我仔细查看了您的代码，根据我的理解，您希望将"Created"写回用于创建PDF的单元格的行数据。

您可以做的是，在forEach(function(row){})函数中再添加一个额外的参数索引(计数器(，以保持循环迭代次数，然后您可以将此计数器用作行号，以引用"Created"字所在的单元格。

示例：

function createPdfs(){
........
allRows.forEach(function(row,index) {
........
//Once PDF created
var colNum=5; //Column number where you want to write "Created" 
updateCell(parseInt(index)+2,colNum,"Created"); 
........
})
}

这是您的updateCell函数，您也可以在相同的函数中编写

function updateCell(rowNum,colNum,value){
var ss=sheet activation code
ss.getRange(rowNum,colNum).setValue(value);
........
}