I'm using Google Forms to create an easy method of adding stories and photos to a Google doc for a collective history/journal.

My code takes the Google form responses from the linked Google sheet and then just appends the Google Form responses to the Google doc but I would like to add the responses sorted by the date that gets submitted in the Google form. That way an event that gets submitted that took place on 01/01/2020 will be listed before an event that took place on 01/02/2020 etc.

How would I go about doing that?

function autoFillGoogleDocFromForm(e) {
  var timestamp = e.values[0];
  var photo = e.values[1];
  var date = e.values[2];
  var event = e.values[3];
  var name = e.values[4];
  var photoCap = e.values[6];
  var photoDesc = e.values[7];

  var fileURL = photo;
  var fileID = fileURL.substr(fileURL.search("=") 1);  //strip off text before id= in the URL
  var image = DriveApp.getFileById(fileID).getBlob();  

  var doc = DocumentApp.openById("1DrE4ElgaP08uOTH52E2GjgmrJmoL2VZsZ1YlNeV0_20")

  var body = doc.getBody();
  body.appendPageBreak();
  body.appendParagraph(date);
  body.appendParagraph(event);
  body.appendParagraph(name);
  body.appendImage(image);
  body.appendParagraph(photoCap);
  body.appendParagraph(photoDesc);
  
  

  doc.saveAndClose();
}

CodePudding user response：

Here is an example of how to insert paragraphs based on date. I use Date object to compare dates so I convert a text string in the form "1/1/2022" to a Date object.

The format of your Doc must have the date string directly following the Page Break.

function testAutoFillGoogleDocFromForm() {
  try {
    let row = { values: [ "time", "photo", "2/1/2022", "event", "name", "", "photoCap", "photoDesc" ]};
    autoFillGoogleDocFromForm(row);
    console.log("done");
    row = { values: [ "time", "photo", "1/1/2023", "event", "name", "", "photoCap", "photoDesc" ]};
    autoFillGoogleDocFromForm(row);
    console.log("done");
  }
  catch(err) {
    console.log(err)
  }
}

function autoFillGoogleDocFromForm(e) {
  try {
    let timestamp = e.values[0];
    let photo = e.values[1];
    let date = new Date(e.values[2]);
    let event = e.values[3];
    let name = e.values[4];
    let photoCap = e.values[6];
    let photoDesc = e.values[7];

    let doc = DocumentApp.getActiveDocument();
    let body = doc.getBody();
    let i = 0;
    while( i < body.getNumChildren() ) {
      let para = body.getChild(i);
      if( para.getType() === DocumentApp.ElementType.PARAGRAPH ) {
        let j = 0;
        console.log("numchild = " body.getNumChildren());
        while( j < para.getNumChildren() ) {
          let child = para.getChild(j);
          if( child.getType() === DocumentApp.ElementType.PAGE_BREAK ) {
            // get next paragraph and check date
            if( (i 1) >= body.getNumChildren() ) break; // in case there is a page break at the end of body
            para = body.getChild(i 1);
            let temp = new Date(para.asParagraph().getText());
            console.log(temp);
            if( temp > date ) {
              body.insertPageBreak(i  );
              body.insertParagraph(i  ,date.toLocaleDateString());
              body.insertParagraph(i  ,event);
              body.insertParagraph(i  ,name);
              body.insertParagraph(i  ,photoCap);
              body.insertParagraph(i  ,photoDesc);
              return;
            }
          }
          j  ;
        }
      }
      i  ;
    }
    // if the date is latest just append a new page
    body.appendPageBreak();
    body.appendParagraph(date.toLocaleDateString());
    body.appendParagraph(event);
    body.appendParagraph(name);
    //body.appendImage(image);
    body.appendParagraph(photoCap);
    body.appendParagraph(photoDesc);
  }
  catch(err) {
    console.log(err)
  }
}