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]);