Apps Script: How to Take Multiple Converted Google Docs and Place it into Only One Google Doc

Question

I am trying to automate some tasks and was wondering if what I'm considering is possible using Google Apps Script. Here's a bit of context first:

I have a Google Form connected to a spreadsheet. Once a Google Form is submitted by an employee, I have a Google Apps script that is triggered and it transfers the information from the spreadsheet and puts the info into its own Google Doc.

This strategy has been working well, but I'd like to streamline it a little bit more by reducing the number of Google Docs it creates. Rather than this form submission resulting in a new Google Doc for every single submission, I would like to create an Google Apps script that makes sure that any new Google Form submissions (and that then go into the spreadsheet) are converted and placed into one large, single Google Doc, so that each employee can access their submissions in one document rather than having 30 or so Google Docs in our Drive. In short, what I would hope is that an employee can view all their submissions in one single Google Doc.

Here's the script I'm currently using that doesn't include what I'd like to add (below).

Also, here is the link to my Google folder which contains an example of the set up I currently have:

https://drive.google.com/drive/u/0/folders/1oRE5Sc2oMlt4RxTKmkvw3p4Wr-DMuwn2

From what I'm gathering, a potential solution would involve copy/pasting the Newly Created Google Doc into the Centralized Google Doc (The Doc that will store all submissions) then delete the Newly Created Google Doc. Is this something that is possible and/or reasonable by using Apps Script?

Any and all help appreciated and thanks for the feedback so far.

Michael

 function formtoDoc(e) {
  var callerName = e.values[1];
  var date = e.values[2];
  var disposition = e.values[3];
  var demographics = e.values[4];
  var employeeNotes = e.values[5];
  var callerResponse = e.values[6];
  var notesfromDemographics = e.values[7];
  var employeeName = e.values[8];
  var supervisorName = e.values[9];
  var templateFile = DriveApp.getFileById("1SkA-GYLq8lWjAJuUoj3l_EhS0ssv69pKmxg-Z-EBxXM");
  var templateResponseFolder = DriveApp.getFolderById("1XCUik_pVutA5QevjQgJPJHmM85CPTG5G");
  var copy = templateFile.makeCopy(callerName, templateResponseFolder);
  var doc = DocumentApp.openById(copy.getId());
  var body = doc.getBody();
  body.replaceText("{{Caller Name}}", callerName);
  body.replaceText("{{Date of Call}}", date);
  body.replaceText("{{Disposition}}", disposition);
  body.replaceText("{{Demographics}}", demographics);
  body.replaceText("{{Employee Notes}}", employeeNotes);
  body.replaceText("{{Caller Response}}", callerResponse);
  body.replaceText("{{Notes from Demographics}}", notesfromDemographics);
  body.replaceText("{{Employee Name}}", employeeName);
  body.replaceText("{{Supervisor Name}}", supervisorName);
  doc.saveAndClose();
}

Answer 1

(Solution 1):

  var doc = DocumentApp.openById("ENETER YOUR DOC ID");
  var body = doc.getBody();

function formtoDoc(e) {
  var callerName = e.values[1];
  var date = e.values[2];
  var disposition = e.values[3];
  var demographics = e.values[4];
  var employeeNotes = e.values[5];
  var callerResponse = e.values[6];
  var notesfromDemographics = e.values[7];
  var employeeName = e.values[8];
  var supervisorName = e.values[9];

  let count = body.getNumChildren();
  for (let i = 0; i < count; i++) {
    let child = body.getChild(i);
    if (child.getType() == DocumentApp.ElementType.PARAGRAPH) {
      if (child.asParagraph().findText("Call Log")) start = true;
      if (start) body.appendParagraph(child.asParagraph().copy());
      if (child.asParagraph().findText("End of Call Log")) break;
    }
    else if (child.getType() == DocumentApp.ElementType.TABLE) {
      if (start) body.appendTable(child.asTable().copy());
    }
  }
  
  replaceSingleText("{{Caller Name}}", "{{Caller Name1}}");
  replaceSingleText("{{Date of Call}}", "{{Date of Call1}}");
  replaceSingleText("{{Disposition}}", "{{Disposition1}}");
  replaceSingleText("{{Demographics}}", "{{Demographics1}}");
  replaceSingleText("{{Employee Notes}}", "{{Employee Notes1}}");
  replaceSingleText("{{Caller Response}}", "{{Caller Response1}}");
  replaceSingleText("{{Notes from Demographics}}", "{{Notes from Demographics1}}");
  replaceSingleText("{{Employee Name}}", "{{Employee Name1}}");
  replaceSingleText("{{Supervisor Name}}", "{{Supervisor Name1}}");

  replaceSingleText("{{Caller Name}}", callerName);
  replaceSingleText("{{Date of Call}}", date);
  replaceSingleText("{{Disposition}}", disposition);
  replaceSingleText("{{Demographics}}", demographics);
  replaceSingleText("{{Employee Notes}}", employeeNotes);
  replaceSingleText("{{Caller Response}}", callerResponse);
  replaceSingleText("{{Notes from Demographics}}", notesfromDemographics);
  replaceSingleText("{{Employee Name}}", employeeName);
  replaceSingleText("{{Supervisor Name}}", supervisorName);
  

  replaceSingleText("{{Caller Name1}}", "{{Caller Name}}");
  replaceSingleText("{{Date of Call1}}", "{{Date of Call}}");
  replaceSingleText("{{Disposition1}}", "{{Disposition}}");
  replaceSingleText("{{Demographics1}}", "{{Demographics}}");
  replaceSingleText("{{Employee Notes1}}", "{{Employee Notes}}");
  replaceSingleText("{{Caller Response1}}", "{{Caller Response}}");
  replaceSingleText("{{Notes from Demographics1}}", "{{Notes from Demographics}}");
  replaceSingleText("{{Employee Name1}}", "{{Employee Name}}");
  replaceSingleText("{{Supervisor Name1}}", "{{Supervisor Name}}");

  doc.saveAndClose();
}

//custom function to replace first occurence only
function replaceSingleText(textToReplace, newText){
  var found = body.findText(textToReplace);
    if (found) {
    var start = found.getStartOffset();
    var end = found.getEndOffsetInclusive();
    var text = found.getElement().asText();
    text.deleteText(start, end);
    text.insertText(start, newText).setBold(false);
  }
}

Result:

Page 1: Page 2:

Explanation:

So it first copies the template you have in the first page and it keeps on appending it right after the last line in your doc.

I have tested this and it works. An issue I encountered however, is that it adds a new line after the tables. If it does not bother you, you can just replace all the double empty lines with one using the Replace All (Ctrl + H) in the doc.

Or a workaround I have thought of is to adjust the spacing on the template itself so that it will have the right formatting for the next logs.

You can not use the replaceText for this scenario since this replaces all the occurences for the text to replace so I added a custom function to replace only the first text.

Let me know if this works for you.

Solution 2:

  var sourceDoc = DocumentApp.openById("ID OF SOURCE DOCUMENT TO COPY / TEMPLATE").getBody();
  var targetDoc = DocumentApp.openById("ID OF TARGET DOCUMENT / CALL LOGS");
  var body = targetDoc.getBody()

function formtoDoc(e) {
  var callerName = e.values[1];
  var date = e.values[2];
  var disposition = e.values[3];
  var demographics = e.values[4];
  var employeeNotes = e.values[5];
  var callerResponse = e.values[6];
  var notesfromDemographics = e.values[7];
  var employeeName = e.values[8];
  var supervisorName = e.values[9];

  copyDoc();

  body.replaceText("{{Caller Name}}", callerName);
  body.replaceText("{{Date of Call}}", date);
  body.replaceText("{{Disposition}}", disposition);
  body.replaceText("{{Demographics}}", demographics);
  body.replaceText("{{Employee Notes}}", employeeNotes);
  body.replaceText("{{Caller Response}}", callerResponse);
  body.replaceText("{{Notes from Demographics}}", notesfromDemographics);
  body.replaceText("{{Employee Name}}", employeeName);
  body.replaceText("{{Supervisor Name}}", supervisorName);

  targetDoc.saveAndClose();
}

function copyDoc() {
  var totalElements = sourceDoc.getNumChildren();

  for( var j = 0; j < totalElements; ++j ) {
    var element = sourceDoc.getChild(j).copy();
    var type = element.getType();
    if( type == DocumentApp.ElementType.PARAGRAPH ){
      body.appendParagraph(element);
    }
    else if( type == DocumentApp.ElementType.TABLE){
      body.appendTable(element);
      }
    else if( type == DocumentApp.ElementType.LIST_ITEM){
      body.appendListItem(element);
      }
//    ...add other conditions (headers, footers...
    }
}

Explanation: For this one, it makes use of 2 document files. One for the template itself and another one for the target document where the call logs will be recorded. You just need to create a new blank document for the target doc and edit the ID in the script.

References:

• replaceText
• Replace text only for first occurence