How to create custom keyboard shortcuts for google app script functions?

Question

I'm trying to get a sense of the viability of replacing some of my Microsoft Excel spreadsheets with Google Doc Spreadsheets. How can I create a custom keyboard shortcut to a google app script function in a google docs spreadsheet? This is something I commonly do with VBA and Excel.

Answer 1

Apps Script only exposes server side events. Unfortunately, you cannot register client side events like keyboard strokes today. Please log an issue in our issue tracker

Answer 2

This now supported in Sheets (see https://issuetracker.google.com/issues/36752620), but not yet in Docs. See and star https://issuetracker.google.com/issues/36752620 for Docs support

Answer 3

Very recently (April 2018) Google launched a macro recorder that includes a way to assign a keyboard shortcut to fire a macro and a way to import existing scripts as macros. See Google Sheets Macros

NOTE: This feature is currently being rolled out so it could take few weeks to be available for all. In my case it was available first on my personal Google account and since yesterday it is available on one of my G Suite accounts.

Answer 4

A solution has been posted over at issue 306! For the lazy, here it is:

The new IFRAME mode in HtmlService does allow for key codes to be passed on to Add-ons...

$(document).keydown(function(e){
  //CTRL + V keydown combo
  if(e.ctrlKey && e.keyCode == 86){
    $( '#output' ).html("I've been pressed!");
  }
})

Have to click on / activate the sidebar first for that to happen.

Answer 5

Great news :) You can achieve custom keyboard shortcuts for google app script functions following next easy steps:

1. In the Google Sheets UI, select Tools > Macros > Record Macro.
2. Record any action. For instance, change the color background in A1. Press Save.
3. Save it with a random title and the preferred shortcut. You can change it later.
4. Select Tools > Script editor to open the script bound to the sheet in the Apps Script editor.
5. In the new editor tab, select View > Show Manifest File
6. TA - DÁ! :D Change the functionName value to the desired one ;)

Remember macro functions should take no arguments and return no values. More info at https://developers.google.com/apps-script/guides/sheets/macros#importing_functions_as_macros

Answer 6

Just updating, it is now possible the workaround that The Guy mentioned, trough IFRAME, you can create a sidebar, a STUB just to enter keyboard commands, treat them with jquery, and run the apropriate function, been using this already.

Answer 7

One possible work around for this would be to dedicate a column for "trigger text", define different text based triggers for each action you're trying to perform, and then create a function that checks the value and performs an action based on the "trigger text". You can then set an onEdit event trigger in the project's triggers under "Resources" in the script editor for your hotkey function.

The biggest downside to this approach is that it takes (at least for me) approximately 7 full seconds for the onEdit trigger to catch the change and perform the update. If you need something to process more quickly you may have to look for an alternate approach.

I've provided an example below for how to change row color based on trigger text in a designated "trigger" column. You can use this to do anything that can be done on the sheet via scripting such as change values, set font weight, copy data, or even run other functions after checking the trigger text input.

/*you will need to add an onEdit trigger to your project 
  for this to run when you edit the cell*/


//function to update row color using entered text in a specified "trigger" column

function hotKey(){ 

  //get the cell you edited and the associated column and row number
  var cell = sheet.getActiveCell(); 
  var thisCol = cell.getColumn(); 
  var thisRow = cell.getRow(); 

  //set a range variable for the entire row
  var colorRow = sheet.getRange(thisRow,thisCol,1,Cols); 

  //get the edited value for the cell as a string
  var val = cell.getValue().toString(); 

  //check that the edited cell is in the trigger column
  if (thisCol = 1){

    //update the row color based on the entered value 
    if(val == "g"){ 
      colorRow.setBackground("#00ff00"); //sets row color to green
      cell.clearContent(); //delete the trigger cell value

    }else if(val == "r"){
      colorRow.setBackground("#ff0000");
      cell.clearContent();
    }else if(val == "fd"){
      colorRow.setBackground("#fff2cc");
      cell.clearContent();
    }else if(val == "pr"){
      colorRow.setBackground("#ffff00");
      cell.clearContent();
    }else if(val == "cn"){
      colorRow.setBackground("#6fa8dc");
      cell.clearContent();
    }
  }
}

Answer 8

I'm struggling with a similar issue and tho I haven't got much resolved yet I think a way forward can be found thru this keypress event handler under Class Textbox

I don't know if this gets around the problem of server side only that Arun pointed out but I'm sure hoping so. Please feel free to correct my reasoning before I waste too much time trying this! :)

Answer 9

Long story short: write an html service which uses a key press event handler to capture keypress events and compares them to the hotkey shortcuts you want then calls the appropriate sub routine on the server side in apps script to inline the called for keystrokes, format or document changes.

As far as viability goes, not as easy as the vba you are already familiar with but once you get past the different way of doing things not really that terribly difficult.