Google Apps Script Document Generator with Document Log

This article is a continuation of another one: How to automatically insert data into Google Docs using Google Apps Script?, which describes how to create a copy of a Google Doc document template and insert new data from a Google Spreadsheet into it.

Here we will consider a new version of the document generation program, which has undergone a number of significant changes.

First of all, the changes are connected with the fact that sooner or later, in any more or less decent paperwork, the question of accounting for the created documents inevitably arises. Therefore, in the new version of the program, a register was created for the created (generated) documents, where 2 new fields appeared: document number {document_id} and the date of the document {document_date}. (In this version of the program, the data of the new fields are changed manually). Of course, if desired, this list can be substantially supplemented. The main thing is not to forget to carefully copy the unique characters of the new fields to the appropriate places to insert Google Docs templates.

The logbook was decided to be made on the main sheet ("Main"). Which significantly changed the program interface. Counterparty data has moved to the new "Clients" sheet:

And the fields with check boxes for selecting FROM and TO have been replaced by fields with a list of counterparties associated with the "Clients" sheet.

Another script launch option has also been added from the user menu (My menu), which is created automatically when the document is opened.

Before running the script, you now need to select in the new line of contractors FROM and TO , the name of the Google Docs template, document number and document date. And the most important thing to remember is that in the new script the document is generated, in the line of which the active cell is located.

A completely updated program script is presented below:

//create user's menu
function onOpen() {
  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("My menu")
    .addItem('Create Document', 'DocGenerator')
    .addToUi()
}

 //main function
function DocGenerator() {

  var sm = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main");
     
  //find clients info
  var row_active = sm.getActiveCell().getRow();
  var principal_shart_name = sm.getRange(row_active, 1).getValue();
  var attorney_shart_name = sm.getRange(row_active, 2).getValue();
  var principal = getClientData(principal_shart_name);
  var attorney = getClientData(attorney_shart_name);
  
  //find documents info
  var document = {
    id:sm.getRange(row_active, 4).getValue(),
    date:sm.getRange(row_active, 5).getValue(),
    }
  
  // find template
  var docName = sm.getRange(row_active, 3).getValue();
  var docID = getTemlateID(docName);
  var docNameCopy = docName +"_" + document.id +"_" + document.date + "_" + 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('{document_id}', document.id);
  body.replaceText('{document_date}', document.date);
  
  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 getClientData(sh_name) {

  var sc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Clients");
  var iMax = sc.getLastRow();
  
  for(var i=2; i <= iMax; i++) {
    
    if(sc.getRange(i, 1).getValue() == sh_name) {
      break;    
    }
  }

  var client = {
    short_name: sc.getRange(i, 1).getValue(),
    name: sc.getRange(i, 2).getValue(),
    id: sc.getRange(i, 3).getValue(),
    id_dateOfIssue: sc.getRange(i, 4).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 (Russian voiceover):