I'm trying to compare a name retrieved from a JSON object, with a name as it exists on a google Sheet. Try as I might, I can't get a comparison that yields a positive. I've tried:
IndexOf
localeCompare
==
===
I've tried
key===value
String(key)===String(value) and
String(key).valueof()=String(value).valueof.
I've also tried calling trim() on everything to make sure there are no leading/trailing white spaces (there aren't, as confirmed with a length() comparison.
As you can see from the screen shot, the values of key and value are exactly the same.
Any pointers would be gratefully received. This has held up my project for days!
Description
This is not a solution but it might help find the problem. Perhaps the characters in one or the other is not what you think. Visually they compare, but what if one has a tab instead of a space. Try this, list the character codes for each and see if any character has a different value.
I've added another option that eliminates the for loop thanks to @TheMaster
Script (Option 1)
function test() {
try {
let text = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test").getRange("A1").getValue();
console.log(text);
let code = [];
for( let i=0; i<text.length; i++ ) {
code.push(text.charCodeAt(i))
}
console.log(code.join());
}
catch(err) {
console.log(err);
}
}
Script (Option 2)
function test() {
try {
let text = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test").getRange("A1").getValue();
console.log(text);
let code = [];
[...text].forEach( char => code.push(char.charCodeAt(0)) );
console.log(code.join());
}
catch(err) {
console.log(err);
}
}
Execution log
7:57:53 AM Notice Execution started
7:57:56 AM Info Archie White
7:57:56 AM Info 65,114,99,104,105,101,32,87,104,105,116,101
7:57:54 AM Notice Execution completed
function compare() {
const json = '{"values":[["good","bad","ugly"]]}';
const obj = JSON.parse(json);
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet0");
const [hA,...vs] = sh.getDataRange().getDisplayValues();
let matches = [];
vs.forEach((r,i) => {
r.forEach((c,j) => {
let idx = obj.values[0].indexOf(c);
if(~idx) {
matches.push({row:i+1,col:j+1,index: idx});
}
})
})
Logger.log(JSON.stringify(matches))
}
Execution log
9:31:50 AM Notice Execution started
9:31:52 AM Info [{"row":1,"col":1,"index":0},{"row":2,"col":1,"index":1},{"row":3,"col":1,"index":2}]
9:31:51 AM Notice Execution completed
Sheet1:
| COL1 |
|---|
| good |
| bad |
| ugly |