Conditionally edit cells based of the value in cells in other rows - Google Sheet using Google Script

user_15309432 0
Viewed 38

I've got limited knowledge of google script and I'm trying to get better every day. I'm trying to conditionally set the value of 2 cells based on the value in cells contained in other rows. I've got a sample sheet (here) with appointments on it. You'll also see the output desired on the output sheet.

When two or more appointments are taken by the same person based on his email address I want to write Yes in column Duplicate for every duplicate appointments but the most recent (based on Column E, that is the date when the appointment was created) and that are greater than the current date (if the appointment is already in the past no need to do anything). I also want to set the value of the column L to "Not Coming" which is a cell containing a data validation that I already automated on my main spreadsheet.

Here is the script that I already designed based on other questions answered here on stackoverflow. I'm not really familiar with indexes and how to proceed with them. The script runs without errors but nothing happens.

var currentDate = new Date()

// Master

var sheetMaster = ss.getSheets()[0];
var allValues=sheetMaster.getRange(2,1,sheetMaster.getLastRow()-1,sheetMaster.getLastColumn()).getValues();
var emailValues=sheetMaster.getRange(2,3,sheetMaster.getLastRow()-1,3).getValues();
var dateCreatedAtValues=sheetMaster.getRange(2,5,sheetMaster.getLastRow()-1,5).getValues();
var duplicateColumn=sheetMaster.getRange(2,11,sheetMaster.getLastRow()-1,11);

var eM=[];//emails
var dA=[];//dates
var eR=[];//entire rows
var dC=[];//duplicateColumn Yes or empty 

function analyzeDuplicateEntries() {
  
  for(var i=0;i<emailValues.length;i++) {
    var idx=eM.indexOf(emailValues[i][0]);
    if(idx==-1) {
      eM.push(emailValues[i][0]);
      dA.push(dateCreatedAtValues[i][0]);
      eR.push(allValues[i]);
      }
    else if(new Date(dateCreatedAtValues[i][0]).valueOf() > new Date(dA[idx]).valueOf() && new Date(dateCreatedAtValues[i][0]).valueOf()> currentDate) {
     duplicateColumn[i][0].setValue("Yes");
    }
    }
} ```
google-apps-script google-sheets
1 Answers

You are retrieving the wrong column and set the values to a range cell incorrectly

  • var mailValues=sheetMaster.getRange(2,3,sheetMaster.getLastRow()-1,3).getValues(); will return columns 3 to 5 (see documentation, while your emails are in column B, that is column 2.
  • Pay attention that the first parameter in getRange(row, column, numRows, numColumns) is the number of the columns to retrieve, rather than the last column
  • Mind that to use setValue on a range that contains more than one cell, you need to retrieve first the respective cell with getCell()
  • Take thereby into consideration that the cell indexes start with 1 (opposed to array indexes that start with 0`).

A simple (not optimal) way to rewrite your code would be:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentDate = new Date()
  
  // Master
  
  var sheetMaster = ss.getSheets()[0];
  var allValues=sheetMaster.getRange(2,1,sheetMaster.getLastRow()-1,sheetMaster.getLastColumn()).getValues();
  var emailValues=sheetMaster.getRange(2,2,sheetMaster.getLastRow()-1,1).getValues();
  var dateCreatedAtValues=sheetMaster.getRange(2,5,sheetMaster.getLastRow()-1,1).getValues();
  var duplicateColumn=sheetMaster.getRange(2,11,sheetMaster.getLastRow()-1,1);
  
  var eM=[];//emails
  var dA=[];//dates
  var eR=[];//entire rows
  var dC=[];//duplicateColumn Yes or empty 
  
  function analyzeDuplicateEntries() {
    for(var i=0;i<emailValues.length;i++) {
      var idx=eM.indexOf(emailValues[i][0]);
      if(idx==-1) {
        eM.push(emailValues[i][0]);
        dA.push(dateCreatedAtValues[i][0]);
        eR.push(allValues[i]);
        }
      else if(new Date(dateCreatedAtValues[i][0]).valueOf() > new Date(dA[idx]).valueOf() && new Date(dateCreatedAtValues[i][0]).valueOf()> currentDate) {
       duplicateColumn.getCell(i+1, 1).setValue("Yes");
      }
      }
  }
user_115997890
Related