Blog

Short description of the method

The .filter() method sequentially compares all elements of the array with the filter condition specified in the callback function. That is, the method, in fact, is a for cycle, inside which the callback function sequentially processes all elements of the array.

The .filter() method can pass three parameters to a callback function:

1. the current value of the array element item
2. optional parameter index - index of the current element
3. optional and rarely used parameter array

In order for an element satisfying the filter condition to be selected, the callback function must return the value true .

All elements for which the callback function returned the value true are collected in a separate array, which can be stored in a new array (in the examples below it is an array result2 ).

In the three variants of the example presented below (examples #1, #2, #3), the same filter condition selects all array elements with a value > 3.

In the last example (the most optimal in terms of code brevity), the logical expression instead of the if operator is set directly to return . Since, as you know, a logical expression can take only 2 values: true или false.

const arr = [1, 2, 9, 4, 1, 6, 5];
    
    let result = arr.filter((item, idx, arr) => console.log(item, idx, arr));
//  [20-06-15 14:53:21:082 EEST] 1 0 [ 1, 2, 9, 4, 1, 6, 5 ]
//  [20-06-15 14:53:21:084 EEST] 2 1 [ 1, 2, 9, 4, 1, 6, 5 ]
//  [20-06-15 14:53:21:085 EEST] 9 2 [ 1, 2, 9, 4, 1, 6, 5 ]
//  [20-06-15 14:53:21:086 EEST] 4 3 [ 1, 2, 9, 4, 1, 6, 5 ]
//  [20-06-15 14:53:21:087 EEST] 1 4 [ 1, 2, 9, 4, 1, 6, 5 ]
//  [20-06-15 14:53:21:089 EEST] 6 5 [ 1, 2, 9, 4, 1, 6, 5 ]
//  [20-06-15 14:53:21:090 EEST] 5 6 [ 1, 2, 9, 4, 1, 6, 5 ]
    
    console.log(result);
//  [20-06-15 14:53:21:091 EEST] []  

// variant #1
  let result2 = arr.filter(item => {
      if (item > 3) {
        return true;
      } else {
        return false;
      };
    });
    
// variant #2
    result2 = arr.filter(item => {
      return item > 3;
    });


// variant #3
    result2 = arr.filter(item => item > 3);
    
    console.log(result2); // [ 9, 4, 6, 5 ]
    
    var sf = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    let data = sf.getDataRange().getValues().slice(1);
    
    console.log(data);
//  [ [ '', false, 'apples', 5 ],
//    [ '', true, 'carrots', 12 ],
//    [ '', false, 'grapes', 4 ],
//    [ '', false, 'plums', 3 ],
//    [ '', true, 'strawberry', 9 ],
//    [ '', false, 'perches', 4 ],
//    [ '', false, 'bananas', 1 ] ]   

    let newData = data.filter(item => item[1]);

    console.log(newData); 
// [ [ '', true, 'carrots', 12 ], 
//   [ '', true, 'strawberry', 9 ] ]

And another useful script that was not included in this video is a script that filters ONLY unique values.

The idea is very simple: for each element of the array, its index is found and compared with the current index of the value of this element in the array. If the element has not been encountered earlier, then the values of the indices will be equal, and the function uniqValue will return true .

In the first example, for a better understanding of the algorithm, the uniqValue function is written separately. In the second, it is directly "embedded" into the .filter method

let arrayNotUniq = [1, 5, 9, 5];

//variant #1

function uniqValues(item, index, arr) {
  return arr.indexOf(item) === index;
}

let arrayUniq = arrayNotUniq.filter(uniqValues)                        // [1, 5, 9]



//variant #2

  let arrayUniq = arrayNotUniq.filter((item, index, arr) => {
    return arr.indexOf(item) === index;
  });                                                                                                     // [1, 5, 9]

You can find more information in this video (RU voice):

Short description of the method

The .map() method sequentially iterates over all elements of the array. That is, in essence, it is a for loop, where the loop variable changes from the index of the first element to the last.

This method can pass 3 elements to a callback function:

1. the current value of the array element currentValue (in the example #2, the variable x )
2. optional parameter index - index of current item (in the example #2, the variable y )
3. optional and rarely used parameter array (in the example #2 the variable z )

The first example #1 multiplies each element of the array by 2. That is, the callback function each time returns an element of the array multiplied by 2.

The second example #2 studies the parameters of the method. Therefore, the callback function does not return anything, but only prints the parameters passed to it. In each cycle it is: the value of the array element, its index and the array itself.

var arr = [1, 2, 3];

  // example #1
  var arr2 = arr.map(x => x * 2); // [ 2, 4, 6 ]

  // example #2
  var arr2 = arr.map((x, y, z)  => {
    console.log(x + "|" + y + "|" + z);
  }); 
 
//  1|0|1,2,3
//  2|1|1,2,3
//  3|2|1,2,3

Refactoring code using the .map () method

The scripts below process the same array in two ways:

1.) using the for loop

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var arrM = ss.getRange(1, 1, 10).getValues(); // [[10. Will Smith: $35 million], [9. Paul Rudd: $41 million], [8. Chris
  var arrW = ss.getRange(14, 1, 10).getValues(); // [[10. Ellen Pompeo: $22 million], [9. Charlize Theron: $23 million], [8. 
  
  var arr = arrM.concat(arrW);
  
  for (var i = 0; i < arr.length; i++) {
    arr[i][0] = arr[i][0].replace(":", "").replace("$", "").replace(" (tie)", "(tie)").replace(" Jr", "Jr"); // 
    arr[i] = arr[i][0].split(' '); // [10., Will, Smith, 35, million]
    arr[i][1] = arr[i][1] + " " + arr[i][2]; // [10., Will Smith, Smith, 35, million]
    arr[i].splice(2, 1); // [10., Will Smith, 35, million]
    if (i < 10) {
      arr[i][3] = 'man';
    } else {
      arr[i][3] = 'woman';
    };
  };
  
  arr.sort(function(a, b) {
    return b[2] - a[2];  
  });

  for (var i = 0; i < arr.length; i++) {
    arr[i][0] = i + 1;
    Logger.log(arr[i]);
  };
  
  ss.getRange(2, 6, arr.length, arr[0].length).setValues(arr);

2.) using .map() method:

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var arrM = ss.getRange(1, 1, 10).getValues(); // [[10. Will Smith: $35 million], [9. Paul Rudd: $41 million], [8. Chris
  var arrW = ss.getRange(14, 1, 10).getValues(); // [[10. Ellen Pompeo: $22 million], [9. Charlize Theron: $23 million], [8. 
  
  var arr = arrM.concat(arrW);
  
  var arr2 = arr.map(x => x[0].replace(":", "").replace("$", "")
                              .replace(" (tie)", "(tie)").replace(" Jr", "Jr")
                              .split(' '));  // [ [ '10.', 'Will', 'Smith', '35', 'million' ],
  
  arr = arr2.map(x => [x[2], x[1] + ' ' + x[2], x[3], x[4]]);
  arr = arr.map((x, i) => {
    (i < 10) ? x[3] = 'man' : x[3] = 'woman' ;
    return x;
  });   //  [ [ 'Smith', 'Will Smith', '35', 'man' ],
  
  arr.sort((a, b) => a[0].localeCompare(b[0])); // [ [ '1.', 'Dwayne Johnson', '89.4', 'man' ],
  
  arr = arr.map((x, i) => {
    x[0] = i + 1;
    return x;
  });
  
 
  console.log(arr);
  ss.getRange(2, 6, arr.length, arr[0].length).setValues(arr);

You can find more information in this video (RU voice):

Introduction.

Before talking about .sort method is need to say a few words about the functions used in JavaScript and Google Apps Script.

Higher-order functions - these are functions that use first-class functions as arguments and / or return objects. (An example is the function .sort )

First-class functions are used as arguments and / or return objects for higher-order functions. (An example is functions that are arguments to the .sort function)

const arr = [1, 2, 3, 10, 20];
  
// ========== Higher-order functions ===============

  //arr.sort(sortFunction)
  
// ========== First-class functions ================

  // function definition 
  function twoTimes(x) {
    return x * 2;
  }; 
  
  // another function definition
  var twice;
  twice = function(x) {return x * 2};
  
  // arrow function
  twice = (x) => x * 2;
  
  // arrow function with 1 argument
  twice = x => x * 2;
  
  console.log(twice(3));
  console.log(typeof twice);
  
  // 1.) function as argument another function
  var fourTimes = (x, twice) => twice(x);
  
  console.log(fourTimes(3, twice)); // 6
  
  //  2.) return function
  var fourTimes = (x) => x * 4;
  
  var xTimes = (x, order) => {
    if (x / order == 4) {
      return fourTimes;
    } else if (x / order == 2) {
      return twice;
    };
  };
  
  var y = xTimes(12, 3); // 8

  console.log(y(2)); // =6
  console.log('y is ' + typeof y); // y is function

Array Sorting Examples

// numbers sorting
  
  const arr = [1, 2, 10, 20];
  
  arr.sort((a, b) => a - b); // [ 20, 10, 2, 1 ]
  arr.sort((a, b) => b - a); // [ 1, 2, 10, 20 ]
  
  // string sorting
  
  const arr1 = ['a', 'b', 'A', 'B', 'г', 'Г', 'д', 'Д']; 
  
  arr1.sort();                             // [ 'A', 'B', 'a', 'b', 'Г', 'Д', 'г', 'д' ]
  arr1.sort((a, b) => a.localeCompare(b)); // [ 'a', 'A', 'b', 'B', 'г', 'Г', 'д', 'Д' ]
  arr1.sort((a, b) => b.localeCompare(a)); // [ 'Д', 'д', 'Г', 'г', 'B', 'b', 'A', 'a' ]
  
  
// 2-dimensional array
  
    var arr_2 = [[1, 5, 6],
                 [3, 7, 9],
                 [9, 2, 1]];
                 
    arr_2.sort((a, b) => b[1] - a[1]); // [ [ 3, 7, 9 ], 
                                       //   [ 1, 5, 6 ], 
                                       //   [ 9, 2, 1 ] ]

// JSON objects

  var actors = [
    {name: 'Dwayne Johnson', income:	89.4},	
    {name: 'Chris Hemsworth', income: 76.4},
    {name: 'Robert DowneyJr.', income: 66},
    {name: 'Akshay Kumar', income: 65},
    {name: 'Jackie Chan', income: 58},
  ];
  
  actors.sort((a, b) => a.income - b.income); 
                   //[ { name: 'Jackie Chan', income: 58 },
                   //  { name: 'Akshay Kumar', income: 65 },
                   //  { name: 'Robert DowneyJr.', income: 66 },
                   //  { name: 'Chris Hemsworth', income: 76.4 },
                   //  { name: 'Dwayne Johnson', income: 89.4 } ]
                
  actors.sort((a, b) => a.name.localeCompare(b.name)); 
                  // [ { name: 'Akshay Kumar', income: 65 },
                  //   { name: 'Chris Hemsworth', income: 76.4 },
                  //   { name: 'Dwayne Johnson', income: 89.4 },
                  //   { name: 'Jackie Chan', income: 58 },
                  //   { name: 'Robert DowneyJr.', income: 66 } ]

You can get more information 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):

This script will be very useful if you need to unpack a large number of zip files.

where folder_with_zip_files is the name of the folder with zip files.

The result (unpacked files) will be placed in the same folder.

#!/usr/bin/env python3

from zipfile import ZipFile
import glob, os

zfiles = []
folder_with_zip_files = "/home/su/Downloads/unzipping"
os.chdir(folder_with_zip_files )
for file in glob.glob("*.zip"):
    zfiles.append(file)

for zfile in zfiles:
   with ZipFile(zfile, 'r') as zipObj:
      zipObj.extractall()

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: