Run script only on click on button instead with open the sheets

Question

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();
}

I didnt set up anything. I just put in this script in the script editor and it runs by open the sheets. — johny
– johny, Commented Mar 31, 2020 at 10:12
Is this the only function in your project? Do you have any installable triggers viewable on the project triggers console? Do you have any formulae in the Sheet that you're opening? — Rafa Guillermo
– Rafa Guillermo, Commented Mar 31, 2020 at 10:30
Yes, this is the only function in my project and there is no triggers on the project triggers console but yeah, I use the function with =WENNFEHLER(getStatusCode(G3);"Not Found") for all cells in a column. Is this the problem? And if yes, how can I do that better? — johny
– johny, Commented Mar 31, 2020 at 10:33
as far as I know this script as standalone wont run upon opening unless you change function getStatusCode(url){ to function onOpen(url){ therefore only thing that runs this script is your WENNFEHLER formula — player0
– player0, Commented Mar 31, 2020 at 10:57

Nimantha · Accepted Answer · 2021-11-06 02:46:22Z

1

Answer:

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

Code:

Make sure to change the range of cells containing your URLs and the range of cells that have =IFERROR() formula 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 formula
  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.

Go to the Insert > Drawing menu item and create a shape; any shape will do, this will act as your button.
Press Save and Close to add this to your sheet.
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.
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.

_{* Translation note: WENNFEHLER means IFERROR}

edited Nov 6, 2021 at 2:46

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Mar 31, 2020 at 11:11

Rafa Guillermo

15.5k3 gold badges23 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

johny Over a year ago

Ok thank you! I replaced my script with yours. My IFERROR formula was in column F and started with cell F3, so I changed range to getRange("F3:F100"). What should I do now to run the script?

Rafa Guillermo Over a year ago

Edited my answer to explain how to make a drawing and run it :)

johny Over a year ago

Ok, cool! It seems to work now for the first 100 cells – I test it now for 600 cells and this need time I think. There is still the alert "Script running". I have a last question. I need this same function for several columns in this sheet. In which way I should extend the script you wrote for that? Really thank you for your help – it saves my day :)

Rafa Guillermo Over a year ago

You might start to run into execution time issues if you have so many columns as Apps Scripts have execution time limits. Could you share a sanitised copy of your Sheet so to have a look?

Rafa Guillermo Over a year ago

Yes, but your original question was actually about running a script on button and not on open. This has been addressed (the script was running because you were using a formula to get its return). If you want to do this now for multiple columns the script has to be edited away from what the original question wanted to get done, and by increasing the range of checked values you may start to hit new errors such as script execution time limits which are all best addressed as a new question.

|

Collectives™ on Stack Overflow

Run script only on click on button instead with open the sheets

1 Answer 1

Answer:

Code:

Assigning to a Button:

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Answer:

Code:

Assigning to a Button:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related