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.

使用插入>Checkbox 将复选框放置在单元格中，然后在 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屋！