This article will be useful to those who need to change or modify their Apps Script code (including the code posted on this site) to their immediate needs.
And the first question:
Of course, in order for the code to work again, it needs to be debugged.
And first of all, we need to find a place after which problems begin and the code behaves in a completely different way from what is expected of it.
There may be several strategies here. The most reliable of them (but not the fastest) is to check the entire code step by step, starting from the very beginning.
To do this, in the editor window, you must:
In this figure, the program stopped just at the breakpoint on the 32nd line.
In the same figure, you can see that in the right block under the heading Debugger there are four buttons:
Below the control buttons of the Debugger block is the Variables block, which shows the current value of the variables at each program breakpoint.
In addition, we can print the desired values to the console using the command:
console.log(variable)
These data are displayed on the images in the lower part in the Execution log block.
If we want to test the operation of a function that has arguments (for example, the function fillColumn(row, col, mask)), then the best solution is to first create a helper function tmp(), where
Then, the launch of this very auxiliary function tmp() for debugging, in fact, will be the launch of the fillColumn function itself with arguments.
In fact, in the figure above, the tmp() function plays a similar auxiliary role for the fillColumn(row, col, mask) function.
The onEdit(e) system function is used to intercept data changes/entry on Google spreadsheet and does not respond to the standard breakpoint processing by the debugger. Therefore, we will not be able to debug it in the usual way.
There are at least two solutions:
Browser.msgBox(variable);
For the convenience of debugging the second scenario, you can add the values of any variables as arguments to variable.
Now, to run the second option, you need to make any changes to the Google spreadsheet. This will immediately run the onEdit(e) function. And if there are no errors before the message box launch line, then we will definitely see a window with the variable.
For more information on how to debug scripts in the Apps Script editor, you can get from this video (RU voice):
This article is a continuation of Infinite Dependent Dropdowns in Google Sheets.
Changes and additions to the code are described here, namely:
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:
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"
datasheet: "Data",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 the article Infinite Dependent Dropdown Lists in Google Sheets (part 3) .
Here's an example of how Infinite Linked Lists can be used in a Personal Financial Plan.
In addition to adding several user-friendly functions (automatic insertion of the payment date and the formula for maintaining the balance of receipts and expenses), the script provided contains a number of significant improvements and improvements:
// global variable
const excludedSheets = ['Summary', 'Category'];
const dataSheet = 'Category';
var colShift = 3;
var rowMin = 4;
function onEdit(e) {
let row = e.range.getRow();
let col = e.range.getColumn();
let sheetName = e.source.getActiveSheet().getName();
if (excludedSheets.includes(sheetName)) return;
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]);
let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet).getLastColumn();
if(name !== oldName && row >= rowMin && col < colMax+colShift) {
insertDateFormulasAndDataValidation(row, col, sheetName)
fillColumn(row, col, mask, sheetName);
}
}
function insertDateFormulasAndDataValidation(row, col, sheetName) {
if (col == colShift+1) {
let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
let dateValue = sh.getRange(row, 2).getValue();
if (dateValue == '') {
let today = new Date();
let timeZone = Session.getScriptTimeZone();
let todayStr = Utilities.formatDate(today, timeZone, 'dd.MM.yyyy');
sh.getRange(row, 2).setValue(todayStr);
};
let formulaStr = '=I' + (row-1) + '+H' + row + '-C' + row;
sh.getRange(row, 9).setFormula(formulaStr);
let conN = String.fromCharCode(65+colShift)
let adress = sheetName + '!' + conN + (row+1) + ':' + conN + (row+3);
sh.getRange(adress).setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInRange(sh.getRange(dataSheet+'!$A$2:$A'), true)
.build());
}
}
function tmp() {
insertDateFormulasAndDataValidation(7, 4, 'Transactions');
}
function onOpen() {
let ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Custom Menu')
.addItem('Create sheets', 'createSheets')
.addToUi();
}
function fillColumn(row, col, mask, sheetName) {
let col_data = col - colShift;
// clear dataVal and Value
let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet).getLastColumn();
sh.getRange(row, col + 1, 1, colMax-col_data).clearDataValidations().clearContent();
// find date
let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheet);
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 uniqValues(item, index, arr) {
return arr.indexOf(item) === index;
}
You can get more information from this video (RU voice!):
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:
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:
You can get more information from this video (RU voice!):
ATTENTION!
This article continues: Infinite Dropdown Lists: Personal Financial Plan (example # 1)
The method of parsing (scraping), discussed in the article Parsing (scraping) using Google Apps Script is incredibly simple and easy to use. There is no need for complex programs, or high qualifications or special knowledge, or a lot of experience. All code is written directly in the Google Sheets editor, and then placed and run there.
In addition to all this, the script can be run on a schedule at any time in 24/7 mode, and the result can be processed right there and, if necessary, sent by email or a new file with the received and processed data can be created on Google disk.
However, there may be problems here. First of all, this script does not work with sites (more precisely, with pages) where pre-registration is required. Since, to register on the site, you need to create a session and place its code in special files - cookies. However, the standard UrlFetchApp function used in Apps Script to access a website does not allow cookies.
There is another problem, which has recently manifested itself more and more often. This is the unwillingness of site owners to share information with bots. Therefore, you can often see a picture when the html-code issued by the server at the request of the script is significantly different from the code that is issued to the browser.
Actually, precisely in order to quickly find out whether it is possible to parse (scrap) the site we need or not, this program was written:
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Custom Menu')
.addItem('Check', 'fastAnalyzer')
.addSeparator()
.addItem('Create Sheeet CHECK', 'createSheet')
.addToUi();
}
function fastAnalyzer() {
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Check');
const urlDomain = ss.getRange(2, 1).getValue();
let url = urlDomain;
let count = 0;
let response = UrlFetchApp.fetch(url);
let lll = response.getContentText().length;
ss.getRange(2, 2).setValue(response.getResponseCode());
ss.getRange(2, 3).setValue(lll);
let html = response.getContentText();
let whatLookingFor = ss.getRange(3, 2).getValue();
let pos = html.indexOf(whatLookingFor);
ss.getRange(3, 3).setValue(pos);
ss.getRange(4, 1, ss.getLastRow(), 2).clearContent();
let totalNumber = html.length
let i = 0;
// Check defoult value of delta - number of symbols in 1 row
// If the cell is empty, print the defole value = 5000
let delta = Number.parseInt(ss.getRange(2, 4).getValue());
if (isNaN(delta)) {
delta = 5000
// Print defoult value of number of symbols in 1 row
ss.getRange(2, 4).setValue(delta);
};
let iStart = 0;
while (true) {
let iEnd = iStart + delta;
if (iEnd > totalNumber) iEnd = totalNumber;
ss.getRange(4 + i, 1).setValue("Символы от " + iStart + " до " + iEnd);
ss.getRange(4 + i, 2).setValue(html.slice(iStart,iEnd));
// let currentRow = (4+i) + ":" + (4+i)
// ss.getRange(3, 3).setValue(currentRow);
// ss.getRange(currentRow).activate();
// ss.setRowHeight(4+i, 200);
i++;
if (iEnd - iStart < delta) break;
iStart = iEnd;
};
}
function createSheet() {
// is exist Check sheet?
let s = SpreadsheetApp.getActiveSpreadsheet();
// create if not exist
if(!s.getSheetByName('Check')) {
s.insertSheet('Check', 0);
ssc = s.getSheetByName('Check');
ssc.getRange(1, 1).setValue("Url сайта");
ssc.getRange(1, 2).setValue("Код ответа сайта");
ssc.getRange(1, 3).setValue("Число символов на выбранной странице");
ssc.getRange(1, 4).setValue("Изменить вывод числа знаков в однй строке");
ssc.getRange(2, 1).setBackground('ACCENT3'); // yellow background cell
ssc.getRange(3, 1).setValue("Какой элемент кода ищем?");
ssc.getRange(3, 2).setBackground('ACCENT3'); // yellow background cell
};
}
So, all you need to work with this program is
The program is ready to go! All that is needed now is to add the site url to the Google tables page, the code element that you want to find on this page, and run the script. The answer will be ready in a second!
You can find more information in this video (RU voice):
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:
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):
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):
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:
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):
Arrays in Apps Script can be created in several ways:
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:
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 .
The value of the deleted element can be assigned to a new variable.
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):