CODEWITHSUNDEEP
formsemailgoogle-apps-scriptgoogle-sheetsgoogle-docs
Marcelo Meza
Marcelo Meza

Reputation: 73

Create and email google doc from form submission

I'm trying to create a Google doc from a form submission and send the doc via email. THe data for the doc comes from a second sheet (not the response sheet) that looks up values based on the submission (a student id that looks up student, parent, class, and teacher data placed in a 3rd sheet). For some reason, the email and doc that is created and sent is the previous submission, but not the latest submission. My scripting knowledge is insufficient to figure this one out. Any help is much appreciated. My document

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

Upvotes: 0

Views: 97

Answers (1)

Marcelo Meza
Marcelo Meza

Reputation: 73

In the end the error was in the spreadsheet; the range from the response sheet that I was using was obtained by using the Importrange method. I changed the method to an Index and Counta method (Eg. =index(Responses!A2:K,Counta(Responses!A2:A))). This did the trick. No need to delete the responses in the script.

Upvotes: 1

Related Questions