我使用一個腳本，其中我有一行與 IF 函式說：如果在選項卡“Suivi Clients”的第 21 列中有值“OUI”或“ANNULé”，則該函式執行。
if (sh.getName() == 'Suivi Clients' && rng.getColumn() == 21) {
if (rng.getValue() == 'OUI' || rng.getValue() == 'ANNULé') {
我想修改為：如果在“Suivi Clients”選項卡的第 21 列和第 12 列中有值“OUI”或“ANNULé”，則函式執行。
我試過這樣，但它不起作用。
if (sh.getName() == 'Suivi Clients' && rng.getColumn() == 12 && rng.getColumn() == 21) {
if (rng.getValue() == 'OUI' || rng.getValue() == 'ANNULé') {
床單在這里：
https://docs.google.com/spreadsheets/d/1CPcMx3Dhbqi-zO4D3jYNxO-PGjyW3iTfRo5gRmEB9p4/edit#gid=0
要修改的完整代碼在這里：
function onEdit(e) {
var sh = e.source.getActiveSheet();
var rng = e.source.getActiveRange();
if (sh.getName() == 'Suivi Clients' && rng.getColumn() == 21) {
if (rng.getValue() == 'OUI' || rng.getValue() == 'ANNULé') {
var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients Finalisé')
dest.insertRowBefore(7)
var plage = sh.getRange('C' rng.getRow() ':G' rng.getRow())
plage.copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
var plage = sh.getRange('L' rng.getRow() ':N' rng.getRow())
plage.copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
var plage = sh.getRange('P' rng.getRow())
plage.copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
var plage = sh.getRange('U' rng.getRow() ':W' rng.getRow())
plage.copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortCF()
}
}
if (sh.getName() == 'Clients Finalisé' && rng.getColumn() == 12 || rng.getColumn() == 21) {
if (rng.getValue() == 'NON') {
var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients')
dest.insertRowBefore(7)
var plage = sh.getRange('C' rng.getRow() ':G' rng.getRow())
plage.copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
var plage = sh.getRange('L' rng.getRow() ':N' rng.getRow())
plage.copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
var plage = sh.getRange('P' rng.getRow())
plage.copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
var plage = sh.getRange('U' rng.getRow() ':W' rng.getRow())
plage.copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortSC()
}
}
if (sh.getName() != 'Suivi Clients') return;
var editRange = {
top: 6,
left: 3,
right: 7
};
var thisRow = e.range.getRow();
if (thisRow < editRange.top || thisRow > editRange.bottom) return;
var thisCol = e.range.getColumn();
if (thisCol < editRange.left || thisCol > editRange.right) return;
removeEmpty()
}
function sortSC() { // Suivi Clients
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Suivi Clients");
var range = sheet.getRange("B6:W" (sheet.getLastDataRow(3)));
range.sort({ column: 7, ascending: true });
}
function sortCF() { // Clients Finalisé
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Clients Finalisé");
var range = sheet.getRange("B6:W" (sheet.getLastDataRow(3)));
range.sort({ column: 16, ascending: true });
}
Object.prototype.getLastDataRow = function (col) {
var lastRow = this.getLastRow();
if (col == null) { col = 1 }
var range = this.getRange(lastRow, col);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
};
有效的完整代碼在這里：
function onEdit(e) {
var spreadsheet = e.source;
var sh = spreadsheet.getActiveSheet();
var rng = spreadsheet.getActiveRange();
var rngRow = rng.getRow();
var rngCol = rng.getColumn();
if (sh.getName() == 'Suivi Clients') {
var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients Finalisé');
var otherCol = sh.getName() == 'Suivi Clients' ? rngCol == 12 ? 21 : 12 : '';
var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
.getRanges().map(range => range.getValue());
if ((cell == 'OUI' || cell == 'ANNULé') && (otherCell == 'OUI' || otherCell == 'ANNULé')) {
dest.insertRowBefore(7)
sh.getRangeList([`C${rngRow}:G${rngRow}`, `L${rngRow}:N${rngRow}`, `P${rngRow}`, `U${rngRow}:W${rngRow}`])
.getRanges().forEach(range => range.copyTo(dest.getRange(`${range.getA1Notation()[0]}7`), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false))
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortCF()
}
} else if (sh.getName() == 'Clients Finalisé') {
var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients');
var otherCol = sh.getName() == 'Clients Finalisé' ? rngCol == 12 ? 21 : 12 : '';
var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
.getRanges().map(range => range.getValue());
if ((cell == 'NON') || (otherCell == 'NON')) {
dest.insertRowBefore(7)
sh.getRangeList([`C${rngRow}:G${rngRow}`, `L${rngRow}:N${rngRow}`, `P${rngRow}`, `U${rngRow}:W${rngRow}`])
.getRanges().forEach(range => range.copyTo(dest.getRange(`${range.getA1Notation()[0]}7`), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false))
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortSC()
}
}
if (sh.getName() != 'Suivi Clients') return;
var editRange = {
top: 6,
left: 3,
right: 7
};
var thisRow = e.range.getRow();
if (thisRow < editRange.top || thisRow > editRange.bottom) return;
var thisCol = e.range.getColumn();
if (thisCol < editRange.left || thisCol > editRange.right) return;
removeEmpty()
}
function sortSC() { // Suivi Clients
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Suivi Clients");
var range = sheet.getRange("B6:W" (sheet.getLastDataRow(3)));
range.sort({ column: 7, ascending: true });
}
function sortCF() { // Clients Finalisé
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Clients Finalisé");
var range = sheet.getRange("B6:W" (sheet.getLastDataRow(3)));
range.sort({ column: 16, ascending: true });
}
Object.prototype.getLastDataRow = function (col) {
var lastRow = this.getLastRow();
if (col == null) { col = 1 }
var range = this.getRange(lastRow, col);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
};
uj5u.com熱心網友回復：
試試下面的代碼：
function onEdit(e) {
var spreadsheet = e.source;
var sh = spreadsheet.getActiveSheet();
var rng = spreadsheet.getActiveRange();
var rngRow = rng.getRow();
var rngCol = rng.getColumn();
var dest = spreadsheet.getSheetByName('Clients Finalisé');
var otherCol = sh.getName() == 'Suivi Clients' ? rngCol == 12 ? 21 : 12 : '';
var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
.getRanges().map(range => range.getValue());
if ((cell == 'OUI' || cell == 'ANNULé') && (otherCell == 'OUI' || otherCell == 'ANNULé')) {
dest.insertRowBefore(7)
sh.getRangeList([`C${rngRow}:G${rngRow}`, `L${rngRow}:N${rngRow}`, `P${rngRow}`, `U${rngRow}:W${rngRow}`])
.getRanges().forEach(range => range.copyTo(dest.getRange(`${range.getA1Notation()[0]}7`), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false))
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortCF()
}
};
您的代碼不起作用的原因是rng.getColumn() == 12 && rng.getColumn() == 21因為您使用的是 onEdit() 并且您只在一個單元格上進行編輯，所以從技術上講，它只能是第 12 列或第 21 列。
在代碼中，我添加了一個 var 來確定另一列，這樣您就可以同時掌握這兩列。您現在可以檢查單元格和 otherCell 是否為“OUI”或“ANNULé”。
同樣對于復制多個范圍的腳本，它使用了太多的重復代碼行，所以我試圖讓它更短。
讓我知道這個是否奏效！
以下是有關如何復制多個范圍的參考： Apps 腳本將多個范圍從一張作業表復制到另一個電子表格
編輯 - 添加代碼
正如您的評論所述：
一旦客戶的行位于“Clients Finalisé”選項卡中，我希望如果我將 L OR U 列的值從“OUI”或“ANNULé”更改為“NON”，該行將回傳到“Suivi Clients” “ 標簽。
我已經為此添加了代碼。試試下面的更新代碼：
function onEdit(e) {
var spreadsheet = e.source;
var sh = spreadsheet.getActiveSheet();
var rng = spreadsheet.getActiveRange();
var rngRow = rng.getRow();
var rngCol = rng.getColumn();
if (sh.getName() == 'Suivi Clients') {
var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients Finalisé');
var otherCol = sh.getName() == 'Suivi Clients' ? rngCol == 12 ? 21 : 12 : '';
var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
.getRanges().map(range => range.getValue());
if ((cell == 'OUI' || cell == 'ANNULé') && (otherCell == 'OUI' || otherCell == 'ANNULé')) {
dest.insertRowBefore(7)
sh.getRangeList([`C${rngRow}:G${rngRow}`, `L${rngRow}:N${rngRow}`, `P${rngRow}`, `U${rngRow}:W${rngRow}`])
.getRanges().forEach(range => range.copyTo(dest.getRange(`${range.getA1Notation()[0]}7`), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false))
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortCF()
};
} else if (sh.getName() == 'Clients Finalisé') {
var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients');
var otherCol = sh.getName() == 'Clients Finalisé' ? rngCol == 12 ? 21 : 12 : '';
var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
.getRanges().map(range => range.getValue());
if ((cell == 'NON') || (otherCell == 'NON')) {
dest.insertRowBefore(7)
sh.getRangeList([`C${rngRow}:G${rngRow}`, `L${rngRow}:N${rngRow}`, `P${rngRow}`, `U${rngRow}:W${rngRow}`])
.getRanges().forEach(range => range.copyTo(dest.getRange(`${range.getA1Notation()[0]}7`), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false))
sh.deleteRow(rng.getRow())
SpreadsheetApp.flush()
sortSC()
};
};
};
這是按照規定的條件作業的：
“Suivi Clients”選項卡在 L AND U 列中為“OUI”或“ANNULé”，以便行從“Suivi Clients”到“Clients Finalisé”
和
一旦客戶的行位于“Clients Finalisé”選項卡中，我希望如果我將 L OR U 列的值從“OUI”或“ANNULé”更改為“NON”，該行將回傳到“Suivi Clients” “ 標簽
在里面添加你的其他功能。或檢查您提供的虛擬表，我已在其中添加了完整代碼。我也測驗過。讓我知道它是否有效。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/466575.html
標籤：javascript if 语句 谷歌应用脚本 谷歌表格