I am a novice and don't have much experience. Now try to solve some problems at work with JS, and I have encountered a big trouble. Have tried to read some suggested QA, but failed to a solution. I sincerely ask for help, any help is greatly appreciated.
html:
<script>
document.getElementById("autoin").addEventListener("input",doSearch);
function doSearch(){
var ai = document.getElementById("autoin").value;
google.script.run.withSuccessHandler(udAg).getAgent(ai);
}
function udAg(ag){
document.getElementById("agent").value = ag;
M.updateTextFields();
}
</script>
google script:
function getAgent(ai){
var ss = SpreadsheetApp.openByUrl(url);
var ws = ss.getSheetByName("Updated");
var ctList = ws.getRange('A2:A17').getValues();
var position = ctList.indexOf(ai);
return position;
}
It always return -1, even I replaced variable ai by direct string 'ABC', which is the string in A2.
Tried to return both ai and ctList[0], they seemed to be identical, 'ABC'.
console.log(ctList) showed [ [ 'ABC' ], [ 'CDE' ],[ 'EFG' ],... ]
I really can't figure out a solution, I really need to trouble everyone to help. Thank you.
It returns -1 because you run an indexOf() on an array containing sub-arrays, so it cannot match your string.
For instance, I can take your example as a minimal test case and do this:
const array = [ [ 'ABC' ], [ 'CDE' ],[ 'EFG' ] ];
console.log(array.indexOf('ABC')); // -1
Obviously, it returns -1 because it cannot find a match for the "ABC" string inside the array variable: "ABC" is contained in a sub-array that's one level deeper. ie it's contained by array[0], specifically at array[0][0].
If you read the MDN page for Array.indexOf(), you will notice that it will attempt to find the item that matches the parameter in the array. You are looking for a string in an array of string arrays.
Which is why trying to look for ctList[0] would work, as it's an identity match:
// returns -1 : correct string value, not an object equality, though
console.log(array.indexOf(['ABC']));
// returns 0: same object that's being looked up
console.log(array.indexOf(array[0]));
So, it's hard to know from your example what you really are trying to do, but it looks like you actually want to lookup the position of the array that contains your string, and not the position of the string. If that's the case, and there's always only one element per sub-array, this would work for my example:
array.map(subarray => subarray[0]).indexOf('ABC'); // 0: YAY!
Or for your case:
ctList.map(subarray => subarray[0]).indexOf(ai);
Try adding flat() after you getValues():
function getAgent(ai) {
var ss = SpreadsheetApp.openByUrl(url);
var ws = ss.getSheetByName("Updated");
var ctList = ws.getRange('A2:A17').getValues().flat();//added flat
var position = ctList.indexOf(ai);
return position;
}
Even a single row or column is returned as two dimensional array and index does not really work with 2 dimensional arrays. You can do the same thing as flat() does in other ways like with map.