Indelible Google Doc Journal Entries

About a month ago, I did round one of the form-to-Google-Docs scripting post.

Since then, we’ve done some testing and things are working well. There was a desire to have the ability to rename documents. That required some rethinking. It’s also quite possible that the add commenter function wasn’t working. In any case, it had to be shifted because the way documents were found changed. It’s mostly the same but maybe useful to see some evolution.

Initially, I was just using the email name to name the file. That made finding them later for appending entries pretty easy. To allow for the file name to change, I needed to record the file ID when its first created and tie it to the submission email. I did that by adding a sheet to the Google Spreadsheet where entries go and adding that pattern to the creation process. It’s just two columns, email and doc ID. Now we’ve got a little table to reference.

    const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
    dataSheet.appendRow([email,fileId]);

Then I needed to change the search pattern so that we’d check the submission email there first. If it was found, then we’d return the document ID and append the entry rather than creating a new document. This little function does that trick.

function existsById(emailToCheck){
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
  const data = dataSheet.getDataRange();
  const values = data.getValues();
  const index = values.findIndex(subArray => subArray[0] === emailToCheck);//is email in there, if so return doc id
  if (index === -1) {
      return false;
  }
  else {
        return values[index][1];
  }
}

The whole script

//this function is set to run on each form submit and is tied to the spreadsheet where the entries go
function onSubmit(){
   let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
   let rows = sheet.getDataRange();
   let lastRow = rows.getLastRow();  
   let email = sheet.getRange(lastRow,2).getValue();
   let atSymbol = email.search('@');
   let cleanEmail = email.substring(0,atSymbol);
   let title = sheet.getRange(lastRow,3).getValue();
   let content = sheet.getRange(lastRow,4).getValue();
  makeStudentDoc(cleanEmail, title, content, email);
}

function makeStudentDoc(fileName, title, content, email){
//The folder is hardcoded via the ID
  const parentFolder = DriveApp.getFolderById('**********');
  let exists = existsById(email);
  //make base document if it doesn't exist
  if(exists === false){
    const studentDoc = DocumentApp.create(fileName);
    addCommenter(studentDoc.getId(), email);//add commenter
    let fileId = studentDoc.getId();
    const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
    dataSheet.appendRow([email,fileId]);
    DriveApp.getFileById(fileId).moveTo(parentFolder);//move to parent folder
    makeJournalEntry(fileId,title,content); //make journal entry
  } else {
      makeJournalEntry(exists,title,content); //make journal entry
  }
 
}

function makeJournalEntry(fileId, title, content) {
   let doc = DocumentApp.openById(fileId);
  // Get the body of the document
  let body = doc.getBody();

  //set title of entry
  let weekTitle = body.appendParagraph(title);
  // Set the heading style to H1
  weekTitle.setHeading(DocumentApp.ParagraphHeading.HEADING1);

  // Append the paragraph content to the document
  body.appendParagraph(content);
  
  // Save and close the document just in case
  doc.saveAndClose();
}


function existsById(emailToCheck){
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
  const data = dataSheet.getDataRange();
  const values = data.getValues();
  const index = values.findIndex(subArray => subArray[0] === emailToCheck);//is email in there, if so return doc id
  if (index === -1) {
      return false;
  }
  else {
        return values[index][1];
  }
}



function addCommenter(fileId, email) {
  let file = DriveApp.getFileById(fileId);  
  file.addCommenter(email);
}