How to I create a document with only the most recent Form submission's data automatically with Google Apps Script?

32 views
Skip to first unread message

Bad Cosmonaut

unread,
Jun 4, 2020, 5:28:04 PM6/4/20
to Google Apps Script Community

I want Google Apps Script to automatically take any new Form submission (as opposed to everything already on the sheet) and produce a document in which those details are formatted in HTML. A unique document per submission's data. I have already figured out how to create a Trigger that will execute the function upon a form being submitted. I have also figured out how to establish a template document for the script to copy and utilize.


I have included some of the code I've cobbled together from a lot of self-teaching. As you may see, I've gotten as far as being able to manipulate the documents. But, for example, when I try to replace the Management Corp placeholder in the Document, it's replaced with everything in that column rather than the cell of the newest submission. I realize that this is what "u:u" means and so I guess that's the point of my question-- what do I need to do to effectively say "only use the most newest u"? I will duplicate such a method for the other columns as well.


I deeply appreciate your time and help.


function createHTMLchunks() {
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");

  //DocID (redacted for question)
  //SheetID (redacted for question)
  //driveFolder (redacted for question)

  const docFile = DriveApp.getFileById("(redacted for question)");
  const driveFolder = DriveApp.getFolderById("(redacted for question)");
  const copyFile = docFile.makeCopy(driveFolder);
  const openFile = DocumentApp.openById(copyFile.getId());
  const body = openFile.getBody();
  body.replaceText("##Management Corp##", NewSubmission('U:U'));
  openFile.saveAndClose();


}



Scott Marquardt

unread,
Jun 4, 2020, 5:40:04 PM6/4/20
to Google Apps Script Community

Here's a method I've used to get the last row number for a column (I don't know if some other columns might be empty; I'm assuming you'll want the last row in your column of interest), if you think that'll do the trick.


Knowing the row, you can then specify the cell of interest. 

Michael O'Shaughnessy

unread,
Jun 4, 2020, 7:48:29 PM6/4/20
to google-apps-sc...@googlegroups.com
IMHO it would be better to get the submitted values into an array and deal with it from there.

I would suggest that you do something like this right after you get the "currentSheet"

let rowData = currentSheet.getRange(currentSheet.getLastRow(), 1, 1, currentSheet.getLastColumn()).getValues();

Now rowData is a 2d array that has only 1 "row" of data (which is the data that was just submitted).  Arrays are "0" based so the first column is referred to as 0, the second 1 and so on.  Let's say "Management Corp" info is in the 3rd column.  When you go to replace the text use something like this:

body.replaceText("##Management Corp##", rowData[0][2]);  // [0][2] can be read "in the first row (0) of the array get the 3rd item (2)

If you need an example of getting data from a spreadsheet and "putting" it somewhere else let me know... I am sure I can put something together.

In the meantime, have a look at the following:
https://developers.google.com/apps-script/guides/sheets //This has an example of reading and writing data to a sheet

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c3632323-2cd2-4a29-be94-e28925ada83eo%40googlegroups.com.

cbmserv...@gmail.com

unread,
Jun 4, 2020, 11:00:45 PM6/4/20
to google-apps-sc...@googlegroups.com

The simplest way to get the row where the submitted values from the form are is to let the google environment variable tell you.

 

If the createHTMLchunks function is your trigger function, then just do the following:

 

Function createHTMLchunks (e) {

 

  var row = e.range.getRow();  // row where form submitted data is saved

 

… rest of code ...

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

Bad Cosmonaut

unread,
Jun 8, 2020, 6:50:32 PM6/8/20
to Google Apps Script Community
This was all extremely helpful, you nailed it with those two lines! I was cruising after that. I'm basically finished thanks to you and the others here. I can't thank you enough.

I've expanded my code so that it will email myself and others with the file, but so far I have only been able to get the email to bring a PDF with it (although the PDF is perfect and does contain the newly submitted information formatted according to the central template). It would be awesome if I could attach the living Google Doc that was just created in the same function to the email.

I have been digging around in https://developers.google.com/apps-script/reference to find some language that might set me on the right track, but I haven't seen anything yet. Do you know of a way to do this?

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Michael O'Shaughnessy

unread,
Jun 8, 2020, 8:48:48 PM6/8/20
to google-apps-sc...@googlegroups.com
Well, "technically" you do not "attach" Google Docs... you provide a "link".  Now, the email will see that link and make it look like the doc is attached....

So, I suggest you save all your docs you create into a folder that is set to be "view only" to the "public".  This will let everyone view the document without requesting access.

Then you need to create the body of the email as an HTML document and add a link to the doc.  Here is a quick little function for example:

function email3(){
  var id = "REPLACE WITH DOC ID";
  var toEmail = "REPLACE WITH EMAIL OF RECIPIENT";
  var subj = "Test Doc Email";
  var body = "Just in case HTML can't be displayed by email.";
 
  var doc = DriveApp.getFileById(id)
  var link = doc.getUrl();  //get the link to the document
  var html = 'Dear whoever, <br>Please click on the following link to view the doc: <br><br><a href="'+ link + '">THE DOC</a><br><br>Thank you!';
  var options = {htmlBody:html}
  MailApp.sendEmail(toEmail, subj, body, options)
}

Note that since you have a lot of the info in your array you can easily replace "toEmail" with something like rowData[0][1] assuming the email address is in the 2nd column.
And this part, "<a href="'+ link + '">THE DOC</a>" is a basic HTML link where "THE DOC" is the text that will be shown as a hyperlink.  Last but not least, <br> gives you a line break.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/90b70e8a-ea3d-48da-8533-40053d5045aeo%40googlegroups.com.

Bad Cosmonaut

unread,
Jun 8, 2020, 9:28:37 PM6/8/20
to Google Apps Script Community
I'm at 100%! Thank you!
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Michael O'Shaughnessy

unread,
Jun 8, 2020, 10:27:39 PM6/8/20
to google-apps-sc...@googlegroups.com
Glad to help!!

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f3c3e65a-a6f9-42e1-bf0c-b51260bd5b07o%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages