Blog

Infinite Dependent Dropdown Lists in Google Sheets (Part 3)

This article is a continuation of the article Infinite Dependent Dropdown Lists in Google Sheets (part 2) .

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

  • Automatic sheet creation Nome ;
  • Automatic addition of all formulas on the Nome sheet;
  • When editing a previously created row of dependent lists, the values ​​and formulas to the right of the edited cell are automatically deleted.
  • Ability to shift the table on the Nome sheet to the right and down by an arbitrary number of columns
  • 10/01/2021 Added check for Home and Data sheets in the file

var colShift = 0;

function onEdit(e) {

  let row = e.range.getRow();
  let col = e.range.getColumn();
  let sheetName = e.source.getActiveSheet().getName();
  let name = e.value;
  let oldName = e.oldValue;
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  let mask = JSON.stringify(sh.getRange(row, colShift+1, 1, col-colShift).getValues()[0]);
  if (!doesSheetExist('Home', true)) return;
  if (!doesSheetExist('Data', true)) return;

  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").getLastColumn();
  
  if(sheetName === "Home" && name !== oldName && col < colMax+colShift) {
    fillColumn(row, col, mask);
  }
}

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

function fillColumn(row, col, mask) {

  let col_data = col - colShift;

// clear dataVal and Value
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
  let colMax = sh.getLastColumn();
  sh.getRange(row, col + 1, 1, colMax).clearDataValidations().clearContent();
 
// find date
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  let arrData = sd.getDataRange().getValues();
  let arrData_2 = [];
  
  let iMax = arrData.length - 1;
  for(let i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col_data)) == mask) {
      arrData_2.push(arrData[i].slice(0, col_data+1));
    }
  }
  arrData_2 = arrData_2.map(item => item.pop())
  let uniqArrDate_2 = arrData_2.filter(uniqValues); 

// add dataVal
  col++;
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInList(uniqArrDate_2, true)
  .build());
}

function createSheets() {

// is exist Home?
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sd;
  if (doesSheetExist('Data', true)) {
    sd = ss.getSheetByName('Data');
  } else {
    return;
  }
  
  
// 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');

  };

}

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


function doesSheetExist(sheetName, needAlert) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let allSheets = ss.getSheets().map(sheet => sheet.getName())
  if (allSheets.includes(sheetName)) return true;
  if (needAlert) Browser.msgBox(`Error! Could not find sheet "${sheetName}"!`);
  
  return false;
}

This version of the program retains the ability to automatically create all the sheets of the file necessary for the script to work (including formatting and data validation) by pressing just one (!) button from the user menu.

All you need for this:

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

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

ATTENTION!
This article continues: Infinite Dropdown Lists: Personal Financial Plan (example # 1)

Read more >>

Web Scraping With Google Apps Script

The script below allows you to automatically find and download information from one of the freelance exchanges.

The domain name is taken from the Google Spread Sheets page. And search results are also uploaded there.

The function scraper() , which contains two cycles, is used as a control script. In the first loop ( for-loop ), we access the web pages of the site and save them in the variable html . In the second loop ( while-loop ), this variable is sequentially processed using three auxiliary functions:

  • The getBlock function finds the part of the html code (code block) inside the tag (usually by the unique value of the attributes of this tag), and returns this block as a string value;
  • The deleteBlock function, on the contrary, deletes the found fragment of the html code inside the block and also returns the remainder of this block as a string value.
  • Unlike the first two functions, the getOpenTag function does not delete the found tag, but returns it as a string value. True, not the whole tag, but only the first (opening part) of this tag.

function scraper() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const urlDomain = ss.getRange(1, 1).getValue();
  
  let url = urlDomain;
  let count = 0;
  for (let page = 1; page < 5; page++) {
    url = urlDomain + page + '/';
    if (page == 1) url = urlDomain;
    let response = UrlFetchApp.fetch(url);
    ss.getRange(2, 1).setValue(response.getResponseCode());
    let html = response.getContentText();
    let p = 0;
    while (true) {
    
      let out = getBlock(html, 'div', html.indexOf('class="JobSearchCard-primary"', p));
      let block = out[0];
      p = out[1] + 1;
      if (p == 0) break;
      
      let title1 = getBlock(block, 'div', 0)[0];
      let title = getBlock(title1, 'a', 0)[0];
      
      let link = getOpenTag(title1, 'a', 0);
      link = getAttrName(link, 'href', 0)
      let formula = '=HYPERLINK("https://www.freelancer.com' +link + '", "' + title + '")';
      ss.getRange(3 + 3 * count, 2).setValue(formula);
      
      let price = getBlock(block, 'div', block.indexOf('class="JobSearchCard-primary-price'))[0];
      if (price.includes('span')) price =  deleteBlock(price, 'span', price.indexOf('span'));
      ss.getRange(3 + 3 * count + 1, 2).setValue(price).setHorizontalAlignment('right');
      
      let description = getBlock(block, 'p', block.indexOf('class="JobSearchCard-primary-description"'))[0];
      ss.getRange(3 + 3 * count, 1, 3).mergeVertically().setValue(description)
          .setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID)
          .setVerticalAlignment('middle')
          .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
  
      ss.getRange(3 + 3 * count, 2, 3).setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);
      
      let cat = getBlock(block, 'div', block.indexOf('class="JobSearchCard-primary-tags"'))[0];
      cat = cat.split('</a>').map(item => item.split('>')[1]);
      cat.pop();
      cat = cat.join(', ');
      ss.getRange(3 + 3 * count + 2, 2).setValue(cat);
    
      count++;
    
    }; 
  };
}

function getAttrName(html, attr, i) {
  let idxStart = html.indexOf(attr , i);
  if (idxStart == -1) return "Can't to find attr " + attr + ' !';
  idxStart = html.indexOf('"' , idxStart) + 1;
  let idxEnd = html.indexOf('"' , idxStart);
  return html.slice(idxStart,idxEnd).trim();
}

function getOpenTag(html, tag, idxStart) {
  let openTag = '<' + tag;
  let lenOpenTag = openTag.length;
  // where we are?
  if (html.slice(idxStart, idxStart + lenOpenTag) != openTag) {
    idxStart = html.lastIndexOf(openTag, idxStart);
    if (idxStart == -1) return "Can't to find openTag " + openTag + ' !';
  };
  // begin loop after openTag
  let idxEnd = html.indexOf('>', idxStart) + 1;
  if (idxStart == -1) return "Can't to find closing bracket '>' for openTag!";
  return html.slice(idxStart,idxEnd).trim();
}

function deleteBlock(html, tag, idxStart) { // delete opening & closing tag and info between them
  let openTag = '<' + tag;
  let lenOpenTag = openTag.length;
  let closeTag = '</' + tag + '>';
  let lenCloseTag = closeTag.length;
  let countCloseTags = 0;
  let iMax = html.length;
  let idxEnd = 0;
  // where we are?
  if (html.slice(idxStart, idxStart + lenOpenTag) != openTag) {
    idxStart = html.lastIndexOf(openTag, idxStart);
    if (idxStart == -1) return ["Can't to find openTag " + openTag + ' !', -1];
  };
  // begin loop after openTag
  let i = html.indexOf('>') + 1;
  
  while (i <= iMax) {
    i++;
    if (i === iMax) {
      return ['Could not find closing tag for ' + tag, -1];
    };
    let carrentValue = html[i];
    if (html[i] === '<'){
      let closingTag = html.slice(i, i + lenCloseTag);
      let openingTag = html.slice(i, i + lenOpenTag);
      if (html.slice(i, i + lenCloseTag) === closeTag) {
        if (countCloseTags === 0) {
          idxEnd = i + lenCloseTag;
          break;
        } else {
          countCloseTags -= 1;
        };
      } else if (html.slice(i, i + lenOpenTag) === openTag) {
        countCloseTags += 1;
      };
    };
  };
  return (html.slice(0, idxStart) + html.slice(idxEnd, iMax)).trim();
}

function getBlock(html, tag, idxStart) {  // <tag .... > Block </tag>
  let openTag = '<' + tag;
  let lenOpenTag = openTag.length;
  let closeTag = '</' + tag + '>';
  let lenCloseTag = closeTag.length;
  let countCloseTags = 0;
  let iMax = html.length;
  let idxEnd = 0;
  // where we are?
  if (html.slice(idxStart, idxStart + lenOpenTag) != openTag) {
    idxStart = html.lastIndexOf(openTag, idxStart);
    if (idxStart == -1) return ["Can't to find openTag " + openTag + ' !', -1];
  };
  // change start - will start after openTag!
  idxStart = html.indexOf('>', idxStart) + 1;
  let i = idxStart;
  
  while (i <= iMax) {
    i++;
    if (i === iMax) {
      return ['Could not find closing tag for ' + tag, -1];
    };
    let carrentValue = html[i];
    if (html[i] === '<'){
      let closingTag = html.slice(i, i + lenCloseTag);
      let openingTag = html.slice(i, i + lenOpenTag);
      if (html.slice(i, i + lenCloseTag) === closeTag) {
        if (countCloseTags === 0) {
          idxEnd = i - 1;
          break;
        } else {
          countCloseTags -= 1;
        };
      } else if (html.slice(i, i + lenOpenTag) === openTag) {
        countCloseTags += 1;
      };
    };
  };
  return [html.slice(idxStart,idxEnd + 1).trim(), idxEnd];
}

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

Read more >>

Google Apps Script and JavaScript Arrays. Search methods

The examples below detail JavaScript and Apps Script methods for finding array elements: find() , findIndex () , indexOf() , lastIndexOf() , includes() , every() and some() .

Methods findIndex() , indexOf() and lastIndexOf() find the index of the array that satisfies the specified condition, and the find() method find its value.

The includes() method is convenient for use in the if operator, since it returns true if the item is found, and false if not.

The every() method checks that ALL elements of the array match the condition specified in the callback function, and the some() method checks Whether this condition satisfies at least ONE element of the array.

const arr = [1, 2, 3, 4, 5, 3];
  
// find() returns the VALUE of the FIRST item satisfies condition of the callback function
// OR return <underfined>

  let valueGteater3 = arr.find(item => item > 3);
  console.log(valueGteater3); // 4
  
  let valueGteater5 = arr.find(item => item > 5);
  console.log(valueGteater5); // undefined

// findIndex() returns the INDEX of the FIRST item satisfies condition of the callback function
// OR return <-1>

  let indexItemGteater3 = arr.findIndex(item => item > 3);
  console.log(valueGteater3); // 3
  
  let indexItemGteater5 = arr.findIndex(item => item > 5);
  console.log(valueGteater5); // -1
  
// variant for not first item  
  
  let notFirstItem = arr.find((item, index) => {
    if (item > 3 && index > 3) return true;
  });
  console.log(notFirstItem); // 5
  
// indexOf() returns the INDEX of the FIRST item is equal the specified value
// OR return <-1>

  let valueEqual3 = arr.indexOf(3);
  console.log(valueEqual3); // 2
  
  let valueEqual3Next = arr.indexOf(3, 3);
  console.log(valueEqual3Next); // 5
 
  let valueEqual1 = arr.indexOf(1, 3);
  console.log(valueEqual1); // -1
  
// lastIndexOf() returns the INDEX of the LAST item is equal the specified value
// OR return <-1>

  let valueEqual3Last = arr.lastIndexOf(3);
  console.log(valueEqual3Last); // 5
  
  let valueEqual3NextLast = arr.lastIndexOf(3, 4);
  console.log(valueEqual3NextLast); // 2
 
  let valueEqual5Last = arr.lastIndexOf(5, 3);
  console.log(valueEqual5Last); // -1
  
// const arr = [1, 2, 3, 4, 5, 3];
// includes() returns TRUE is element is icluded in the array, and FALSE is not

  let isIncludes3 = arr.includes(3);
  console.log(isIncludes3); // true
  
  let isIncludes6 = arr.includes(6);
  console.log(isIncludes6); // false
  
// every() returns TRUE if ALL elements satisfy the condition, and FALSE is not

  let allPositive = arr.every(item => item > 0);
  console.log(allPositive); // true
  
  let moreThen1 = arr.every(item => item > 1);
  console.log(moreThen1); // false

// some() returns TRUE if AT LEAST ONE element satisfy the condition, and FALSE is not

  let moreThen4 = arr.some(item => item > 4);
  console.log(moreThen4); // true
  
  let moreThen5 = arr.some(item => item > 5);
  console.log(moreThen5); // false

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

Read more >>

Google Apps Script and JavaScript Arrays. Method .reduce()

Short description of the method

The .reduce() method, like most other methods using the callback function, is a for loop, inside which the callback function sequentially processes all elements of the array. However, this is the only method that uses another parameter - accumulator, due to which the .reduce() method is often used to calculate the sum of the array.

The .reduce() method can pass 4 parameters to the callback function:

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

This method can also be used for more complex calculations. For example, below is a script that creates an array of indexes of the elements of the arr array, which are odd numbers.

const arr = [1, 2, 3, 4, 5];
  
// sum with for loop
  let sumArr = 0;
  for (let i = 0; i < arr.length; i++) {
    sumArr += arr[i];
  };
  console.log(sumArr);
  
// sum with forEach method
  let sumArr_1 = 0;
  arr.forEach(item => sumArr_1 += item);
  console.log(sumArr_1);
  
// sum with reduce method
  let sumArr_2 = arr.reduce((acc, item) => acc + item, 0);
  console.log(sumArr_2);
  
// with initial value
  sumArr_2 = arr.reduce((acc, item, index, array) => {
    console.log(`acc = ${acc}, item = ${item}, index = ${index}`);
    return acc + item;
  }, 0);
  console.log(sumArr_2);
  
// without initial value
  sumArr_2 = arr.reduce((acc, item, index, array) => {
    console.log(`acc = ${acc}, item = ${item}, index = ${index}`);
    return acc + item;
  });
  console.log(sumArr_2);
  
// find indexes items that are odd numbers  
  let newArr = arr.reduce((acc, item, index) => {
    if (item % 2 !== 0) acc.push(index);
    return acc;
  }, []);
  console.log(newArr); // [ 0, 2, 4 ]
  
 // reduceRight
  sumArr_2 = arr.reduceRight((acc, item, index, array) => {
    console.log(`acc = ${acc}, item = ${item}, index = ${index}`);
    return acc + item;
  });
  console.log(sumArr_2);

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

Read more >>

Creating Arrays of Apps Script. Adding, Deleting and Modifying Array Elements

Arrays in Apps Script can be created in several ways:

  1. Declare the name of the array in the line of code and equate it to a sequence of values taken in square brackets;
  2. Read data from a Google SpreadSheet using the .getValues() method;
  3. Dividing a string using the .split([delimiter]) method (usually a space is used as the delimiter delimiter;
  4. PP 3, when a word is used as a string, and as an divide, an empty string.

function createArray() {
  
// ======== HOW TO CREATE ARRAY? ===========

  // 1.) Write
  var arr = [1, 2, 3];
  
  // 2.) Read from spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var arr1 = ss.getDataRange().getValues(); // [[1.0, 2.0, 3.0]]
  
  // 3.) Using string splitting
  var string = 'I am learning Google Spreadsheet';
  var arr2 = string.split(' '); // [I, am, learning, Google, Spreadsheet]
  
  // 4.) Using string splitting
  var arr3 = 'word'.split(''); // [w, o, r, d]
  
}

There are several ways to add elements to an array:

  1. The simplest one is to equate a specific element of an array (by specifying its index in square brackets) to the desired value.

  2. If the maximum index value is exceeded by more than one, then all elements between the old and new maximum indexes are automatically set to null .

  3. Adding elements to the end of an array using the .push (value) method;
  4. Adding elements to the beginning of the array using the .unshift (value) method;
  5. Deleting an element with maximum index .pop () ;

  6. The value of the deleted element can be assigned to a new variable.

  7. Deleting an element with a null index .shift () ;
  8. Address change of array elements using the .splice (index, hawManyDelete, whatInsert) method;

  9. Where:
    • index - index number from which indexes will be inserted / deleted;
    • hawManyDelete - the number of items to be deleted. If hawManyDelete = 0, there will be no deletion;
    • whatInsert - the value of the element (s) that (s) will be inserted, starting at index number index .
  10. Method of changing the order of indexes in an array from direct to reverse and vice versa .reverse() ;
  11. The method for sorting the elements of the array .sort() . Without a parameter in the form of a function, it is used extremely rarely due to its limitations: it only sorts unicode strings in ascending order.

function addDeleteItems() {

// ================ ADD ITEMS =======================

  // 1.) [index]
  var arr4 = [1, 2, 3];
  arr4[3] = 4; // [1.0, 2.0, 3.0, 4.0]
  arr4[5] = 6; // [1.0, 2.0, 3.0, 4.0, null, 6.0]
  arr4[-1] = 0; // [1.0, 2.0, 3.0, 4.0, null, 6.0]
  
  // 2.) .push(value)
  arr4.push(7); //  [1.0, 2.0, 3.0, 4.0, null, 6.0, 7.0]
  
  // 3.) .unshift(value)
  arr4.unshift(0) // [0.0, 1.0, 2.0, 3.0, 4.0, null, 6.0, 7.0]
  
// ================= DEL ITEMS ======================

  // 4.) .pop()
  var x = arr4.pop(); // [0.0, 1.0, 2.0, 3.0, 4.0, null, 6.0]
  
  // 5.) .shift()
  var x = arr4.shift(); // [1.0, 2.0, 3.0, 4.0, null, 6.0]
  
// ===== ADD (INSERT), DELETE, CAHGE =================

  // 6.) .splice(index, hawManyDelete, whatInsert)
  arr4.splice(4, 0, 5); // [1.0, 2.0, 3.0, 4.0, 5.0, null, 6.0]
  arr4.splice(5, 1);    // [1.0, 2.0, 3.0, 4.0, 5.0, 6.0]

// ============ CHANGE ORDER =========================
  // 7.) .reverse()
  arr4.reverse(); // [6.0, 5.0, 4.0, 3.0, 2.0, 1.0]
  
  // 8.) .sort()
  arr4 = [6.0, 5.0, 404.0, 41.0, 4.0, 3.0, 2.0, 1.0];
  arr4.sort();
}

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

Read more >>

Google Apps Script and JavaScript Arrays. Method .forEach()

Short description of the method

The .forEach() method, in essence, is a for loop, inside which the callback function sequentially processes all elements of the array.

The .forEach() 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

This is perhaps the only method that returns nothing. Therefore, it is used for external processing, for example, for printing array elements.

const arr = [1, 2, 3, 4, 5];
  
  for (let i = 0; i < arr.length; i++) {
    console.log(arr[i]);
  };

// variant #1
  arr.forEach(function(item) {
    console.log(item);
  });

// variant #2
  arr.forEach(item => console.log(item));
  
  console.log('------------------------------------');
  
// callback function parameters
  arr.forEach((item, idx, array) => console.log(item, idx, array));

  console.log('------------------------------------');

// get intersection of arrays
  let arr1 = [1, 3, 5];
  let arr2 = [1, 2, 3, 4, 5];
  let result = [];

  let res = arr1.forEach(item => {
    if (arr2.includes(item)) return result.push(item);
  });

  console.log(result); // [ 1, 3, 5 ]
  console.log(res); // undefined

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

Read more >>

Google Apps Script and JavaScript Arrays. Method .filter()

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

Read more >>

Google Apps Script and JavaScript Arrays. Method .map()

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

Read more >>

Google Apps Script and JavaScript Arrays. Method .sort()

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

Read more >>

Tags list

    Apps Script      Arrays Java Script      asynchronous code      asyncio      coroutine      Django      Dropdown List      Drop Shipping      Exceptions      GitHub      Google API      Google Apps Script      Google Docs      Google Drive      Google Sheets      multiprocessing      Parsing      Python      regex      Scraping      ssh      Test Driven Development (TDD)      threading      website monitoring      zip