google-sheetsgoogle-apps-scriptgoogle-docs

Insert Form Responses Value to Table In Google DOC


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

What I want is the flow:

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();
    };

Tabel Result 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.


Solution

  • 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.