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