Search code examples
google-apps-scriptgoogle-sheetspdfgoogle-forms

Why is there a mismatch between form data written to spreadsheet, and the PDF created?


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. Why?

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

Solution

  • 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()