compare 2 columns and set value if there is a change-CodePudding

im trying to compare 2 columns B and C and if the result is different setvalue on column D

example: https://docs.google.com/spreadsheets/d/1Y7--aDDUwSR08GtdakQIFT9QPGWGILHSaHgSdh6jsmU/edit#gid=788248808

function COMPARE()
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ss_s = ss.getSheetByName('temp');

  const LR = ss_s.getLastRow();

  const B = ss_s.getRange("B2:B"   LR).getValues();
  const C = ss_s.getRange("C2:C"   LR).getValues();

  var Data = [];
  for (var i = 0; i < LR; i  ) 
  {
    if (B[i] !== C[i])
    {
      Data.push([B[i]]);
    }
    else
    {
      Data.push([""]);
    }
  ss_s.getRange(2,4,LR,1).setValues(Data);
  }

//  if( B !== C )
//  {
//    var Data = ss_s.getRange(2,2,LR,1).getValues();
//    ss_s.getRange(2,4,LR,1).setValues(Data);
//  }
  
};

error

Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 7.
COMPARE @ compare.gs:22

i dont fully understand that as im trying to follow answers and examples ive seen around, without much sucess so far

CodePudding user response：

From your script, I understood it as follows.

When the values of columns "B" and "C" are the same, you want to put "" to the column "D". When the values of columns "B" and "C" are different, you want to put the value of column "B" to the column "D".

If my understanding is correct, how about the following modification?

Modified script:

When your script is modified, it becomes as follows. getValues() returns a 2-dimensional array. In your script, in order to use your for loop, please flat the values of B and C. And, please move ss_s.getRange(2, 4, LR, 1).setValues(Data); to the out of loop. I think that these are the reason for your error.

function COMPARE() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ss_s = ss.getSheetByName('temp');
  const LR = ss_s.getLastRow();
  const B = ss_s.getRange("B2:B"   LR).getValues().flat();
  const C = ss_s.getRange("C2:C"   LR).getValues().flat();
  var Data = [];
  for (var i = 0; i < LR; i  ) {
    if (B[i] !== C[i]) {
      Data.push([B[i]]);
    } else {
      Data.push([""]);
    }
  }
  ss_s.getRange(2, 4, LR, 1).setValues(Data);
}

Or, you can also modify as follows.

function COMPARE2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ss_s = ss.getSheetByName('temp');
  const values = ss_s.getRange("B2:C"   ss_s.getLastRow()).getValues();
  const data = values.map(([b, c]) => [b == c ? "" : b]);
  ss_s.getRange(2, 4, values.length).setValues(data);
}

Reference:

getValues()

Added:

From the following reply,

indeed the statement was ou tof place and missd the flat (which i didnt know) BUT your solution is only setting value to what changed, every thing else is "", Data.push([""] was redudancy on this example in reallity i just want to set values if any of the values change, and keep whats the same, basicly put the valueS of column B to the column D

And, about my confirmation of When the columns "B" and "C" are edited, you want to compare the column "B" and "C" of the edited row, and when the value of column "B" is different from the column "C", you want to put the value of column "B" to the column "D". Is my understanding correct?, you said as follows.

yes, @Tanaike you understood it correctly, if B changes any row values compared to C, then copy B to D

In this case, I think that in order to achieve your goal, it is required to use OnEdit trigger as follows.

Sample script:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const row = range.rowStart;
  if (sheet.getSheetName() != 'temp' || row == 1 || !(range.columnStart == 2 || range.columnStart == 3)) return;
  const [b, c] = sheet.getRange(row, 2, 1, 2).getValues()[0];
  sheet.getRange(row, 4).setValue(b == c ? null : b);
}

When you use this script, please edit the cells "B2:C" of "temp" sheet. When the script is run and the values of columns "B" and "C" are comparead and when the values are different, the value of column "B" is put to the column "D".
If you want to run the script only when the column "B" is edited, please modify if (sheet.getSheetName() != 'temp' || row == 1 || !(range.columnStart == 2 || range.columnStart == 3)) return; to if (sheet.getSheetName() != 'temp' || row == 1 || range.columnStart != 2) return;.
When you directly run this script, an error like TypeError: Cannot read property 'range' of undefined occurs. Because this script is run by the OnEdit trigger. Please be careful this.