Google Script import form data to new spreadsheet, then send as PDF

Question

Thank you all for this great resource. I am new to google scripts and it has helped me greatly.

I am trying to create a script that takes google form information, creates a new spreadsheet, imports that data into new spreadsheet to do calculation and make a chart, then email a PDF of that sheet to the Form User.

I thought I was getting close, but ran into an issue. The data imports over to the new spreadsheet, but the data does not show up in the PDF in my e-mail.

A few questions; Do you guys see any changes that I could make to my code to help the PDF display the data? Is there a better or simpler way to accomplish what I'm trying to do?

Thanks!!

//Template Info
  var docTemplate = "myssID";

  var docName = "MotionControlReport";

function formSubmitCreate(e) {

  //Variables

  var userOrientation = e.values[1];
  var userStroke = e.values[2];
  var userStrokeUnits = e.values[3];
  var userName = e.values[11];
  var userCompany = e.values[12];
  var userEmail = e.values[13];
  var userPhone = e.values[14];

  //Template Info
  var copyId = DocsList.getFileById(docTemplate).makeCopy(docName+' for '+userName).getId();
  var copyDoc = SpreadsheetApp.openById(copyId);
  var copyBody = copyDoc.getActiveSheet();

  copyBody.getRange(1, 1).setValue(userName);
  copyBody.getRange(1, 2).setValue(userCompany);
  copyBody.getRange(1, 3).setValue(userEmail);
  copyBody.getRange(1, 4).setValue(userPhone);
  copyBody.getRange(1, 5).setValue(userOrientation);
  copyBody.getRange(1, 6).setValue(userStroke);
  copyBody.getRange(2, 6).setValue(userStrokeUnits);

  //Save as PDF and send e-mail
  var pdf = DocsList.getFileById(copyId).getAs("application/pdf");
  var subject = "Motion Control Report";
  var body = userName + ",  Thank you very much for using our online Linear Motion Sizing Tool.";
  MailApp.sendEmail(userEmail, subject, body, {htmlBody: body, attachments: pdf});
  //Deletes temporary Document
  DocsList.getFileById(copyId).setTrashed(true);
}

Buravchik Buravchik · Accepted Answer · 2012-11-28T15:41:37

You forgot to flush your Spreadsheet and data is saved from cache a little bit later than it is converted to PDF. I checked your code (with flushing) and it works well and mails correct data in PDF from form for me.

SpreadsheetApp().flush()

Google Script import form data to new spreadsheet, then send as PDF

1 Answers