Loop through different sheets and change background color based on same value

[Arne Ballegeer]

Hello everyone
Scenario: I have a spreadsheet with 2 sheets. In the first sheet, data comes in via a google form. There are names in the 2nd sheet. Now I want if the names of the 2nd sheet are the same as the names entered via the form, the background of the cell changes to eg green.
I can get the same names out, but it's not entirely clear to me how I can change the background color.

Screenshot of my code attached

Thks!

[Arne Ballegeer]

Ok, I got it working via emailLijst getRange. But I have to do this twice? Not really good practice?

const vergelijkenNamen = () => {

const ss = SpreadsheetApp.getActiveSpreadsheet();

//sheet leesbevestiging

const leesBevestiging = ss.getSheetByName('Leesbevestiging');

const dataLeesBevestiging = leesBevestiging.getRange(2, 2, leesBevestiging.getLastRow() - 1, 2).getValues();

//sheet E-mail lijst voor bevestiging

const emailLijst = ss.getSheetByName('E-mail lijst voor bevestiging');

const dataEML = emailLijst.getRange(2, 1, emailLijst.getLastRow() - 1, 2).getValues();

for (let i = 0; i < dataLeesBevestiging.length; i++) {

Logger.log('eerste loop: ' + dataLeesBevestiging[i])

for (let j = 0; j < dataEML.length; j++) {

console.log('tweede loop: ' + dataEML[j])

if (dataLeesBevestiging[i][0] === dataEML[j][0] && dataLeesBevestiging[i][1] === dataEML[j][1]) {

console.log('De naam komt 2x voor ' + dataEML[j]);

emailLijst.getRange(j + 2, 1).setBackground('green');

emailLijst.getRange(j + 2, 2).setBackground('green');

}

}

}

}

Op zaterdag 18 december 2021 om 18:10:43 UTC+1 schreef Arne Ballegeer:

[Laurie Nason]

Hi Arne,

Does conditional formatting not work for you?

I do this kind of thing regularly - with ID's, not usually text - but I would do the following:

On sheets 1&2 set up a conditional formatting rule that uses COUNTIF(Range1,A1) as a custom function 

• Sheet 1 - set up a conditional format that applies to the column of data you want to color.
• In the conditional formatting set the Apply to Range the column on sheet 1 you want to highlight - eg A1:A
• In the "format cells if" box - enter the formula as follows - "=countif(Sheet2!A:A,A1)"
• This currently assumes that the spelling and capitalisation for both sheets is the same - however, you can use the "Lower()" function to force both fields to lower case e.g =COUNTIF( LOWER(Sheet2!A:A),LOWER (A1)  )
  
• You can then do the opposite for the column on sheet 2 if you also want to match duplicates over on that sheet

Hopefully this helps!

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d2ddf8e6-435a-4e86-89fa-a893c013c56an%40googlegroups.com.

[Arne Ballegeer]

Hi Laurie

Thanks for the tip. However, I just want to improve my google script skills... ;)
On the other hand, I tried your formula once, but can't get it to work. This formula works with conditional formatting -> =MATCH(A1;indirect("Leesbevestiging!B2:B");0).
But it only compares 2 columns. Do you know how I can compare 2x2 columns? On sheet 1 I have 2 columns(firstname, lastname), on sheet 2 the same. I would like to compare them together with conditional formatting.
A tip?

Thank you!

Op zondag 19 december 2021 om 17:36:41 UTC+1 schreef Laurie Nason:

[David]

You can concatenate the content of the two columns and compare that instead. 

[Clark Lind]

At issue is you are dealing with 2 two-dimensional arrays.
Using both script and David's concat suggestion, make it easier on yourself by comparing two one-dimensional arrays :)  
And if you want to improve your scripting/javascript, really get to know the array methods. 

Here is an example I came up with. If you are doing this based on a form, I wouldn't do it this way; instead, I would only run the comparison using a onFormSubmit trigger, then

only compare the submitted row instead of comparing everything, every time. Anyway, my try at it: (change the sheet names to your sheet names**):

 function compare() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const leesBevestiging = ss.getSheetByName('target**');

  const tempDataleesBevestiging = leesBevestiging.getRange(2, 1, leesBevestiging.getLastRow() -1, 2).getValues();

  const emaillijst = ss.getSheetByName('emailLijst**');

  const tempDataEML = emaillijst.getRange(2, 1,emaillijst.getLastRow(), 2).getValues();

  let gelijk = [];

  let dataLeesBevestiging = [];

  let dataEML = [];

//concat names from first sheet and hold in an array

tempDataleesBevestiging.forEach((row) => {

    dataLeesBevestiging.push(row[0] + row[1])

  })

//concat names from second sheet and hold in an array

 tempDataEML.forEach((row) => {

    dataEML.push(row[0] + row[1])

  });

//use the second sheet as your "guide" since it will be the sheet whose colors change

  dataEML.forEach( (row, indx) => {

     if (dataLeesBevestiging.indexOf(row) > -1) { //if the first sheet contains the name from the second sheet, get the index so we know which row to change color on

       gelijk.push(indx +2)  //add "2" to get row number since arrays start at 0, but sheet rows start at 1 and we skipped first row

     }

    })

  gelijk.forEach( (row) => {

    emaillijst.getRange(row,1,1,2).setBackground("#00cc66"); //or whatever color string

  })

}

[Arne Ballegeer]

Thank you very much for the inspiring code. Those are things I learn a lot from.
What does break my head is your next line of code

dataEML.forEach((row, indx) => {

//console.log(row, indx)

if (dataLeesBevestiging.indexOf(row) > -1) { //if the first sheet contains the name from the second sheet, get the index so we know which row to change color on

gelijk.push(indx + 2) //add "2" to get row number since arrays start at 0, but sheet rows start at 1 and we skipped first row

}

})

--> if (dataReadConfirm.indexOf(row) > -1)

Why greater than -1?

and indx + 2...

Very confusing for me ;)

grt

Arne

Op woensdag 22 december 2021 om 17:31:24 UTC+1 schreef cwl...@gmail.com:

[Clark Lind]

Hi Arne,

 array.indexOf()  always returns a value. If not found, it returns  "-1". If found, it might be in the first position/index or "0". 

Example:  because the range starts on row two, and arrays start at an index of 0, we have to convert the index# back to the row# we want to manipulate:

All the original code really does is 1) Make both arrays simpler for comparison with each other, 2) iterate over one array, one item at a time, checking if that item is in the second array. Because the second array was derived from the sheet, when a match is found, the sheet row is implied from the index. In your case (and the above case) , the difference between the starting row and the beginning of the array is 2. 

If the data range started on row 25, we would add 25. 3) add the "difference" to each index number, then do your desired manipulation to the target row :) 

I hope that helps!  It helps me understand it better by having to explain it!

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/kGWYpVRmk9s/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bc878f87-57ca-48e0-96b0-57ef36dc9a4an%40googlegroups.com.

[Clark Lind]

It might have made more sense if I wrote it this way:

function compare() {

const startRow = 2;

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const leesBevestiging = ss.getSheetByName('target**');

  const tempDataleesBevestiging = leesBevestiging.getRange(startRow, 1, leesBevestiging.getLastRow() -1, 2).getValues();

  const emaillijst = ss.getSheetByName('emailLijst**');

  const tempDataEML = emaillijst.getRange(2, 1,emaillijst.getLastRow(), 2).getValues();

  let gelijk = [];

  let dataLeesBevestiging = [];

  let dataEML = [];

//concat names from first sheet and hold in an array

tempDataleesBevestiging.forEach((row) => {

    dataLeesBevestiging.push(row[0] + row[1])

  })

//concat names from second sheet and hold in an array

 tempDataEML.forEach((row) => {

    dataEML.push(row[0] + row[1])

  });

//use the second sheet as your "guide" since it will be the sheet whose colors change

  dataEML.forEach( (row, indx) => {

     if (dataLeesBevestiging.indexOf(row) > -1) { //if the first sheet contains the name from the second sheet, get the index so we know which row to change color on

       gelijk.push(indx +startRow)  //add "2" to get row number since arrays start at 0, but sheet rows start at 1 and we skipped first row

     }

    })

  gelijk.forEach( (row) => {

    emaillijst.getRange(row,1,1,2).setBackground("#00cc66"); //or whatever color string

  })

}

[Arne Ballegeer]

What a good explanation, thank you so much for the effort!

Happy holidays

grt

Arne

Op donderdag 23 december 2021 om 14:42:40 UTC+1 schreef cwl...@gmail.com: