I need help modifying my current workflow to merge all form submissions into one master document.
Right now I have a Google Form that feeds data into a spreadsheet. When someone submits the form, my Apps Script automatically creates a new Google Doc for each submission using the spreadsheet data. This works fine but creates too many individual files.
I want to change this so all submissions get added to one master Google Doc instead of creating separate documents every time. This way employees can see all their form responses in a single place rather than having dozens of separate docs cluttering our shared drive.
My idea is to modify the script so it appends new content to an existing master document instead of making new files each time. Maybe I could copy the content from a temporary doc and paste it into the main one, then delete the temporary file.
Here’s my current script that makes individual documents:
function processFormSubmission(event) {
var clientName = event.values[1];
var submissionDate = event.values[2];
var category = event.values[3];
var details = event.values[4];
var userComments = event.values[5];
var feedback = event.values[6];
var additionalInfo = event.values[7];
var staffMember = event.values[8];
var managerName = event.values[9];
var sourceTemplate = DriveApp.getFileById("template_id_here");
var destinationFolder = DriveApp.getFolderById("folder_id_here");
var newCopy = sourceTemplate.makeCopy(clientName, destinationFolder);
var document = DocumentApp.openById(newCopy.getId());
var docBody = document.getBody();
docBody.replaceText("{{Client Name}}", clientName);
docBody.replaceText("{{Submission Date}}", submissionDate);
docBody.replaceText("{{Category}}", category);
docBody.replaceText("{{Details}}", details);
docBody.replaceText("{{User Comments}}", userComments);
docBody.replaceText("{{Feedback}}", feedback);
docBody.replaceText("{{Additional Info}}", additionalInfo);
docBody.replaceText("{{Staff Member}}", staffMember);
docBody.replaceText("{{Manager Name}}", managerName);
document.saveAndClose();
}
Is there a way to append this content to one master document instead? Any suggestions would be really helpful.
You can definitely modify your existing script to append content to a master document rather than creating individual files. The key change is replacing your template copying logic with direct content insertion into your master document. Instead of using makeCopy(), open your master document with DocumentApp.openById() using your master document’s ID. Then use the getBody().appendParagraph() method to add each form submission as new paragraphs. I’d suggest creating a formatted string that contains all your form data and then appending it in one go. Something like creating a submission block with all the client info, dates, and comments formatted nicely. You might want to add document.appendHorizontalRule() between submissions to visually separate them. Also consider adding the submission date prominently since everything will be in chronological order. One gotcha I ran into was making sure the master document doesn’t get corrupted if multiple form submissions happen simultaneously, so you might want to add some error handling around the document operations.
I’ve dealt with a similar situation before and found that directly appending to the master document works much better than the copy-paste approach you mentioned. Instead of creating new documents, you can open your master document and use appendParagraph() or insertParagraph() methods to add new content. Here’s what worked for me: First, define your master document ID at the top of your function. Then instead of making a copy of the template, open the master document directly and append the formatted content. You’ll want to add some kind of separator between entries like a page break or horizontal line so individual submissions don’t run together. One thing to watch out for is the document size limit. Google Docs can handle quite a bit but if you’re getting hundreds of submissions you might hit performance issues. I also recommend adding a timestamp to each entry since you’ll have everything chronologically in one file. The appendParagraph method maintains formatting pretty well if you need bold headers or specific styling for each submission section.
just modify the part where you open the document - instead of DocumentApp.openById(newCopy.getId()) use your master doc id directly. then use docBody.appendPageBreak() and append all your content with the same replaceText stuff but on the master doc. way simpler than copying and pasting between docs imo