I am converting Google form submissions into pdf files and the code seems to work for the values that are questions listed in the form. however I also have multiple columns that calculate data based on answers. I'm wondering how I can add all headers to the list of constructed information.
Below is the code I'm using. However it only grabs the data from the namedValues generated from the form submission. I need to add the data from the last row of all columns into the constructed info
function After_Submit(e){
const info = e.namedValues;
const pdfFile = Create_PDF(info);
console.log(info);
sendEmail(e.namedValues['Email Address to Receive file'][0],pdfFile);
}
function sendEmail(email,pdfFile){
GmailApp.sendEmail(email, "Subject", "File Attached", {
attachments: [pdfFile],
name: "PDF Sender"
});
}
function Create_PDF(info) {
const PDF_folder = DriveApp.getFolderById("Storage destination ID");
const TEMP_FOLDER = DriveApp.getFolderById("Temp Folder ID");
const PDF_Template = DriveApp.getFileById('template ID');
const newTempFile = PDF_Template.makeCopy(TEMP_FOLDER);
const OpenDoc = DocumentApp.openById(newTempFile.getId());
const body = OpenDoc.getBody();
console.log(body);
body.replaceText("{{value1}}", info['value1'][0])
body.replaceText("{{value2}}", info['value2'][0]);
body.replaceText("{{value3}}", info['value3'][0]);
OpenDoc.saveAndClose();
const BLOBPDF = newTempFile.getAs(MimeType.PDF);
const pdfFile = PDF_folder.createFile(BLOBPDF).setName("FLHA");
console.log("The file has been created ");
return pdfFile;
}
CodePudding user response:
Use the range object from the Form Submit Event Object to get all necessary details (such as headers, newly added form data and formulated column values) and use it to create an Object that you can use to populate your PDF.
In my example below, I created a form that will populate columns A-H and added the formulated columns to I-O.
Input:
Code:
function After_Submit(e){
var range = e.range;
var row = range.getRow(); //get the row of newly added form data
var sheet = range.getSheet(); //get the Sheet
var headers = sheet.getRange(1, 1, 1, 15).getValues().flat(); //get the header names from A-O
var data = sheet.getRange(row, 1, 1, headers.length).getValues(); //get the values of newly added form data formulated values
var values = {}; // create an object
for( var i = 0; i < headers.length; i ){
values[headers[i]] = data[0][i]; //add elements to values object and use headers as key
}
Logger.log(values);
}
Note:
- Unlike using namedValues, this method will include the column that has no data.
- If you want to adjust the number of headers to be included in your object, just change the 4th parameter of
var header
.
Result:
{4=, Location of Work=Test, 1=X, 6=, Workers Present=Test, Test2, 2=X, 5=X, 7=, Work To Be Done=Test, Timestamp=Tue Oct 12 06:06:30 GMT 08:00 2021, Environmental Hazards=Dust / Mist Fumes, Noise in area, Waste, 3=, Tasks To Be Completed=Test, Muster Point=Test, PPE Inspected=Test}