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

https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush — TheMaster, Feb 11 '22 at 12:27

score 3 · Accepted Answer · answered Nov 28 '12 at 15:41

3

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

answered Nov 28 '12 at 15:41

Buravchik

509
1
5
11

Thanks for the reply Buravchik. Does it matter where I put SpreadsheetApp().flush()? – MattMoco Nov 28 '12 at 15:58
1

You need to do it after your edits are done and before you export to PDF. Good place is string before "//Save as PDF and send e-mail" – Buravchik Nov 28 '12 at 16:40

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

1 Answers1

Linked

Related