Blog

This article is a continuation of Infinite Dependent Dropdowns in Google Sheets.

Changes and additions to the code are described here, namely:

• Automatic creation of sheets Nome Data_2;
• Automatic addition of all formulas on sheet Nome;
• When editing the row of dependent (linked lists) created earlier, the values and formulas to the right of the edited cell are automatically deleted.

function onEdit(e) {

  var row = e.range.getRow();
  var col = e.range.getColumn();
  var list_name = e.source.getActiveSheet().getName();
  var name = e.value;
  var oldName = e.oldValue;
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var mask = JSON.stringify(sh.getRange(row, 1, 1, col).getValues()[0]);
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var colMax = sh.getLastColumn();
  
  if(list_name === "Home" && name !== oldName && col < colMax) {
    fillColumn(row, col, mask);
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Create sheets', 'createSheets')
      .addToUi();
}


function fillColumn(row, col, mask) {

// clear dataVal and Value
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  var colMax = sh.getLastColumn();
  sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();

// find date
  var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var arrData = sd.getDataRange().getValues();
  var arrData_2 = [];
  arrData_2.push(arrData[0]);
  
  var iMax = arrData.length - 1;
  for(var i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col)) == mask) {
      arrData_2.push(arrData[i]);
    }
  }
   
// clear Data_2
  var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
  sd_2.getDataRange().clearContent();

// insert data
  sd_2.getRange(1, 1, arrData_2.length, arrData_2[0].length).setValues(arrData_2);
  
// add dataVal
  col++;
  var list = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'];
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInRange(sh.getRange('Data_2!$' + list[col - 1] + '$2:$' + list[col - 1] + '$1000'), true)
  .build());
}

function createSheets() {

// is exist Home?
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sd = ss.getSheetByName('Data')
  
// create if not exist
  if(!ss.getSheetByName('Home')) {
    ss.insertSheet('Home', 0);
// create Data Val
    var sh = ss.getSheetByName('Home');
    sh.getRange('Home!A2:A20').setDataValidation(SpreadsheetApp.newDataValidation()
      .setAllowInvalid(false)
      .requireValueInRange(sh.getRange('Data!$A$2:$A'), true)
      .build());
    sh.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');

  };

// is exist Data_2?
  if(!ss.getSheetByName('Data_2')) {
   
// create if not exist
    var k = ss.getNumSheets();
    ss.insertSheet('Data_2', k + 1);
    var sd_2 = ss.getSheetByName('Data_2');
    sd_2.getRange(1, 1, 1, 10).setValues(sd.getRange(1, 1, 1, 10).getValues()).setFontWeight('bold');
  };
}

Now, in this version of the program, it is possible to automatically create all the sheets of the file necessary for the script to work (including data formatting and validation) by pressing just one(!) button from the user menu.

All you need for this:

1. create a sheet named "Data"
2. copy the data needed for linked (dependent) lists into it
3. copy and paste this script into a file
4. refresh page
5. in the appeared custom menu select: Create sheets

You can get more information from this video (RU voice):

ATTENTION!
This article has a continuation: Infinite Dependent Dropdowns in Google Sheets (part 3)

The article considers the option of creating dependent drop-down lists on the Google Spreadsheet sheet, practically unlimited neither in the number of linked elements, nor in the number of rows on the sheet.

The program script is shown below:

function onEdit(e) {

  let col = e.range.getColumn();
  let list_name = e.source.getActiveSheet().getName();
  let name = e.value;
  
  if(list_name=="Home") {
    fillColumn(col, name);
  }
}

function fillColumn(col, name) {

// find date
  var sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var iMax = sd.getLastRow();
  var col_data = [];
  
  for(var i=2; i<=iMax; i++) {
    var x = sd.getRange(i, col).getValue();
    if(x == name) {
      col_data.push(sd.getRange(i, col+1).getValue());
    }
  }
   
// clear Data_2
  var sd_2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data_2");
  sd_2.getRange(2, col+1, sd_2.getLastRow(), sd_2.getLastColumn()).clearContent();


// insert data
  iMax = col_data.length;

  for(var i=2; i<=iMax+1; i++) {
    sd_2.getRange(i, col+1).setValue(col_data.shift());
  }
}

Please note: sheet names must EXACTLY match the names specified in the script!

Main sheet: "Home"

intermediate data sheet: "Data_2"

These names in the script are highlighted in red, so, if desired, it will not be difficult to find them to replace with the names of your sheets.

This article has a continuation: Infinite Dependent Dropdowns Lists in Google Sheets (Part 2)

Now, in the new version of the program, it is possible to automatically create all the sheets of the file necessary for the script to work (including data formatting and validation) by pressing just one (!) button from the user menu.

More information you can find in this video (RU voice):

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):

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: