有没有一种方法可以将onEdit函数应用于谷歌表单应用程序脚本中的多个单独范围

Question

注意-我是编码新手，如果我问了一些愚蠢或格式错误的问题，请提前道歉。

目标-在谷歌表单中创建一个具有单选按钮功能的表格(而不是列表((意味着一次只能选择一个复选框。(

代码-这是用于复选框列表的代码(1个单一范围(-

function onEdit(e){
if(e.source.getActiveSheet().getName() != "Sheet1" || e.range.columnStart != 2 || e.value == "FALSE") return;
for(var i = 2;i<21;i++){
if(i == e.range.rowStart) continue;
e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
}
}

问题-有没有一种方法可以将onEdit函数应用于谷歌表单应用程序脚本中的多个单独区域？

示例表：在本例中，您将看到表中有多个复选框范围：D3:H3、D5:H5、D7:H7、D9:H9

示例表

非常感谢！

Answer 1

以下是经过修改的脚本，可用于更大的范围。

function onEdit(e){
if (!e) throw "do not run from editor";
radioButtons(e);
}
function radioButtons(e){
const src = e.source.getActiveSheet();
const r = e.range;
const row = r.rowStart;
const col = r.columnStart;
if (src.getName() != "Sheet1" || row < 3 || row > 9 || col > 5 || e.value != "TRUE") return;
let range = src.getRange("A3:E9").getValues();
for (let i = 0; i < range.length; i=i+2){
for (let j = 0; j < range[i].length; j++){
if (range[i][j] == true && i+3 != row && j+1 != col){
src.getRange(i+3,j+1).setValue("FALSE")
return;
}
}
}
}

Answer 2

像这样的通用函数可能会有所帮助：

/**
* @typedef {Object} startEnd
* A object denoting limits of  a row or column
* @property {number} start
* @property {number} end
*/
/**
* @typedef {Object} runcriteria
* @description Describes which rows/columns to run this function
* @property {startEnd} row
* @property {startEnd} column
*/
/**
* A criteria checker for event object e
* @author TheMaster
* @see https://stackoverflow.com/a/73681716
* @param {GoogleAppsScript.Events.SheetsOnEdit}  e
* @param {Object<string,Array<runcriteria>> } run_in
* @returns {{result:boolean,editedSheet:GoogleAppsScript.Spreadsheet.Sheet}}
*/
const isEditInMyCriteria_ = (
e,
run_in = {
Sheet1: [
/*Run in Sheet1!D2:I9 or Sheet1!!A9:Infinity*/
{
/*2:9*/ row: { start: 2, end: 5 },
/*D:I*/ column: { start: 4, end: 9 },
},
{ /*Anywhere after row9*/ row: { start: 9 } },
],
Sheet2: [/*Run everywhere in Sheet2*/ {}],
Sheet3: [
{ /*Only in rows 5 to rows10 */ row: { start: 5, end: 10 } },
{
/*or D15:E25*/ row: { start: 15, end: 25 },
column: { start: 4, end: 5 },
},
],
}
) => {
const criteriaSheetNames = new Set(Object.keys(run_in)),
editedRange = e.range,
editedSheet = editedRange.getSheet(),
editedSheetName = editedSheet.getName(),
isEditedSheetInRun_in = criteriaSheetNames.has(editedSheetName),
runcriteria = isEditedSheetInRun_in && run_in[editedSheetName],
{ rowStart, columnStart } = editedRange,
gte = (a, b, e) =>
a >= (b[e]?.start ?? -Infinity) && a <= (b[e]?.end ?? Infinity);
return {
result:
runcriteria &&
runcriteria.some(
(obj) => gte(rowStart, obj, 'row') && gte(columnStart, obj, 'column')
),
editedSheet,
};
};

要测试，

const onEdit = e => console.log(isEditInMyCriteria_(e).result)

使用

const onEdit = e => {
const {result, editedSheet} = isEditInMyCriteria_(e);
if(result){
//This edit satisfies all my criteria
//let's do some damage...
//uncheck all of D3:H3, D5:H5, D7:H7, D9:H9
editedSheet.getRangeList(["D3:H3", "D5:H5", "D7:H7", "D9:H9"]).uncheck()
//recheck current checkbox
e.range.check();
}
}

Answer 3

这是两张不同纸张的onEdit

function onEdit(e){
const sh = e.range.getSheet();
if(sh.getName() == "Sheet1" && e.range.columnStart == 2 && e.value == "TRUE" {
e.range.setValue("FALSE")
}
if(sh.getName() = "Sheet2" && e.range.columnStart == 4 && e.value) {
e.range.offset(0,1).setValue(new Date()); //puts a timestamp in column5
}
}