Instead of OnFormSubmit, is there a way to take a row from Google Sheets to merge with a Google Doc using AppsScript?

Question

I know how to merge data from a Google Form submission to a Google Sheet to a Google Document using the trigger OnFormSubmit.

What I want to know is there a way to take a Row of data from a Google Sheet and merge with the same or similar Google Document without going through the Form?

I'm still learning about AppScripts and it seems like it would be easy, but I can't find anything through google searches.

I know that I need to specify the active row, etc and then I need to figure out how to get my e.values or other variables from the active row.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var getActiveRow = [{name: "getActiveRow", functionName: "fetchActiveRow"}];
     ss.addMenu("customMenu", getActiveRow);
};

function fetchActiveRow(e){ 
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var row = ss.getActiveSheet().getActiveRange().getRow();

I expect to take row data and merge with a Google Document so it fills out the "paper form" in the appropriate spaces.

Any help would be greatly appreciated.

Answer 1

A Simple Google Doc Envelope Printer

Well here's a simple number 10 Envelope Printer. The code is in a Google Document but it accesses a Spreadsheet to store addresses. You can enter the addresses in the sidebar or simply type them into the spreadsheet which I personally find much easier.

EnvelopePrinter.gs:

var DSSID=getDSSID();

function onOpen(e)
{
   makeEnvelopeMenu()
}

function onInstall(e){
  setupEnvelopeApp();
  setupDocument();
  onOpen(e);
}

function makeEnvelopeMenu()//Rename this to onOpen if this is all you have in this document
{
  DocumentApp.getUi().createMenu('Envelope Printer')
    .addItem('Show Envelope Sidebar', 'displayEnvelopeSidebar')
    .addSubMenu(DocumentApp.getUi().createMenu('Initial Setup')
        .addItem('Initialize Spreadsheet Id for Email Address', 'setupEnvelopeApp')
        .addItem('Setup Document', 'setupDocument')       )
    .addToUi();
}

function setupEnvelopeApp()//To make the connection between this document and the spreadsheet you created to store mailing addresses run this script from Script Editor
{
  var resp=DocumentApp.getUi().prompt('Mailing Address SpreadsheetId', 'Create a spreadsheet to store mailing address and enter the id of that spreadsheet here', DocumentApp.getUi().ButtonSet.OK);
  var dssid=resp.getResponseText();
  var props=PropertiesService.getScriptProperties();
  props.setProperty('DSSID', dssid);
  var ss=SpreadsheetApp.openById(getDSSID());
  var sh=ss.getSheetByName('Addresses');
  if(!sh) {
    var sh=ss.insertSheet('Addresses');
  }
  sh.appendRow(['Line 1','Line 2','Line 3','Line 4']);
  sh.getRange(1,1,1,4).setFontWeight("Bold");
}

function displayDSSID(){
  DocumentApp.getUi().alert('The Id of the spreadsheet that stores emails is ' + getDSSID());
}

function resetDSSID() {
  PropertiesService.getScriptProperties().setProperty('DSSID', '');
}

function getDSSID(){
  return PropertiesService.getScriptProperties().getProperty('DSSID');
}

function setupDocument(){
  var doc=DocumentApp.getActiveDocument();
  var envelope10={};
  envelope10[DocumentApp.Attribute.PAGE_HEIGHT]=296;
  envelope10[DocumentApp.Attribute.PAGE_WIDTH]=684;
  envelope10[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
  envelope10[DocumentApp.Attribute.FONT_SIZE] = 14;
  envelope10[DocumentApp.Attribute.BOLD] = true;
  envelope10[DocumentApp.Attribute.LINE_SPACING]=1;
  envelope10[DocumentApp.Attribute.MARGIN_LEFT]=36;
  envelope10[DocumentApp.Attribute.MARGIN_RIGHT]=36;
  envelope10[DocumentApp.Attribute.MARGIN_TOP]=36;
  envelope10[DocumentApp.Attribute.MARGIN_BOTTOM]=36;
  doc.getBody().clear().setAttributes(envelope10);
}

function insertReturnAddress(retaddr){
  var retaddr=(typeof(retaddr)!='undefined')?retaddr:'No return address selected.';
  var retAddrStyle={};
  retAddrStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
  retAddrStyle[DocumentApp.Attribute.FONT_SIZE] = 14;
  retAddrStyle[DocumentApp.Attribute.BOLD] = true;
  retAddrStyle[DocumentApp.Attribute.LINE_SPACING]=1;
  retAddrStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT]=DocumentApp.HorizontalAlignment.LEFT;
  retAddrStyle[DocumentApp.Attribute.MARGIN_TOP]=0;
  var doc=DocumentApp.getActiveDocument();
  doc.getBody().getChild(0).asParagraph().setAttributes(retAddrStyle).setText(retaddr);
}

function insertRecipientAddress(recaddr){
  var recaddr=(typeof(recaddr)!='undefined')?recaddr:'No Recipient Address selected.';
  var retAddrStyle={};
  retAddrStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
  retAddrStyle[DocumentApp.Attribute.FONT_SIZE] = 14;
  retAddrStyle[DocumentApp.Attribute.BOLD] = true;
  retAddrStyle[DocumentApp.Attribute.LINE_SPACING]=1;
  retAddrStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT]=DocumentApp.HorizontalAlignment.CENTER;
  retAddrStyle[DocumentApp.Attribute.MARGIN_TOP]=0;
  var doc=DocumentApp.getActiveDocument();
  doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
  doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
  doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
  doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
  doc.getBody().appendParagraph(recaddr).setAttributes(retAddrStyle);
}

function prepareEnvelope(retaddr,recaddr){
  setupDocument();
  insertReturnAddress(retaddr);
  insertRecipientAddress(recaddr)
}

function displayEnvelopeSidebar(){
  var userInterface=HtmlService.createHtmlOutputFromFile('Envelope').setWidth(300).setHeight(500).setTitle('Printing Envelopes');
  DocumentApp.getUi().showSidebar(userInterface);
}

function savAddress(addr){
  var ss=SpreadsheetApp.openById(DSSID);
  var sh=ss.getSheetByName('Addresses');
  if(sh.appendRow(addr))
  {
    return true;
  }
  else
  {
    return false;
  }
}

function getAllAddresses(){
  var ss=SpreadsheetApp.openById(DSSID);
  var sh=ss.getSheetByName('Addresses');
  var rg=sh.getRange(2,1,sh.getLastRow(),sh.getLastColumn());
  var vA=rg.getValues();
  return vA;
}

Envelope.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <style>
    #Instructions{padding:4px;}
    </style>
  <script>
    $(function() {
        clearAddressFields();
        google.script.run
          .withSuccessHandler(putNamesAndAddresses)
          .getAllAddresses();

        google.script.run
          .withSuccessHandler(function(ssid){
            document.getElementById('Instructions').innerHTML='Insert Envelope into Printer with front facing up and top facing to the left when printer is in front of you. Folded portion down. Use the Com-10 setting for page size.<br /><span style="font-size:8px;"><strong>SSID:</strong>'+ ssid +'</span>'
          })
          .getDSSID();
      });

      function putNamesAndAddresses(data)
      {
        var select1=document.getElementById("sel1");
        var select2=document.getElementById("sel2"); 
        var lf='\n';
        select1.options.length=0;
        select2.options.length=0;
        for(var i=0;i<data.length;i++)
        {
          var name=data[i][0];
          var addr='';
          for(var j=0;j<data[i].length;j++)
          {
            if(j>0 && data[i][j]){addr+=lf;}
            addr+=data[i][j];
          }
          select1.options[i]=new Option(name,addr);
          select2.options[i]=new Option(name,addr);
        }
      }

      function saveAddress()
      {
        $('.addrinp').css('background-color','#ffff00');
        var line0=$('#adr0').val();
        var line1=$('#adr1').val();
        var line2=$('#adr2').val();
        var line3=$('#adr3').val();
        var addr=[line0,line1,line2,line3];
        google.script.run
          .withSuccessHandler(clearAddressFields)
          .savAddress(addr)
      }

      function clearAddressFields()
      {
        $('.addrinp').css('background-color','#ffffff');
        $('#adr0').val('');
        $('#adr1').val('');
        $('#adr2').val('');
        $('#adr3').val('');
        google.script.run
          .withSuccessHandler(putNamesAndAddresses)
          .getAllAddresses();
      }

      function prepareEnvelope()
      {
        var recaddr=$('#sel1').val();
        var retaddr=$('#sel2').val();
        google.script.run.prepareEnvelope(retaddr,recaddr);
      }

      console.log('My Code');
    </script>
  </head>
  <body>
  <div id="Instructions"></div>
  <div id="envprep" style="border-style: double;padding:0 0 0 10px;">
  <h3>Recipient Address:</h3>
  <select id="sel1" size="5">
  </select>
  <h3>Return Address:</h3>
  <select id="sel2" size="5">
  </select>
  <br /><br /><input type="button" id="btn2" value="Address Envelope" onClick="prepareEnvelope();" />
  </div>
  <div id="newaddr" style="border-style: double;padding:0 0 0 10px;">
  <h3>Add Address:</h3>
  <input class="addrinp" id="adr0" type="text" size="30" placeholder="1st Line of Address" />
  <br /><input class="addrinp" id="adr1" type="text" size="30" placeholder="2nd Line of Address" />
  <br /><input class="addrinp" id="adr2" type="text" size="30" placeholder="3rd Line of Address" />
  <br /><input class="addrinp" id="adr3" type="text" size="30" placeholder="4th Line of Address" />
  <br /><br /><input id="btn1" type="button" value="Save Address" onClick="saveAddress();" />
  </div>
  <br /><br /><br /><input id="btn0" type="button" value="Close" onClick="google.script.host.close();" />
  </body>
  </html>

Run the initial setup steps. One of them is to provide the file id for a spreadsheet when it will get mailing addresses. It will open the Spreadsheet based upon the ID you provide and it will create an Addresses sheet and write the Header Line. All you have to do is enter address into that page via the side bar or directly on the Spreadsheet.

Image of Envelope Printer Google Document

Spend some time reading the Google Apps Script reference to figure out what's going on in the code.

Have fun.