How to automatically insert data into Google Docs using Google Apps Script?

Very often there is a need to create typical documents where only the data of the contractors change: contracts, powers of attorney, commercial offers, etc.

Quite logical solution in such situation:

• to create the document templates Google Docs where the data of the contractors will be inserted;
• to create the document Google Sheets where the data of the contractors will be stored and the references to the document templates,
• and to write the script which will manage the process of the selection of the contractors and the insertion of the data of the selected contractors into the document templates.

The idea of finding a place to insert and then inserting the necessary value to this place is to create a unique character set on Google Doc sheet that could be uniquely identified and replaced using the text replacement method replaceText:

body.replaceText("{unique label}", "klient data");

Let's start with the table. In our example, we have made three information columns: the name of the client {name}, {id}, the date of issue {id_dateOfIssue}. To make it easier to edit the template column names, we will name them in a similar way as the label names in Google Docs. And, since these are placeholders (places of insertions), we decide from the very beginning that we always take placeholders in curly brackets.

In addition to the information columns, there are also “service columns” that help to realize the interface of our program: FROM whom the power of attorney is issued (who concludes the contract), TO whom the power of attorney is issued (with whom the contract is concluded) and which TEMPLATE of the document is used.

The following version of the power of attorney is used as the document template to which the script refers.

In order to distinguish between the principal and attorney placeholders, the element of the text [principal_] was added to the placeholders of the principal and [attorney_] were added to the placeholders of the attorney.

The script below implements the following tasks:

• identification of selected clients and reading the data of these clients from Google Sheets;
• finding the selected template in Google Drive, creating a copy of the selected template and saving it under a new name;
• opening a document (a newly created copy of the template) and replacing placeholders with the contractors’ data.

function DocGenerator() {

  var sc = SpreadsheetApp.getActiveSheet();
  
  //find clients info
  
  var principalIndex = getClientIndx(1);
  var attorneyIndex = getClientIndx(2);
  var principal = getClientData(principalIndex);
  var attorney = getClientData(attorneyIndex);
  
  // find template
  var docName = sc.getRange(principalIndex, 3).getValue();
  var docID = getTemlateID(docName);
  var docNameCopy = docName + "_" + principal.name + "_" + attorney.name;
  var file = DriveApp.getFileById(docID);
  file.makeCopy(docNameCopy);
  var fileCopy = DriveApp.getFilesByName(docNameCopy).next();
  var fileCopyID = fileCopy.getId();
  
  
  // replacement
  var body = DocumentApp.openById(fileCopyID).getBody();
  body.replaceText('{principal_name}', principal.name);
  body.replaceText('{principal_id}', principal.id);
  body.replaceText('{principal_id_dateOfIssue}', principal.id_dateOfIssue);
  body.replaceText('{attorney_name}', attorney.name);
  body.replaceText('{attorney_id}', attorney.id);
  body.replaceText('{attorney_id_dateOfIssue}', attorney.id_dateOfIssue);
  
  Browser.msgBox("Completed!");    
  
}

function getClientIndx(col) {

  var sc = SpreadsheetApp.getActiveSheet();
  var iMax = sc.getLastRow();
  var found = false; 
  
  for(var i=2; i <= iMax; i++) {
    
    if(sc.getRange(i, col).getValue() == true) {
      found = true;
      break;    
    }
 
  }
  
  if(found == true) {
    return i;
  } else {
    Browser.msgBox("Please select at least one client!")
  }

}

function getClientData(indx) {

  var sc = SpreadsheetApp.getActiveSheet();
  var client = {
    name: sc.getRange(indx, 4).getValue(),
    id: sc.getRange(indx, 5).getValue(),
    id_dateOfIssue: sc.getRange(indx, 6).getValue(),
    };
  return client;
}

function getTemlateID(docName) {

  var st = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Templates');
  var iMax = st.getLastRow();
  
  for(var i=2; i <= iMax; i++) {
    
    if(st.getRange(i, 1).getValue() == docName) {
      var docId = st.getRange(i, 2).getValue();
      return docId;    
    }
 
  }

}

You can get more detailed explanations of the script from this video:

To continue, see the article: