How to correctly update the cell based on a condition?

Question

I ran into troubles trying to make my script work and was wondering if anyone could help me figure it out.

I am working on a simple script in Google Sheets which would overwrite a D column cell in one row if columns A, B, C, E and F are the same for both rows. The screenshot of the example sheet can be seen here. Row 2 is made based on the original reservation, where columns A-F are filled by an external program and columns G and H are later filled by Appsheet. If the reservation status changes, the external program will create a new row with columns A-F with updated reservation status. My idea is to have a script which would replace a cell in the D column of original reservation if it finds another row in data which has the same driver name, car, event, date and duration.

I have tried to base my script on duplicate delete script, but as an output, it deletes the "updated" row without replacing the D cell in the original. Any help would be hugely appreciated!

//overwrite old rows with updated rows
function update() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var update = false;
    for(j in newData){

      if(row.slice(0,2).join() == newData[j].slice(0,2).join() && row.slice(4,5).join() == newData[j].slice(4,5).join() && row.slice(3).join() !== newData[j].slice(3).join() ){
        update = true;
      }
    }
    if(!update){
      newData.push(row);
    }
  }


  // Clear the existing info and update with newData.
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Iamblichus Iamblichus · Accepted Answer · 2019-11-28T11:49:42

Assuming that:

You want to compare columns A, B, C, E, F from the different rows.
If all these values match, and values in D don't, you want to copy the value of column D from the row with higher index to the one with lower index and remove the row with higher index.
You have date and time values in columns E and F.

If all these are correct, you can try this:

function update() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var rowsToDelete = []; // Array to store the rows to be deleted (this avoids the deletion of rows messing with the loop indexes)
  for (var i = 1; i < data.length; i++) {
    var firstRow = data[i];
    for (var j = i; j < data.length; j++) {
      var secondRow = data[j];
      if (i != j && firstRow[0] == secondRow[0] && firstRow[1] == secondRow[1] && firstRow[2] == secondRow[2] && firstRow[4].getTime() == secondRow[4].getTime() && firstRow[5].getTime() == secondRow[5].getTime() && firstRow[3].toLowerCase() != secondRow[3].toLowerCase()) {
        sheet.getRange(i + 1, 4).setValue(secondRow[3]); // Copying value D from higher to lower index
        rowsToDelete.push(j + 1); // New row to be removed
      }
    }
  }
  rowsToDelete.sort(function(a, b){return b - a}); // Sorting the rows to delete from higher to lower (to avoid messing with loop indexes)
  for (var r = 0; r < rowsToDelete.length; r++) {
    sheet.deleteRow(rowsToDelete[r]); // Deleting rows
  }
}

You can check the inline comments to know what the code is doing, step by step.

Please note that, to compare values in columns E and F, you should use getTime() or valueOf() (otherwise these conditions will return false).

I hope this is of any help.

How to correctly update the cell based on a condition?

1 Answers