I'm fairly new to both Google Apps Script and JavaScript, but have done some extensive reading on similarities and differences between the two. However, despite this, I can't seem to understand why this custom function for Google Sheets that I've been working on will run correctly in Javascript, but not in GaS.
My basic goal is to check over a list of names, count any duplicates, and display the five names that show up the most, along with the count of how often they appeared. The code below only accounts for the top result.
The script so far is as follows:
//Initial Declarations
function watchList(data) {
var first = {};
var rcount = 0;
if (data === undefined){data = SpreadsheetApp.getActiveSheet().getRange("B2:B139")}; //Utilized for testing within the script editor
for (var i = 0; i < data.length; i++){//loop through the names
rcount = 0;// Reset duplicate count
for (var r = 0; r < data.length; r++) {//loop through the entire list again for each increment of the array above
if (data[i] === data[r]) {//Is this name a duplicate?
rcount++;//If yes, add 1 to the count
}
}
if (first.count == undefined || first.count < rcount) {//Does this value have the most duplicates?
first.name = data[i];//If so, assign the name and count values to the first object
first.count = rcount;
}
}
return [first.name, first.count];
}
What I return, though, is:
first name on the list, 1
When I then punched all the code into a javascript editor, and logged the results to the console, it worked perfectly! The slightly altered (to account for there being no attached sheet) javascript code is below:
function watchList() {
var range = new Array('Name 1', 'Name 1',
'Name 1','Name 2',
'Name 2', 'Name 2',
'Name 2', 'Name 3');
var first = {};
var rcount = 0;
for (var i = 0; i < range.length; i++){
rcount = 0;
for (var r = 0; r < range.length; r++) {
if (range[i] == range[r]) {
rcount++;
}
}
if (first.count === undefined || first.count < rcount) {
first.name = range[i];
first.count = rcount;
}
}
console.log([first.name, first.count]);
return first.name;
}
watchList();
Would anyone be able to enlighten me as to whatever missteps I'm missing here? Thanks a ton in advance!
Edit - This has been linked as a duplicate of the "How to Compare JS Arrays" question, but seems, at least to me, to be a different issue, as that is regarding two entire arrays, while my question is looking to compare individual array elements against one another, and at a separate pace.
getRange() doesn't return a array. .getValues() is needed to get array values from the range.
Assuming the above is a simple typo, getValues() doesn't return a return a 1 dimensional array either. It returns a 2D array. Custom functions also return a 2D array.
Illustrating the problem,
function watchList() {
var range = new Array('Name 1', 'Name 1',
'Name 1','Name 2',
'Name 2', 'Name 2',
'Name 2', 'Name 3').map(e=>[e]);// modified to mimick the 2D array returned by `getValues` or in a custom function
var first = {};
var rcount = 0;
for (var i = 0; i < range.length; i++){
rcount = 0;
for (var r = 0; r < range.length; r++) {
if (range[i] == range[r]) {//Two objects are never equal, except when they refer to the same object,i.e., the first time, when both sides refer to the same object in memory. See duplicate question.
rcount++; //rcount is 1
}
}
if (first.count === undefined || first.count < rcount) {//1<1 will never be true; The first name and first count will stay
first.name = range[i];
first.count = rcount;
}
}
console.log([first.name, first.count]);
return first.name;
}
watchList();.getValues() on the rangefunction watchList() {
var range = [
[ 'Name 1' ],
[ 'Name 1' ],
[ 'Name 1' ],
[ 'Name 2' ],
[ 'Name 2' ],
[ 'Name 2' ],
[ 'Name 2' ],
[ 'Name 3' ]
];// simulate getValues() or custom function arguments B1:B8
var first = {};
var rcount = 0;
for (var i = 0; i < range.length; i++){
rcount = 0;
for (var r = 0; r < range.length; r++) {
if (range[i][0] == range[r][0]) {//compare primitives
rcount++;
}
}
if (first.count === undefined || first.count < rcount) {
first.name = range[i];
first.count = rcount;
}
}
console.log([first.name, first.count]);
return first.name;
}
watchList();