Setting a value in a drop down with Google Sheets

问题:

I need to figure out a way to set a drop down value based on if another cell in the same row has a value. So for instance if D2 has a value other than '' or null; H2 needs to be set to "New Issue". The answers found in another similar post don't actually work with this since I am using formRedirector. Things got really weird last time. I tried to delete the earlier post but couldn't.

I have some of the logic figured out, but the issue is that what I have now writes "New issue" to H3 and below. Here is what I have so far:

var NEW_ISSUE = 'New Issue';

function defaultValue() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = 900;
  var dataRange = sheet.getRange(startRow, 4, numRows);
  var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var default_status = row[7];

    if (default_status != NEW_ISSUE && row == '') {
      sheet.getRange(startRow + i, 8).setValue(NEW_ISSUE);
    }
  }
}

I have a feeling somewhere in the if statement I am messing up somewhere.

回答1:

This solved the issue, thank you

var NEW_ISSUE ='New Issue';
var row;
var default_status;

function defaultValue() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 1;
 var numRows = 900;
 var dataRange = sheet.getRange(startRow, 4, numRows);
 var data = dataRange.getValues();
 var statusRange = sheet.getRange(startRow,8,numRows);
 var status = statusRange.getValues();



 for (var i = 0; data[i] != ''; ++i)
  { 
   if (status[i]  == '')
   {
    sheet.getRange(i+startRow, 8).setValue(NEW_ISSUE);
   }
  }
}