创建和电子邮件从表单提交谷歌文档

Question

我正试图从表单提交创建一个谷歌文档，并通过电子邮件发送文档。文档的数据来自第二张表(不是响应表)，该表根据提交查找值(学生id查找放置在第三张表中的学生、家长、班级和教师数据)。由于某些原因，创建和发送的电子邮件和文档是以前的提交，而不是最近的提交。我的脚本知识不足以解决这个问题。非常感谢任何帮助。我的文档

function createNewGoogleDocs() {
const googleDocTemplate = DriveApp.getFileById('1Q5s3doVHDDNygeLFNLqm90x9JoVZTIFW77Xix7VckVs');
var cache = CacheService.getScriptCache();
const destinationFolder = DriveApp.getFolderById('1rE8vvX9omreAUy7gnrhxpYcfM6uK3v4r')
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data')
const rows = sheet.getRange("Data!A2:S2").getValues();
const friendlyDate = new Date(rows[4]).toLocaleDateString("dd/mm/yyyy");
const sheetR = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses')
rows.forEach(function(row, index){
const copy = googleDocTemplate.makeCopy(`${row[12]}, ${row[13]}` , destinationFolder)
const doc = DocumentApp.openById(copy.getId())
const body = doc.getBody();
const friendlyDate = new Date(row[4]).toDateString();
body.replaceText('{{timestamps}}', row[0]);
body.replaceText('{{emailSG}}', row[1]);
body.replaceText('{{rutalumnoa}}', row[2]);
body.replaceText('{{cOMMENTS}}', row[3]);
body.replaceText('{{fecha}}', friendlyDate);
body.replaceText('{{solicitadapor}}', row[5]);
body.replaceText('{{asistentes}}', row[6]);
body.replaceText('{{objetivo}}', row[7]);
body.replaceText('{{familia}}', row[8]);
body.replaceText('{{colegio}}', row[9]);
body.replaceText('{{conclusionyacuerdos}}', row[10]);
body.replaceText('{{sTUDENTnAME}}', row[12]);
body.replaceText('{{cLASS}}', row[13]);
body.replaceText('{{tEACHERNAME}}', row[14]);
body.replaceText('{{motherName}}', row[15]);
body.replaceText('{{fatherName}}', row[16]);
body.replaceText('{{motherRut}}', row[17]);
body.replaceText('{{fatherRut}}', row[18]);
doc.saveAndClose();
const url = doc.getUrl();
const docname = doc.getName();
const StudentName = sheet.getRange("M2").getValue();
const Class = sheet.getRange("N2").getValue();
sheet.getRange("U2").setValue(url);
const GDoc =  sheet.getRange("U2").getValue();
const emailAddress = sheet.getRange("B2").getValue();
const message = 'Entrevista: Alumno/a ' + StudentName +', Curso: ' + Class +', Archivo: ' + docname +', ' + GDoc;
const subject = 'Entrevista: ' + StudentName +', ' + Class;
MailApp.sendEmail(emailAddress, subject, message);
sheetR.deleteRow(2);
})
}

Answer 1

最终错误出现在电子表格中;我使用的回答表中的范围是通过使用Importrange方法获得的。我改变了方法的索引和计数方法(例如。=指数(反应! A2: K, Counta(反应! A2: A)))。这招奏效了。不需要删除脚本中的响应