Home > Software engineering >  For loop not looping through spreadsheet
For loop not looping through spreadsheet

Time:09-17

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.

  • Related