使用复选框触发各种脚本，然后清除 [英] Using checkboxes to trigger various scripts, then clear

问题描述

我有一个 Google表格设置了表单上的两个按钮，分别附加到两个不同的脚本上.它们可以在PC上完美运行，但是不幸的是，自定义按钮在平板电脑的Google表格应用上仍然无法正常工作.我能够通过下拉框合并解决方法，但这仍然有些棘手，所以我想知道是否可以将PC和平板电脑用户都切换为复选框.

I have a Google Sheet set up with two buttons on the Form sheet, which are attached to two different scripts. They work perfectly on PC, but unfortunately, custom buttons still do not appear to work on the Google Sheets app for tablets. I was able to incorporate a workaround via a dropdown box, but that is still a bit finicky, so I'm wondering whether I could just switch both PC and tablet users to checkboxes instead.

如果选中了单元格G3中的复选框，则应运行AUTOFILL脚本并清除该复选框；随后，如果选中了G5单元格中的复选框，则应运行UPDATE脚本并清除其复选框.

If the checkbox in cell G3 is checked, the AUTOFILL script should run and the checkbox should be cleared; subsequently, if the checkbox in cell G5 is checked, the UPDATE script should run and its checkbox be cleared.

既然复选框已成为Google表格中的内容，那么最好的方法是什么?

What would be the best way of doing this, now that checkboxes are a thing in Google Sheets?

这是我当前正在使用的代码，可同时用于按钮和下拉菜单:

Here is the code I am currently using, working for both the buttons and the dropdown:

    function onEdit(e) {
      if (e.range.getA1Notation() == 'D3') {
        if (/^\w+$/.test(e.value)) {        
          eval(e.value)();
          e.range.clearContent();
        }
      }
    }

    function AUTOFILL() {
      var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
      var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form');
      var valueOfData = sheet1.getRange(sheet1.getLastRow(), 1).getValue();
      sheet2.getRange('B3').setValue(valueOfData + 1);
    } 

    function UPDATE() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var formSS = ss.getSheetByName("Form");
    var dataSheet = ss.getSheetByName("Data");

    var values = formSS.getRange("B3:B6").getValues().reduce(function(a, b) {
        return a.concat(b)
    });
    var partNum = values[0];
    var row;
    dataSheet.getDataRange().getValues().forEach(function(r, i) {
        if (r[0] === partNum) {
            row = i + 1
        }
    })
    row = row ? row : dataSheet.getLastRow() + 1;
    var data = dataSheet.getRange(row, 1, 1, 4).getValues()[0].map(function (el, ind){
      return el = values[ind] ? values[ind] : el;
      })


        var now = [new Date()];
    var newData =  data.concat(now)
    dataSheet.getRange(row, 1, 1, 5).setValues([newData]);


    formSS.getRange("B3:B6").clearContent()
    }

推荐答案

您正确地说，在单击按钮时运行脚本似乎无法在Android移动应用程序上运行.这是已经报告的问题(请参见 Android插件，但现在已经使用不推荐使用.

A you correctly said, running scripts on button clicks does not appear to work on the Android mobile app. This is an issue that has already been reported (see this and this). A common workaround used to be using Android add-ons but they are now deprecated.

为了使您的脚本使用复选框运行，您可以做的一件事就是修改onEdit函数.进行以下修改后，它将检查是否启用了任何复选框，并基于此复选框运行适当的功能，然后再次将其禁用.您可以在下面看到更新的onEdit函数:

In order to make your script run using checkbox, one thing you can do is to modify your onEdit function. After the following modifications, it will check whether any of the checkboxes is enabled, run the appropiate function based on that, and then disable it again. You can see the updated onEdit function below:

function onEdit(e) {
  var isAutofill = SpreadsheetApp.getActiveSheet().getRange("G3").getValue();
  var isUpdate = SpreadsheetApp.getActiveSheet().getRange("G5").getValue();

  if (isAutofill && isUpdate) {
    Browser.msgBox("You cannot autofill and update at once!");
    SpreadsheetApp.getActiveSheet().getRange("G3").setValue(false);
    SpreadsheetApp.getActiveSheet().getRange("G5").setValue(false);
  } else if (isAutofill) {
    AUTOFILL();
    SpreadsheetApp.getActiveSheet().getRange("G3").setValue(false);
  } else if (isUpdate) {
    UPDATE();
    SpreadsheetApp.getActiveSheet().getRange("G5").setValue(false);
  }

  if (e.range.getA1Notation() == 'D3') {
    if (/^\w+$/.test(e.value)) {        
      eval(e.value)();
      e.range.clearContent();
    }
  }
}

这篇关于使用复选框触发各种脚本，然后清除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！