Home > Mobile >  Set column value on Sheet 2 to true when criteria is met
Set column value on Sheet 2 to true when criteria is met

Time:10-06

Disclaimer: New to scripting...When the value of a cell column A on Sheet 1 matches column C on Sheet 2, another column on Sheet 2(same row) is updated with "true".

CodePudding user response:

If you don't mind to use a formula:

=IF(EQ(Sheet1!A1,Sheet2!C1),TRUE,FALSE)

Just put it any cell in row 1 and you will get TRUE if A1 == C1 or FALSE otherwise. If you copy it on another row, you will get result for the cells of that another row.

CodePudding user response:

Since I'm not sure if you want column A and column C match on the same row (e.g. A3=C3) or not (e.g. A1=C2), I have provided both possibility in the script below. See output for their difference and use either one of them depending on your case.

Script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var sheet1ColA = sheet1.getRange('A:A').getValues().flat().filter(String);
  var sheet2ColC = sheet2.getRange('C:C').getValues().flat().filter(String);
  // assuming the column we would like to update is Sheet2!D:D
  var sheet2ColD = sheet2.getRange('D:D').getValues().flat().filter(String);

  // if Sheet1ColA = Sheet2ColC and should be in the same row
  // e.g. Sheet1!A3 = Sheet2!C3
  var output = sheet2ColD.map((row, index) => [sheet2ColC[index] == sheet1ColA[index] ? true : row]);
  
  // if Sheet1ColA is present in Sheet2ColC regardless of the row
  // e.g. Sheet1!A1 = Sheet2!C2
  var output = sheet2ColD.map((row, index) => [sheet1ColA.includes(sheet2ColC[index]) ? true : row]);
  
  sheet2.getRange(1, 4, output.length, output[0].length).setValues(output);
}

Sheet1:

sheet1

Sheet2:

sheet2

Output (same row):

var output = sheet2ColD.map((row, index) => [sheet2ColC[index] == sheet1ColA[index] ? true : row]);

output1

Output (regardless of row):

var output = sheet2ColD.map((row, index) => [sheet1ColA.includes(sheet2ColC[index]) ? true : row]);

output2

  • Related