Google如何在运行带有按钮的脚本之前确保如何完成字段更改 [英] Google sheets how do I ensure field change done before running script with button

问题描述

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

I have a sheet with a button (drawing) that executes an apps script. The script reads the data on the sheet and does some work.

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

I keep making the same mistake. I change some data in a cell then push the button. Since the cell only saves on lost focus, and the button push does not cause a change of focus, the data has not actually changed. I have to make the change to the field, click on another field then push the button. There does not seem to be anyway to save all changes before the button press.

如何检测到我正在编辑中，并保存我先保存的内容，然后运行脚本.还是有一种方法可以禁用按钮，直到完成编辑?

How can I detect that I am mid edit and save what I have first then run my script. Or is there a way to disable the button until edit is done?

推荐答案

以下是一些用于实现复选框按钮的示例代码.

Here is some sample code to implement checkbox buttons.

使用插入>复选框以将复选框放置在单元格中，然后在 cellToWatch 参数中指定复选框的位置，并提供通过 action选中该复选框时要运行的函数的名称.run 参数.

Use Insert > Checkbox to place checkboxes in cells, then specify the location of a checkbox in the cellToWatch parameter, and give the name of the function to run when that checkbox is ticked through the action.run parameter.

您可以通过在 triggers 数组中包含更多对象来指定多个复选框.

You can specify multiple checkboxes by including more objects in the triggers array.

/**
* 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);
}

这篇关于Google如何在运行带有按钮的脚本之前确保如何完成字段更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！