Set column value on Sheet 2 to true when criteria is met-CodePudding

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:

Sheet2:

Output (same row):

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

Output (regardless of row):

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