I made a small search box to retrieve values from a datasheet. For some reason it only loops once. Also this is the first time I'm trying something in google sheets, so if I want to search a string in column B, then the SEARCH_COL_IDX
should be 1 right?
I put in a few loggers to check the process. I do see Logger.log(str)
, Logger.log("Check")
and Logger.log("Check2)
. The latter two only once, where it should be multiple times imo. The Logger.log(row[#])
I don't get to see at all, which means it doesn't find anything.
I also tried doing if(row[SEARCH_COL_IDX] = str) {
to see where it is looking. Than the Logger.log(row[#])
do come back, but from different rows AND columns. I am doing something wrong, but I can't find it.. any suggestions?
var SPREADSHEET_NAME = "Database";
var SEARCH_COL_IDX = 1;
var RETURN_COL_IDX= 1;
function searchStr(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Userform");
var str = formSS.getRange("d17").getValue();
Logger.log(str);
var values = ss.getSheetByName("Database").getDataRange().getValues();
for (var i = 0; i < values.length; i ) {
Logger.log("check");
var row = values[i];
Logger.log("check2");
if(row[SEARCH_COL_IDX] == str) {
formSS.getRange("d3").setValue(row[0]);
Logger.log(row[0]);
formSS.getRange("d5").setValue(row[1]);
Logger.log(row[1]);
formSS.getRange("d7").setValue(row[2]);
Logger.log(row[2]);
formSS.getRange("d9").setValue(row[3]);
Logger.log(row[3]);
formSS.getRange("d11").setValue(row[4]);
Logger.log(row[4]);
formSS.getRange("d13").setValue(row[5]);
Logger.log(row[5]);
}
Logger.log("nothing found");
return row[RETURN_COL_IDX];
}
CodePudding user response:
Your function has a return command in the last line of your loop block.