仅在单击按钮时运行脚本，而不是打开工作表 [英] Run script only on click on button instead with open the sheets

问题描述

当前，以下脚本在打开Goog​​le表格的情况下运行.我希望脚本只应单击一下按钮即可运行.我该怎么办?

Currently the following script runs with opening the Google Sheet table. I want to have that the script should only run with a click of a button. How can I do that?

function getStatusCode(url){
   var options = {
     'muteHttpExceptions': true,
     'followRedirects': false
   };
   var url_trimmed = url.trim() || "";
   var response = UrlFetchApp.fetch(url_trimmed, options);
   return response.getResponseCode();
}

推荐答案

答案:

您可以使函数为您的单元格设置值而不是返回，而不必使用=IFERROR ^* 公式.

Answer:

Rather than using the =IFERROR^* formula, you can make the function set the value for your cell rather than return.

请确保更改包含您的URL的单元格范围和其中包含=IFERROR()论坛的单元格范围.在此示例脚本中，它们分别位于列G和H中.

Make sure to change the range of cells containing your URLs and the range of cells that have =IFERROR() forumae in. In this example script they are in column G and H respectively.

function getStatusCode(){
  var options = {
    'muteHttpExceptions': true,
    'followRedirects': false
  };
  // this is the range of cells containing your WENNFEHLER foruma
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("H1:H100");
  
  // this is the range of urls
  var range2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("G1:G100");
  
  var newValues = [];
  for (var i = 0; i < range.getValues().length; i++) {
    var url_trimmed = range2.getValues()[i][0].trim() || "";    
    try {
      var response = UrlFetchApp.fetch(url_trimmed, options);
      newValues.push([response.getResponseCode()]);
    }
    catch(e) {
      newValues.push(["Not found"]);
    }
  }
  range.setValues(newValues);  
}

分配按钮:

现在，您可以创建一个页面内按钮，单击该按钮即可运行该脚本.

Assigning to a Button:

Now, you can create an in-sheet button which will run the script whenever you click it.

1. 转到Insert > Drawing菜单项并创建形状；任何形状都可以，这将充当您的按钮.
2. 按Save and Close可以将其添加到工作表中.
3. 将新添加的图形移动到您想要的位置.在图形的右上角，您将看到垂直省略号菜单(⋮).单击此，然后单击Assign script.
4. 在新窗口中，键入getStatusCode并按OK.

1. Go to the Insert > Drawing menu item and create a shape; any shape will do, this will act as your button.
2. Press Save and Close to add this to your sheet.
3. Move the newly-added drawing to where you would like. In the top-right of the drawing, you will see the vertical ellipsis menu (⋮). Click this, and then click Assign script.
4. In the new window, type getStatusCode and press OK.

现在，每次单击按钮，脚本将运行.由于它设置了单元格值而不是从公式中调用，因此它将不再在打开工作表时运行.

Now, each time you click the button, the script will run. As it sets the cell value rather than being called from a formula, it will no longer run on opening the Sheet.

希望对您有帮助！

_{*翻译说明:WENNFEHLER表示IFERROR}

这篇关于仅在单击按钮时运行脚本，而不是打开工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！