Insert Form Responses Value to Table In Google DOC

Question

I'm working with Google Apps Script. I want to make an Activity Journal Recap that is connected to

• Google Form to collect data
• Google Spreadsheet to accommodate data input
• Google Doc to recap the Journal

What I want is the flow:

• User fills in Google Form
• Enter the entries into Google Spreadsheet
• Incoming data is added to the table in Google Doc, with the last Form Input order being in row number 2 (after the column title).

This is my Script :

   function onFormSubmit(e) {
     var docId    = '1b9oFJmKhcFpez4yIYmrsCG6zKOZh6_b-xxxxxxx';
     var formData = e.values;

     var columnMapping = {
        1: 0,  // Try using formData[1] for the 1st column
        2: 1,  // Try using formData[2] for the 2nd column
        3: 2,  // Try using formData[3] for the 3rd column
        };

     var doc  = DocumentApp.openById(docId);
     var body = doc.getBody();

     var table   = body.getTables()[0];
     var numCols = table.getRow(0).getNumCells();

     var newRow  = table.insertTableRow(1);
        for (var i = 0; i < numCols; i++) {
        newRow.insertTableCell(i, '');  // Insert blank cells
     }

    // Add data to all columns according to the mapping
     for (var sourceCol in columnMapping) {
     var targetCol = columnMapping[sourceCol];
       if (targetCol < numCols) {
     var dataToInsert = formData[sourceCol - 1];
       newRow.getCell(targetCol).setText(dataToInsert);
     }
    }
       doc.saveAndClose();
    };

Unfortunately, I was unable to retrieve the data from the spreadsheet. I can only get data from the first column in the spreadsheet to the Table in Google DOC, even though I can move to any column in the Table in Google DOC, but only one data can be retrieved.

Thank you if anyone is willing to help me.

Answer 1

Below is a proposal for a function to append a row to a Google Docs table. This function call is called from a function that has got the form submitted values from a function reading the row values from the spreadsheet.

/** Add here the target document ID*/
const docId = '';

function test_appendRowContent(){
  const rowContent = ['A', 'B', 'C', 'D'];
  appendRowContent(rowContent);
}

/**
 * @param {string[]} rowContent Content of the new row to append to the first table.
 * @param {number} [tableIndex] Table index. 
 * @param {number} [rowIndex] Template row index.
 */
function appendRowContent(rowContent, tableIndex = 0, rowIndex = 0) {
  const doc = DocumentApp.openById(docId);
  const body = doc.getBody();
  const table = body.getTables()[tableIndex];
  const templateRow = table.getChild(rowIndex);
  /**
   * Copy a row. 
   * It should have the same number of cells as the rowContent elements
   */
  const row = templateRow.asTableRow().copy();
  // Clear old content and add new content
  for(let i = 0; i < row.getNumCells(); i++){
    const cell = row.getChild(i);
    cell.setText(rowContent[i]);
  }
  // Append row to table
  table.appendTableRow(row);

}

To insert the row, you can quickly adapt the above code by replacing table.appendTableRow(row); with table.insertTableRow(1, row).

To call the above function from on function handler for a form submit trigger

function formSubmitHandler(e){
   appendRowContent(e.values);
}

Ensure the form has the same questions as the table number of columns.

---

Regarding the code in question, as a function called by an on-form submit trigger from a spreadsheet, the e.values already has the form-submitted data appended to the spreadsheet.

The for-in statement is a convoluted attempt to do something that could be more easily done with a for statement.

It's worth noting that the DocumentApp.Document.saveAndClose() method is not required at the end of the function, as all the changes are applied when the executions finish.