从同一工作表的不同选项卡上的单元格复制背景颜色(Copying background color from cells on a different tab of the same sheet)

从同一工作表的不同选项卡上的单元格复制背景颜色(Copying background color from cells on a different tab of the same sheet)

在谷歌表中我有2个不同信息的标签。 选项卡A有一个表我用作每人变量的摘要。 标签B以随机顺序具有每个变量的颜色编码。 我想要实现的是将选项卡A上的摘要表链接到选项卡B上的颜色编码。因此，只要每个变量的颜色在选项卡B上发生更改，它也将在选项卡A上更改。 例

是否可以将类型值保留在选项卡A的单元格内，但是复制选项卡B中的颜色编码（背景颜色）？ 我认为唯一的方法是使用自定义函数或脚本。

我尝试使用条件格式化，但问题是如果所有数据都在同一个选项卡上，我只能让它工作。

谢谢，托马斯

In google sheets I have 2 tabs with different information. Tab A has a table I am using as a summary of variables per person. Tab B has the color coding of each of the variables in a random order. What I am trying to achive is to have the summary table on Tab A be linked to the colour coding on tab B. So whenever the color of each variable changes on Tab B it will change on Tab A as well. Example

Is it possible to keep the typed value inside the cells of Tab A but copy the color coding (background color) in Tab B? I think the only way would be with a custom function or script.

I tried with conditional formating however the problem is that I can only get it to work if all the data is on the same tab.

Thank you, Thomas

最满意答案

这应该做你想要的。 它还将处理添加人员和车辆。 我也在分享我测试过的电子表格。

function colorCode() { var ss=SpreadsheetApp.getActiveSpreadsheet() var lr=ss.getSheets()[0].getLastRow() // get last row of sheet1 var lc=ss.getSheets()[0].getLastColumn() //get last column of sheet1 var lr1=ss.getSheets()[1].getLastRow() // get last row of sheet2 var lc1=ss.getSheets()[1].getLastColumn() ////get last column of sheet2 var sv=ss.getSheets()[0].getRange(1,2,1,lc-1).getValues() // get vehicles var sn=ss.getSheets()[0].getRange(2,1,lr-1,1).getValues() // get names var s1=ss.getSheets()[1].getRange(2,1,lr,lc-1)//exclude lehend var rng1=s1.getValues() // get sheet2 data var rng2=s1.getBackgrounds() // get background colors of dheet2 data var col=1 //column for vehicles om sheet1 for(var m=0;m<sv[0].length;m++){ //for each vehicle col=col+1 //add one to vehicle column for(var n=0;n<sn.length;n++){ //for each name for(var i=0;i<sn.length;i++){ //loop sheet2 data for(var j=0;j<rng1[0].length;j++){ if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name var c=ss.getSheets()[0].getRange(n+2, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1 }}}}} }

测试电子表格：

https://docs.google.com/spreadsheets/d/13zudMf86oOXHMSYg6rJGPguNfRxCr0aUHAktr0RqWzY/edit?usp=sharing

要将sheet1上的表格向下移动4行，请使用此选项。 代码中记录了这些更改。

function colorCode() { var ss=SpreadsheetApp.getActiveSpreadsheet() var lr=ss.getSheets()[0].getLastRow() // get last row of sheet1 var lc=ss.getSheets()[0].getLastColumn() //get last column of sheet1 var lr1=ss.getSheets()[1].getLastRow() // get last row of sheet2 var lc1=ss.getSheets()[1].getLastColumn() ////get last column of sheet2 var sv=ss.getSheets()[0].getRange(5,2,1,lc-1).getValues() // get vehicles//CHANGED 1 tp 5 var sn=ss.getSheets()[0].getRange(6,1,lr-5,1).getValues() // get names//CHANGED 2 to 6 and -1 to -5 var s1=ss.getSheets()[1].getRange(2,1,lr,lc-1)//exclude legend var rng1=s1.getValues() // get sheet2 data var rng2=s1.getBackgrounds() // get background colors of sheet2 data var col=1 //column for vehicles on sheet1 for(var m=0;m<sv[0].length;m++){ //for each vehicle col=col+1 //add one to vehicle column for(var n=0;n<sn.length;n++){ //for each name for(var i=0;i<sn.length;i++){ //loop sheet2 data for(var j=0;j<rng1[0].length;j++){ if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name var c=ss.getSheets()[0].getRange(n+6, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1//CHANGED +2 to +6 }}}}} }

This should do what you want. It will also handle adding people and vehicles. I am also sharing the spreadsheet I tested it on.

function colorCode() { var ss=SpreadsheetApp.getActiveSpreadsheet() var lr=ss.getSheets()[0].getLastRow() // get last row of sheet1 var lc=ss.getSheets()[0].getLastColumn() //get last column of sheet1 var lr1=ss.getSheets()[1].getLastRow() // get last row of sheet2 var lc1=ss.getSheets()[1].getLastColumn() ////get last column of sheet2 var sv=ss.getSheets()[0].getRange(1,2,1,lc-1).getValues() // get vehicles var sn=ss.getSheets()[0].getRange(2,1,lr-1,1).getValues() // get names var s1=ss.getSheets()[1].getRange(2,1,lr,lc-1)//exclude lehend var rng1=s1.getValues() // get sheet2 data var rng2=s1.getBackgrounds() // get background colors of dheet2 data var col=1 //column for vehicles om sheet1 for(var m=0;m<sv[0].length;m++){ //for each vehicle col=col+1 //add one to vehicle column for(var n=0;n<sn.length;n++){ //for each name for(var i=0;i<sn.length;i++){ //loop sheet2 data for(var j=0;j<rng1[0].length;j++){ if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name var c=ss.getSheets()[0].getRange(n+2, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1 }}}}} }

Test spreadsheet:

https://docs.google.com/spreadsheets/d/13zudMf86oOXHMSYg6rJGPguNfRxCr0aUHAktr0RqWzY/edit?usp=sharing

To shift the table on sheet1 down 4 rows use this. The changes are noted in the code.

function colorCode() { var ss=SpreadsheetApp.getActiveSpreadsheet() var lr=ss.getSheets()[0].getLastRow() // get last row of sheet1 var lc=ss.getSheets()[0].getLastColumn() //get last column of sheet1 var lr1=ss.getSheets()[1].getLastRow() // get last row of sheet2 var lc1=ss.getSheets()[1].getLastColumn() ////get last column of sheet2 var sv=ss.getSheets()[0].getRange(5,2,1,lc-1).getValues() // get vehicles//CHANGED 1 tp 5 var sn=ss.getSheets()[0].getRange(6,1,lr-5,1).getValues() // get names//CHANGED 2 to 6 and -1 to -5 var s1=ss.getSheets()[1].getRange(2,1,lr,lc-1)//exclude legend var rng1=s1.getValues() // get sheet2 data var rng2=s1.getBackgrounds() // get background colors of sheet2 data var col=1 //column for vehicles on sheet1 for(var m=0;m<sv[0].length;m++){ //for each vehicle col=col+1 //add one to vehicle column for(var n=0;n<sn.length;n++){ //for each name for(var i=0;i<sn.length;i++){ //loop sheet2 data for(var j=0;j<rng1[0].length;j++){ if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name var c=ss.getSheets()[0].getRange(n+6, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1//CHANGED +2 to +6 }}}}} }