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:
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
):
- In the Google Sheets UI, select Tools > Macros > Import.
- Select a function form the list presented and then click Add function.
- Select clear to close the dialog.
- Select Tools > Macros > Manage macros.
- 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.
- Click Update to save the macro configuration.
Result
After pressing the sample shortcut key
Ctrl Alt Shift 2
, the function incremented the number onA1
cell from1
to2
:
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.