Problem:
I'm trying to do the following:
Set the same background colors from cells in Sheet2 to the cells in Sheet1 that do have the same values as in Sheet2.
The idea is to compare all the values from Sheet2 that are also present in Sheet1, and take the premade backgrounds from cells in Sheet2 and set those premade backgrounds from Sheet2 to the cells with matching values in Sheet1.
The goal is automatically set the backgrounds based on the cells values when matching regardless of order (to avoid having to do it manually for hundreds of different matching values).
Illustration example:
In Sheet2, I have preset a range A1:A10 with 10 different values all with their unique background.
In Sheet1, I have the same 10 values as in Sheet2 and also other non matching values. Also, the 10 matching values in Sheet1 are 'scattered around' in range A1:AG20 (different range from Sheet2, could be any range).
The result I'm trying to get would be:
Other Close Questions:
I've found close questions here:
Copying background color from cells on a different tab of the same sheet
and here:
Google Sheets: Change background color of cell using content of another cell
and here:
Change cell value based on the cell's background color in Google Sheets
My Script So far:
Based on those other questions, I've come up with this code:
function myFunction() {
const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
const rngsh2 = sheet2.getRange("A1:A10");
const colorssh2 = rngsh2.getBackgroundObjects();
const valuessh2 = rngsh2.getValues();
const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const rngsh1 = sheet1.getRange("A1:G20");
const valuessh1 = rngsh1.getValues();
if(valuessh2 == valuessh1){
sheet1.getRange(rngsh1).setBackgroundObjects(colorssh2);
}
}
But it's not setting the backgrounds.
The Sample Sheet:
Here the sample sheet
What's wrong in the code above?
Many thanks for your help!
Checked Documentation:
I've checked and tried those references:
