Google-apps-script – Automatically update inserted sheets cells in Google Docs

This answer is based on the work of user79865.

You have a Google Form which exports results to a Google Sheet; you have embeded the sheet in a Google Doc and you are looking for a way to update the Google Doc automatically whenever there is a new form response.

Your question is substantially answered in webapps question: Embed Google Spreadsheet table Withing Google Document when data contains dates which in turn was based on this webapps question Embed Google Spreadsheet in Google Document.

Your scenario differs in two ways:

1 - you want to update the Google document for every new form response, and
2 - you want the update to be automatic.

Installation

You have a Google Form, Google Spreadsheet and a Google Document.

• Google Document
  • make a note of the document ID
• Google Form
  • select your spreadsheet as the response destination
• Google Spreadsheet
  • Open the sheet's Script Editor, rename the project as appropriate, and copy the script shown below. You can rename the script if you wish.
  • var sheetName = "<<insert sheet name>>";: edit the script to include the actual name of the sheet containing the form responses - (put the sheet name between the quotes)
  • var docid = "<<insert document ID>>";: edit the script to include the actual document ID - (put the ID between the quotes)
  • save the modified script
  • Trigger: from the menu, select "Edit, Current Projects Triggers". In the new tab that opens
    • Under "Choose which function to run", select the name of the function.
    • Under "Select event type", select "On form submit"
    • Save the trigger

Processing

• When a form submission is made, the spreadsheet will be updated.
• the trigger will execute the function.
• the function looks for a named range called "embeddedSheet-range" (you can change this if you wish).
• when the function executes for the first time, the range isn't found, so the function creates the range and inserts the table.
• when the functions executes thereafter, it finds the named range and replaces the it with the updated data.
• the function will replace any existing data with new data

function wa14658402() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "<<insert sheet name>>";   // name of sheet to use
  var sheet = ss.getSheetByName(sheetName);
  
  //get the last r and column of data
  var sheetLR = sheet.getLastRow();
  var sheetLC = sheet.getLastColumn();
  
  // define the range containing the data
  // this can be changed depending on which columns to embed
  var range = sheet.getRange(1, 1, sheetLR, sheetLC);
  // Logger.log("DEBUG: the range is "+range.getA1Notation()); 
  
  // note - this is a change mentioned in 
  // Embed Google Spreadsheet table Withing Google Document when data contains dates
  // when the data includes a data, "getValues()" fails hence
  // changing the method to "getDisplayValues()"
  var values = range.getDisplayValues(); 
  
  // get the document details
  var docid = "<<insert document ID>>";
  var doc = DocumentApp.openById(docid);
  
  var body = doc.getBody();
  var ranges = doc.getNamedRanges('embeddedSheet-range');
  
  if (ranges.length == 0) {
    //  Logger.log("DEBUG: ranges is zero")
    var table = body.appendTable(values);
  }
  else {
    // Logger.log("DEBUG: ranges is NOT zero")
    tableRange = ranges[0];
    table = tableRange.getRange().getRangeElements()[0].getElement();
    var ind = body.getChildIndex(table);
    tableRange.remove();
    body.removeChild(table);
    table = body.insertTable(ind, values);
  }
  var rangeBuilder = doc.newRange();
  rangeBuilder.addElement(table);
  doc.addNamedRange('embeddedSheet-range', rangeBuilder.build());
}