google-apps-script - Google Sheets 如何确保在使用按钮运行脚本之前完成字段更改

Question

我有一张带有执行应用程序脚本的按钮(绘图)的工作表。该脚本读取工作表上的数据并执行一些工作。

我总是犯同样的错误。我更改单元格中的一些数据，然后按下按钮。由于单元格仅在失去焦点时进行保存，并且按下按钮不会导致焦点发生变化，因此数据实际上并未发生变化。我必须对该字段进行更改，单击另一个字段，然后按下按钮。在按下按钮之前似乎无法保存所有更改。

我如何检测到我正在编辑并首先保存我所拥有的内容，然后运行我的脚本。或者有没有办法在编辑完成之前禁用该按钮？

Answer 1

这里是一些实现复选框按钮的示例代码。

使用Insert > 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);
}