Home > Net >  How do get custom functions to recalculate in google sheets?
How do get custom functions to recalculate in google sheets?

Time:10-20

I created a custom function for google spreadsheets. All it does is return a random letter. The function works great when I first enter it into a cell. But now I want to be able to "recalculate" the function using a keyboard shortcut; I'd also be willing to refresh the page if needed.

TLDR: I want to be able to hit a key and have my custom functions recalculate.

How can I accomplish this?

Edit to add: Here is the code for my function.

//returns a random letter suitable for use in function notation
function ranFunLet() {
  var letters = ['a','b','c','d','f','g','h','j','k','m','n','p','q','r','s','t','u','v','w','x','y','z']
  var letter = letters[Math.floor(Math.random()*letters.length)];
  //console.log(letter);
  return letter;
}

I would like the cell I use it in to run the function again when I press a button (or refresh the page).

CodePudding user response:

the button solution is done like this:

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

Sample script function to test

This sample script function increments the number on A1 cell.

function sample() {
  var data = SpreadsheetApp.getActive().getActiveSheet().getRange("A1").getValue();
  var res = data 1;
  SpreadsheetApp.getActive().getActiveSheet().getRange("A1").setValue(res);
}

Import the function on your spreadsheet (in my testing it is named as sample):

  1. In the Google Sheets UI, select Tools > Macros > Import.
  2. Select a function form the list presented and then click Add function.
  3. Select clear to close the dialog.
  4. Select Tools > Macros > Manage macros.
  5. Locate the function you just imported in the list. Assign a unique keyboard shortcut to the macro. You can also change the macro name here; the name defaults to the name of the function.

enter image description here

  1. Click Update to save the macro configuration.

Result

After pressing the sample shortcut key Ctrl Alt Shift 2, the function incremented the number on A1 cell from 1 to 2:

enter image description here

NOTE: You can not choose a specific shortcut & if you'll edit your function on the Apps Script editor, you would need to re-import your function again as a macro.

  • Related