0

So I was doing a little search engine for Google Sheets and I didn't want it to be case sensitive, so I make it declare "RealComments" as the actual content to be retrieved and then proceed to lowercase the "Comments" array row by row on the main loop.

It sounds to me like a no brainier that it should work, they are different variables and I'm returning the RealComments, not the Comments. But some kind of weird quantum entanglement is going on in my code because no matter how I declare it, every time I apply toLowecase() to either the RealComments or Comments arrays both get modified.

I have no clue why that is or how to fix it. Here's the full code:

/**
 * Searches for relevant questions and answers
 *
 * @param {cell} Keywords Cell with comma separated list
 * @param {cell} Query Cell with text in question format
 * @param {cell} Type Validation cell with options, leave "Any" for no filter
 * @param {column} TypeOfComment Array with the type of question column
 * @param {array} Comments Array with the questions and answers in two columns
 * @param {column} ScreenshotLinks Array with the screenshot link column
 * @return Your answer. :)
 * @customfunction
 */
function SearchQuery(Keywords,Query,Type,TypeOfComment,Comments,ScreenshotLinks) {
  if(Keywords==""){
    return "Please type your query/keywords above for results to be displayed here."
  }
  var ResultArray = [];
  var Score = 0;
  var MinimumScore = 2;
  var MinimumScorePosition = 0;
  var keywords = [];
  var querywords = [];
  var RealComments = Comments;
  for(rows in RealComments){
    RealComments[rows].push(ScreenshotLinks[rows][0]);
  }
  var RemaneingKeywords = CleanPunctuation(Keywords);
  var RemaneingQuery = CleanPunctuation(Query);
  var NextSpace = RemaneingKeywords.indexOf(' ');
  while(NextSpace != -1){
    keywords.push(RemaneingKeywords.substr(0,NextSpace));
    RemaneingKeywords = RemaneingKeywords.substr(NextSpace+1,RemaneingKeywords.length);
    NextSpace = RemaneingKeywords.indexOf(' ');
  }
  keywords.push(RemaneingKeywords);
  NextSpace = RemaneingQuery.indexOf(' ');
  while(NextSpace != -1){
    querywords.push(RemaneingQuery.substr(0,NextSpace));
    RemaneingQuery = RemaneingQuery.substr(NextSpace+1,RemaneingQuery.length);
    NextSpace = RemaneingQuery.indexOf(' ');
  }
  querywords.push(RemaneingQuery);
  for(rows in Comments){
    Comments[rows][0] = Comments[rows][0].toLowerCase(); //Investigate lowercase glitch
    Comments[rows][1] = Comments[rows][1].toLowerCase();
    if(Type=="Any"||TypeOfComment[rows]==Type){
      Score = 0;
      for(keyword in keywords){
        if(Comments[rows][0].indexOf(keywords[keyword])!=-1||Comments[rows][1].indexOf(keywords[keyword])!=-1){
          Score = Score+10;
        }
      }
      for(words in querywords){
        if(Comments[rows][0].indexOf(querywords[words])!=-1||Comments[rows][1].indexOf(querywords[words])!=-1){
          Score = Score+1;
        }
      }
      if(ResultArray.length<20 && Score>2){
        ResultArray.push(RealComments[rows]);
        ResultArray.push(Score);
        MinimumScore = FindMinimumScore(ResultArray);//Math.min.apply(null,ResultArray);
      }
      else{if(Score>MinimumScore){
        MinimumScorePosition = ResultArray.indexOf(MinimumScore);
        ResultArray.splice(MinimumScorePosition-1,2);
        ResultArray.push(RealComments[rows]);
        ResultArray.push(Score);
        MinimumScore = FindMinimumScore(ResultArray);
      }}
    }
  }
  var ScoresForSorting = []; //Sorting the responses here
  for(i=0;i<10;i++){
    if(ResultArray[i+1]==undefined){ScoresForSorting.push(-2);}
    else{ScoresForSorting.push(parseInt(ResultArray.splice(i+1,1)));}
  }
  var ResponseOrder = [];
  for(i=0;i<10;i++){
    MinimumScorePosition = ScoresForSorting.indexOf(Math.max(ScoresForSorting[0],ScoresForSorting[1],ScoresForSorting[2],ScoresForSorting[3],ScoresForSorting[4],ScoresForSorting[5],ScoresForSorting[6],ScoresForSorting[7],ScoresForSorting[8],ScoresForSorting[9]));
    ResponseOrder.push(MinimumScorePosition);
    ScoresForSorting[MinimumScorePosition]=-3;
  }
  var SortedResultArray = [];
  for(results in ResponseOrder){
    SortedResultArray.push(ResultArray[ResponseOrder[results]]);
  }
  if(SortedResultArray[0]==undefined){SortedResultArray = []; SortedResultArray.push("Sorry, No Result Was Found To Your Search. ) : ");}
  return SortedResultArray
}
function FindMinimumScore(ResultArray){
  return Math.min(ResultArray[1],ResultArray[3],ResultArray[5],ResultArray[7],ResultArray[9],ResultArray[11],ResultArray[13],ResultArray[15],ResultArray[17],ResultArray[19])
}
function CleanTwoLetterWords(words){
  var badwords = ["do","in","at","it","of","as","be","if","or","we","by","an","or","no","my","vs"];
  for(badword in badwords){words = words.replace(badwords[badword],"");}
  return words
}
function CleanThreeLetterWords(words){
  if(parseInt(words)>9){return ""} //remove numbers
  var badwords = ["and","for"];
  for(badword in badwords){words = words.replace(badwords[badword],"");}
  return words
}
function CleanPunctuation(words){
  words = words.toLowerCase();
  var badlettergroup = ["{","}",",",":","+","-","™","®","?","!","(",")","'"];
  for(letter in badlettergroup){while(words.indexOf(badlettergroup[letter]) != -1){words = words.replace(badlettergroup[letter]," ");}}
  return words
}
function MakeThingsSingular(words){
  var exceptions = ["this"];
  if(exceptions.indexOf(words) != -1){return words}
  var wordl = words.length;
  if(words.substr(wordl-1,wordl) == "s" && words.substr(wordl-2,wordl) != "ss"){return words.substr(0,wordl-1)}
  return words
}

I wrote it all myself, but I don't think any of this is particularly brilliant so feel free to copy. Also, to suggest improvements if you have any. But most important of all, if you have any idea why the lines making the Comments array lowercased would be affecting RealComments and/or how to fix it, please let me know.

EDIT:

So after some more research and helpful answers I've tried declarying RealComments as:

  var RealComments = [];
  for(rows in Comments){
    RealComments.push(Comments[rows]);
  }

On which case the glitch persists.

  var RealComments = [...Comments];

Which returns a syntax error.

  var RealComments = Comments.slice();

In which case the glitch still persists. ...

So basically, I still don't understand what's wrong with the code, commenting out the either of the lowercase lines affects the column you'd expect on the RealComments as if they were still linked.

Comments[rows][0] = Comments[rows][0].toLowerCase();
Comments[rows][1] = Comments[rows][1].toLowerCase();

So I guess this post if not yet solved... But thank you for all the replies up to now.

Anderson Klein
  • 1
  • 2
  • 1
    `RealComments = Comments` There's your entanglement. There's a lot to process in this code, but you set those variables as pointing to the same array. Did you mean to copy the array? – Carcigenicate Jan 11 '19 at 20:19
  • 3
    Possible duplicate of [Copy array by value](https://stackoverflow.com/questions/7486085/copy-array-by-value) – TheMaster Jan 11 '19 at 20:23
  • Carcigenicate, yes, I wanted to copy it. I thought that maybe this was the cause so I tried replacing RealComments = Comments with: for(rows in Comments){RealComments.push(Comments[rows]);} and got the same result. – Anderson Klein Jan 11 '19 at 20:23
  • TheMaster. Thank you, that is exactly what I was looking for. Should I delete my post? – Anderson Klein Jan 11 '19 at 20:25
  • @Anderson You can accept it as a duplicate question. (For future reference, that's how you should ask a question-not dump all your code. [Mcve]) – TheMaster Jan 11 '19 at 20:29
  • I will try to apply that next time. I was just so clueless to the origin of the glitch I was wondering if I was overlooking something somewhere else in the code. – Anderson Klein Jan 11 '19 at 20:33
  • You need a deep copy. `slice()` won't work, because `Comments` is not a array, but array of arrays. See all answers in the duplicate. – TheMaster Jan 11 '19 at 20:47
  • Thank you TheMaster, you solved my issue with that last comment. I would up-vote you if I could! It would have taken me forever to figure this out with random searches. – Anderson Klein Jan 11 '19 at 21:02
  • 1
    On this site solutions should be posted as an answer not as an edit to the question. – Rubén Jan 11 '19 at 23:32
  • Thank you Ruben for pointing it out. I have edited it and added TheMaster's comment as the solution. – Anderson Klein Jan 15 '19 at 17:23

2 Answers2

0

In javascript Array assignment creates reference not copy.

if you do

var a = [1,2,3,4];

var b = a;

b will be the reference for a. It means if you modify b, it is actually gonna modify a.

QUICK SOLUTION in the line where you are doing var RealComments = Comments;

Do this instead var RealComments = Comments.slice();

As long as Comments is an array slice method will return a new instance of Comments array. So modifying RealComments won't modify Comments anymore.

Mitul
  • 108
  • 2
  • 10
  • I have tried " var RealComments = Comments.slice(); " but when modifying Comments, RealComments is still getting modified. – Anderson Klein Jan 11 '19 at 20:34
  • you are doing something like `for(rows in RealComments){RealComments[rows].push(ScreenshotLinks[rows][0]); }` is this intended? – Mitul Jan 11 '19 at 21:00
  • Yes, it's a third column that should appear in the result but is not used in the search process itself. TheMaster has already figured out why slice did not work, it's because it was an array of arrays so it just moved the issue one level lower. But JSON.parse(JSON.stringify(Comments)); did it for me. Thank you regardless! – Anderson Klein Jan 11 '19 at 21:03
0

TheMaster pointed out that "slice() won't work, because Comments is not a array, but array of arrays.". After which the solution that did it for me was just using:

var newArray = JSON.parse(JSON.stringify(orgArray));

Which in my case was:

var RealComments = JSON.parse(JSON.stringify(Comments));

Thank you for all the help! I hope this helps other users.

Anderson Klein
  • 1
  • 2