push table info in google doc back to linked google sheet

Question

Is there a way to update a google sheet from a google doc that has been linked? Basically, I have a template google doc with a linked google sheet table. I have a script that makes a copy of the google doc when a form is submitted. The variables are then replaced by the appropriate form data.

When I'm ready, I update the google sheet with individual info and update the google doc. This works fine until I need to make changes to an older google doc ... but since my google docs are linked to the same sheet, I have to re-enter the info in the google sheet from the older google doc rather than just editing the google sheet. Is there a way to push data back from the google doc to the google sheet?

Or is there a way to insert & link a table from google sheet into a google doc with apps script? The only way I've found to link a sheet to a doc is using copy & paste ...

Answer 1

There isn't a great answer. Sadly, the refresh feature is not exposed in the Doc api. However you can use Apps Script to copy the data from the sheet into the doc. It does lose the formatting, so you'll have to reset the formatting also, as my example does below.

function copyDataFromSheetToDoc() {
  var sheet = SpreadsheetApp.openById('1R0a4aj1iJA8IxlgYRMjsM7DytPItQxy3be7W55mitr4');
  var data = sheet.getSheetByName('Sheet1').getRange("A1:C4").getValues();
  var doc = DocumentApp.openById('1pMWRr_QbRlObLeulM0DUHZRCc1K6RCch9FMLOM0sSN0');
  var body = doc.getBody();
  // Update the first table.
  var table = body.getTables()[0];

  var headerStyle = {};
  headerStyle[DocumentApp.Attribute.BOLD] = true;
  headerStyle[DocumentApp.Attribute.FONT_SIZE] = 10;
  var bodyStyle = {};

  for (var r = 0; r < data.length; ++r) {
    var row = data[r];
    for (var c = 0; c < row.length; ++c) {
      var cell = table.getCell(r, c);
      cell.setText(row[c]);
      if (r == 0) {
        cell.setAttributes(headerStyle);
      } else {
        cell.setAttributes(bodyStyle);
      }
    }
  }
  doc.saveAndClose();
}