CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jNsatiable
jNsatiable

Reputation: 119

Script for Google sheet to change a cells value based on another cells value (in a column range)

Here is the scenario: Column E in my Googlesheet has a dropdown list of Yes and No. Everytime the user answers No, I want the corresponding cell in Column G to have the words "Not Applicable". But if user answers Yes, I want that cell in G to have another dropdown list of Yes and No.

I tried to build on the script that I got from this thread: Google Sheets formula to change a cells value based on another cells value

It's almost perfect, but I can't make it to work for a range (an entire column, preferrably). Any advice would be appreciated :)

Since I wasn't getting any success trying to tweak it for my own use, here's a copy code from the mentioned thread:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="E10"){
  var val=e.range.getValue()
    if(val=="Levy"){
      s.getRange("E11").setDataValidation(null)
      s.getRange("E11").setValue("Monthly")
  }
      else{
  s.getRange('E11').clearContent()      
  var cell = s.getRange('E11');
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Triannual', 'Quarterly']).build();
  cell.setDataValidation(rule);
   }}}

And here's my dumb attempt:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="E2:E500"){
  var val=e.range.getValue()
    if(val=="No"){
      s.getRange("G2:G500").setDataValidation(null)
      s.getRange("G2:G500").setValue("Not Applicable")
  }
      else{
        s.getRange('G2:G500').clearContent()      
        var cell = s.getRange('G2:G500');
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes','No']).build();
  cell.setDataValidation(rule);
   }}}

Upvotes: 1

Views: 6718

Answers (2)

Brian
Brian

Reputation: 4344

You need to loop a range of cells, which is why the original script isn't working. .getValue() returns a single cell, you can't have a range (as in your edited script).

This script will look at the entire page and loop it each time. This is preferable because you don't have to keep data in order. In other words, you can jump around Column E and mark things "Yes" or "No" as they come up. Blank cells are ignored.

  function addValidation() {

  // Get the spreadsheet and active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // Get the data as an Array you can iterate and manipulate
  var data = sheet.getDataRange().getValues();

  // Store a rule to use for the Data Validation to be added if ColE == "Yes"
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(["Yes", "No"]).build();

  // Loop the sheet
  for(var i=0; i<data.length; i++) {

    // Test ColE. Note that the array is 0-indexed, so A=0, B=2, etc...
    // To change which columns you're testing, change the second value.
    if(data[i][4] == "Yes") {

      // If it's "Yes," add the Data Validation rule to Col G for that row.
      // Note that .getRange() is _not_ 0 indexed, which is why you need `i+1` to get the correct row

      sheet.getRange(i+1, 7).clear().setDataValidation(rule);

      // If ColE == "No," mark ColG as "Not Applicable"
    } else if(data[i][4] == "No") {
      sheet.getRange(i+1, 7).clearDataValidations().setValue("Not Applicable");
    }
  }
}

Also note that this will change values as you change Col E. So, if you change a "Yes" to a "No," Col G will be changed to "Not Applicable."

Upvotes: 1

James Sheard
James Sheard

Reputation: 139

 for (var i = 0; i < 5000; i++) {
function checkData() {
      if(SpreadsheetApp.getActiveSheet().getRange("E" + i).getValue() == "No"){
          SpreadsheetApp.getActiveSheet().getRange("G" + i).setValue('Not Applicable');
      }
      else{
    SpreadsheetApp.getActiveSheet().getRange("G" + i).setValue('Applicable');
        }
    }
}

Hope this works for you:) Btw getting the range of 5000 rows will be VERY slow! Here is another way you can do it faster!

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tsSheet = ss.getSheetByName("YOUR SHEET NAME AT THE BOTTOM OF THE PAGE");
  var tsRows = parseInt(tsSheet.getLastRow());

     for (var i = 0; i < tsRows; i++) {
    function checkData() {
          if(SpreadsheetApp.getActiveSheet().getRange("E" + i).getValue() == "No"){
              SpreadsheetApp.getActiveSheet().getRange("G" + i).setValue('Not Applicable');
          }
          else{
        SpreadsheetApp.getActiveSheet().getRange("G" + i).setValue('Applicable');
            }
        }
    }

EDIT:

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tsSheet = ss.getSheetByName("YOUR SHEET NAME AT THE BOTTOM OF THE PAGE");
  var tsRows = parseInt(tsSheet.getLastRow());

     for (var i = 0; i < tsRows + 1; i++) {
    function checkData() {
          if(SpreadsheetApp.getActiveSheet().getRange("E" + i).getValue() == "No"){
              SpreadsheetApp.getActiveSheet().getRange("G" + i).setValue('Not Applicable');
          }
          else{
        SpreadsheetApp.getActiveSheet().getRange("G" + i).setValue('Applicable');
            }
        }
    }

Upvotes: 0

Related Questions