我有 2 張紙： SheetA 和 SheetB 在相應的單元格中具有相同的值。當 SheetB 中單元格的值發生變化時，我正在嘗試更改 SheetA 中的單元格顏色。例如，如果單元格 A5 在兩張表中的值為 10，然后我在 SheetB 中將其更改為 5，我希望在 SheetA 中更改單元格的背景顏色
這是到目前為止的代碼
function onEdit(e){
var sheetsToWatch = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetA");
var sheetsToEdit = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetB");
var cell = sheetsToEdit.getActiveCell(); // checking the active cell
var bg = '#faec15'
for (let i = 0; i < active_sheet.length; i ) {
if (sheetsToWatch.match(sheetsToEdit[i])) {
sheetsToWatch.getRange(cell.getRow(), cell.getColumn()).setBackground(bg);
} else {
return;
}
}
}
uj5u.com熱心網友回復：
可以這樣做：
function onEdit(e){
var sheetB = e.source.getActiveSheet();
var sheetB_name = sheetB.getName();
if (sheetB_name != 'SheetB') return;
var sheetB_cell_value = e.value;
var sheetB_cell_row = e.range.rowStart;
var sheetB_cell_col = e.range.columnStart;
var sheetA = e.source.getSheetByName('SheetA');
var sheetA_cell = sheetA.getRange(sheetB_cell_row,sheetB_cell_col);
var sheetA_cell_value = sheetA_cell.getValue();
if (sheetB_cell_value != sheetA_cell_value) sheetA_cell.setBackground('#faec15');
}
uj5u.com熱心網友回復：
function onEdit(e){
const sh = e.range.getSheet();
if(sh.getName() == 'SheetB' ) {
e.source.getSheetByName('SheetA').getRange(e.range.rowStart,e.range.columnStart).setBackground("#faec15");
}
}
uj5u.com熱心網友回復：
該代碼現在有效。請在下面找到我的解決方案
function onEdit() {
var sheetsToWatch = ['SheetA'];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = sheet.getActiveCell();
var row = cell.getRowIndex();
var column= cell.getColumnIndex();
var sheetName = sheet.getName();
var matchFound = false;
for (var i = 0; i < sheetsToWatch.length; i ) {
if (sheetName.match(sheetsToWatch[i])) matchFound = true;
}
if (!matchFound) return;
var sheetToEdit = ss.getSheetByName('SheetB');
var editRow = sheetToEdit.getLastRow();
var editColumn = sheetToEdit.getLastColumn();
for (var j = 1; j <= editRow; j ) {
for (var k = 1; k <= editColumn; k ){
var formula = sheetToEdit.getRange(j,k).getFormula();
var rowi= parseInt(formula.replace(/[^0-9]/g,''),10);
if (rowi == row){
if (k == column){
sheetToEdit.getRange(j,k).setBackground('#faec15');
}
};
}
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/418071.html
標籤：