Search code examples
google-sheetsgoogle-apps-script

How to have a script set a column's cells to zero based on the respective values in two other columns?


I'm trying to make a script for use in a click-button macro which would zero the value of individual cells in a column based upon the values in two other columns in the same row. I have a script which has been working for a single condition, but I have no idea how to expand it to work properly with both conditions. They best I've managed is to have the first condition be checked and the second seems to be ignored.

The reason for this change comes down to me attempting to make the google sheet I'm using more user friendly (at the cost of being trickier for me to make work on the backend) because many of my friends who use this sheet are almost entirely computer-illiterate, hence the button in the first place.

Full disclosure, I have almost no idea what I'm doing, and have cobbled together the script I was using previously from answers to that prior question given here and elsewhere around the web, tweaking them with help from tutorials to fit my needs. However, I've spent many hours searching for an answer to this new problem and feel no closer to a solution than when I started. I'm not averse to learning, but I don't currently have the time to start from the ground up to gain a more complete understanding and can't seem to find where to look for this specific problem.

What follows is the script that I have working currently which checks only one condition for zeroing:

function DropAtWill() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Gifts");
  var data = sheet.getRange('J:J').getDisplayValues();
  var range = [];
  data.forEach(function(e, i){
    if (e[0] == "At Will") range.push("I" + (i + 1));
  });
  sheet.getRangeList(range).setValue('0');
}

Now I want it to also check if the corresponding value in column E is "Purchased" and ONLY zero the respective cell in column I if J is "At Will" AND E is "Purchased", so my idea was to try to use && but I've had no luck thus far. The best outcome I got from any of my attempts using && was that the condition was ignored and the script appeared to work exactly as it had prior to any change.

The following link is the only one I could find which seems to be asking the same question, but my attempts to employ the answer given there have failed, possibly due to a lack of understanding of how to adjust it for my purposes. Am I failing to understand the given answer?

Script setValue based on the values of two columns for each row


Solution

  • Here's an edit to your code that you can try

    function DropAtWill() {
      var sheet = SpreadsheetApp.getActive().getSheetByName("Gifts");
      var lastRow = sheet.getLastRow();
      var data = sheet.getRange('E:J').getDisplayValues();
      var range = [];
    
      for(i = 0; i < lastRow ; i++ ) {
        if(data[i][0] === 'Purchased' && data[i][5] === 'At Will') {
          range.push("I" + (i + 1));
        }
      }
    
      sheet.getRangeList(range).setValue('0');
    }
    

    Used getRange to generate a multidimensional array which makes it easy to reference values in the same row.