Adding approval step for Google Form entries ->Google Sheet/Doc

Question

I'm hoping to combine this open letter/Google Suite workflow (https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/) with an extra approval step that requires some form of approval (e.g. a checkbox in the Google Sheet, or some sort of edit/change in a Google Sheet) that would then and only then relay the information to the final Google Doc. I've been able to use the code from Jeffrey Everhart (see below) to successfully set up a Google Form -> Google Sheet -> Google Doc open letter workflow that takes Google Form information and adds it to the Google Doc on form submit. But I have not been able to make the script work for on edit/on change triggers, despite pulling out my hair trying all day yesterday. That would be ideal, because I could simply add a Checkbox "Approval" column in the google sheet that signals whether a form submission should proceed to the next step of being added to the Google Doc. Any suggestions?

function appendSignatureRow(e) {

  //Since there could be a bunch of people submitting, we lock the script with each execution
  //with a 30 second timeout so nothing gets overwritten
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  //Here we read the variables from the form submission event
  const date = new Date(e.values[0]).toLocaleDateString();
  //of you can use toLocaleString method if you want the time in the doc
  const name = e.values[1];
  const affiliation = e.values[2];
  const country = e.values[3];


  //Next format those values as an array that corresponds to the table row layout
  //in your Google Doc
  const tableCells = [name, affiliation, country, date]



  //Next we open the letter and get its body
  const letter = DocumentApp.openById('INSERT ID HERE')
  const body = letter.getBody();

  //Next we get the first table in the doc and append an empty table row
  const table = body.getTables()[0]
  const tableRow = table.appendTableRow()

  //Here we loop through our table cells from above and add
  // a table cell to the table row for each piece of data
  tableCells.forEach(function(cell, index) {
    let appendedCell = tableRow.appendTableCell(cell)
  })


  //here we save and close our letter and then release a lock 
  letter.saveAndClose();
  lock.releaseLock();

}

Here's the Google Sheet format I'm using, with column F being the column where I hope to add the Approval checkmark on edit trigger that then triggers the script to pick up at the "const letter = DocumentApp.openById('INSERT ID HERE')" line.

I'm new to Google App Scripts, so I appreciate the assistance!

Answer 1

How about you use the onFormSubmit to add checkbox to each entry in your Google Forms and use onEdit to validate checkbox and write to Google Docs.

Here I created a function for onFormSubmit that will append checkbox to the new entries and modified some parts of appendSignatureRow() and use it as function for onEdit trigger to write to Google Docs if the entry is checked.

Example:

Forms:

Spreadsheet Data:

Installable Triggers Setup:

Code:

function formSubmit(e){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; //get form response sheet
  var col = e.range.getLastColumn();
  var row = e.range.getLastRow();
  sheet.getRange(row, col+1).insertCheckboxes(); // add checkbox
}

function appendSignatureRow(e) {
  var sheetName = e.source.getSheetName(); //get sheet name
  var editedRow = e.range.getRow(); // get edited row number
  var editedCol = e.range.getColumn(); //get editer column number
  if(editedRow > 1 && editedCol == 6 && sheetName == 'Form Responses 1' && e.value == 'TRUE'){
    const lock = LockService.getScriptLock();
    lock.waitLock(30000);
    var sheet = e.source.getSheetByName('Form Responses 1');
    var data = sheet.getRange(editedRow, 1, 1, editedCol -1).getValues()[0]; // get edited row data
    const date = new Date(data[0]).toLocaleDateString(); 
    const name = data[1];
    const affiliation = data[2];
    const country = data[3];

    const tableCells = [name, affiliation, country, date]

    const letter = DocumentApp.openById('Insert Docs ID here')
    const body = letter.getBody();

    const table = body.getTables()[0]
    const tableRow = table.appendTableRow()

    tableCells.forEach(function(cell, index) {
      tableRow.appendTableCell(cell)
    })
    letter.saveAndClose();
    lock.releaseLock();
  }  
}

Output:

Reference:

• Event Object
• Installable Trigger