我有一张纸，上面有一个执行应用程序脚本的按钮(绘图(。脚本读取工作表上的数据并执行一些工作。
我一直在犯同样的错误。我更改了单元格中的一些数据，然后按下按钮。由于单元格只在失去焦点时保存，并且按下按钮不会导致焦点改变，因此数据实际上没有改变。我必须更改字段，单击另一个字段，然后按下按钮。似乎无论如何都无法在按下按钮之前保存所有更改。
我如何检测到我正在进行编辑并保存我所拥有的内容，然后运行我的脚本。或者有没有一种方法可以在编辑完成之前禁用该按钮？
下面是一些实现复选框按钮的示例代码。
使用插入>Checkbox将复选框放置在单元格中，然后在cellToWatch参数中指定复选框的位置，并在通过action.run参数选中该复选框时给出要运行的函数的名称。
可以通过在triggers阵列中包含更多对象来指定多个复选框。
/**
* Checkbox buttons
*
* Use Insert > Checkbox instead of Insert > Drawing
* to implement clickable buttons in Google Sheets.
*
* Checkboxes work in Sheets on mobile as well as Sheets on web.
* Functions run this way work without explicit end-user authorization,
* but in a limited access mode where they cannot call services that
* require authorization.
*
* @see https://stackoverflow.com/a/67160138/13045193
*/
/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
if (!e) {
throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
}
checkboxButtons_(e);
}
/**
* Runs a function when a cell value changes.
*
* @param {Object} e The onEdit() event object.
*/
function checkboxButtons_(e) {
// version 1.5, written by --Hyde, 19 April 2021
// - generalize
try {
const sheet = e.range.getSheet();
const triggers = [
////////////////////////////////
// [START modifiable parameters]
{
description: 'Shows a message when the checkbox in Sheet1!B2 is ticked.',
cellToWatch: e.source.getRange('Sheet1!B2'),
triggerValue: true,
resetValue: false,
action: {
run: exampleFunction_,
parameters: {
exampleMessage: "It's alive!",
},
},
messagePost: '',
event: e,
},
{
description: 'Clears some cells when the checkbox in Sheet1!B3 is ticked.',
cellToWatch: e.source.getRange('Sheet1!B3'),
triggerValue: true,
resetValue: false,
action: {
run: exampleFunctionClearRanges_,
parameters: {
rangeListToClear: sheet.getRangeList(['C7', 'E7', 'G7', 'C8', 'E8', 'G8']),
},
},
messagePost: 'Cleared six cells.',
event: e,
},
// [END modifiable parameters]
////////////////////////////////
];
triggers.some(function (trigger) {
if (sheet.getSheetId() !== trigger.cellToWatch.getSheet().getSheetId()
|| e.range.getA1Notation() !== trigger.cellToWatch.getA1Notation()
|| e.range.getValue() !== trigger.triggerValue) {
return false;
}
trigger.action.run(trigger.action.parameters, trigger);
trigger.cellToWatch.setValue(trigger.resetValue);
if (trigger.messagePost) {
showMessage_(trigger.messagePost);
}
return true;
});
} catch (error) {
showAndThrow_(error);
}
}
/**
* Example function that shows a message in a toast.
*
* @param {Object} parameters The trigger.action.parameters object from checkboxButtons_().
* @param {Object} event The event object from checkboxButtons_().
* @return {Object} The original event object, for chaining.
*/
function exampleFunction_(parameters, event) {
showMessage_(parameters.exampleMessage);
return event;
}
/**
* Example function that clears all ranges in a range list in one go.
*
* @param {Object} parameters The trigger.action.parameters object from checkboxButtons_().
* @param {Object} event The event object from checkboxButtons_().
* @return {Object} The original event object, for chaining.
*/
function exampleFunctionClearRanges_(parameters, event) {
parameters.rangeListToClear.clearContent();
return event;
}
/**
* Shows error.message in a pop-up and throws the error.
*
* @param {Error} error The error to show and throw.
*/
function showAndThrow_(error) {
// version 1.0, written by --Hyde, 16 April 2020
// - initial version
var stackCodeLines = String(error.stack).match(/d+:/);
if (stackCodeLines) {
var codeLine = stackCodeLines.join(', ').slice(0, -1);
} else {
codeLine = error.stack;
}
showMessage_(error.message + ' Code line: ' + codeLine, 30);
throw error;
}
/**
* Shows a message in a pop-up.
*
* @param {String} message The message to show.
* @param {Number} timeoutSeconds Optional. The number of seconds before the message goes away. Defaults to 5.
*/
function showMessage_(message, timeoutSeconds) {
// version 1.0, written by --Hyde, 16 April 2020
// - initial version
SpreadsheetApp.getActive().toast(message, 'Checkbox buttons', timeoutSeconds || 5);
}
您可以使用复选框来使用onEdit(e)触发函数，这样您就可以在运行函数之前取消选择输入单元格。
或者，您可以继续使用该按钮，但检查复选框是否已选中，否则该函数将返回警告消息或其他信息。
是否可以让按钮只选择一个隐藏的复选框，该复选框使用onEdit脚本运行代码？
我不确定onEdit是否会触发，也不确定所选字段是否会取消选择，但这似乎值得一试。