I was able to create a dynamic dropdown using Tanaike's answer in this thread Auto-populate fields in a form based on a select with data taken from spreadsheet. However, how can I make the dropdown to only show jobs that have an ACTIVE in column G (which is the equivalent of ${data[i][5]} in my code. I tried storing ${data[i][5]} in a variable to create this if statement below:

     for (var i = 0; i < data.length; i =1) {
      var status = ${data[i][5]};

       if(status == "ACTIVE") {
        optionsHTML  = `<option data-values="

        ${data[i][1]},
        ${data[i][2]},
        ${data[i][3]},
        ${data[i][4]},
        ${data[i][6]}                 
        
        ">${data[i][0]}</option>`;  // Modified
        }else{
        
        }
      };

But it wont work.

Here is the full code:

  function getJobList(){
  var sheet   =  SpreadsheetApp.openById("sheetIdHere").getSheetByName("sheetNameHere");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("B2:H"   lastRow);  // Modified
  var data    = myRange.getValues();
    var optionsHTML = "";

      for (var i = 0; i < data.length; i =1) {
 
        optionsHTML  = `<option data-values="

        ${data[i][1]},
        ${data[i][2]},
        ${data[i][3]},
        ${data[i][4]},
        ${data[i][6]}                 
        
        ">${data[i][0]}</option>`;  // Modified
      };
      console.log(optionsHTML);
      return optionsHTML;

    // return option;
}

A screenshot of my spreadsheet table:

Spreadsheet Table

I just want the dynamic dropdown to only show rows that have column G set to "ACTIVE".

CodePudding user response：

Modification points:

• If data is var data = myRange.getValues() in your script, I think that var status = ${data[i][5]}; should be var status = data[i][5];.
• In the case of the template literal, the spaces and the line breaks are reflected in the value. Please be careful about this.

When these points are reflected in your script, it becomes as follows.

From:

for (var i = 0; i < data.length; i =1) {
 var status = ${data[i][5]};

  if(status == "ACTIVE") {
   optionsHTML  = `<option data-values="

   ${data[i][1]},
   ${data[i][2]},
   ${data[i][3]},
   ${data[i][4]},
   ${data[i][6]}                 
   
   ">${data[i][0]}</option>`;  // Modified
   }else{
   
   }
 };

To:

for (var i = 0; i < data.length; i  = 1) {
  var status = data[i][5];
  if (status == "ACTIVE") {
    optionsHTML  = `<option data-values="${data[i][1]},${data[i][2]},${data[i][3]},${data[i][4]},${data[i][6]}">${data[i][0]}</option>`;
  }
}

Note:

• Although, unfortunately, I'm not sure whether I understood I just want the dynamic dropdown to only show rows that have column G set to "ACTIVE". and I cannot know your whole script, I think that in this case, the values of column "B" are shown.