Assigning custom Key Combos w/ Google Sheets [duplicate]

Question

I am trying to assist in moving over to Google Sheets from Excel. The one concern is that we currently have some users who are affluent in Excel and are concerned that their macros where they assign keystroke combos will not be able to move with their workbooks.

I know that macros may be recorded with the Google Sheets, but I am hoping to make this move as painless as possible. It would be a WIN to allow them to use the combos they have grown to know and love.

For example; Ctrl + Shift + R will currently make text in a cell Bolded and Red. I am trying to understand how to best write this out in Script. I do have this so far...

function myFunction() {
  
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setFontWeight('bold')
  .setFontColor('#ff0000');
};

Any suggestion to assigning the specific keys would be marvelous! I have looked at a couple articles but they were older and seemed to say that it couldn't be done and then made reference to the recording macros.

Answer 1

In Google Sheets, you can manage macros through Extensions > Macros. The keyboard shortcuts you can assign are limited to Control + Alt + Shift + (number key). On a Mac, macro shortcuts are like ⌘ + Option + Shift + (number key). You cannot assign keyboard shortcuts like Control + Shift + R.

You can make Google Sheets use keyboard shortcuts that are familiar to Microsoft Excel users. See Enable compatible spreadsheet shortcuts.

To learn how to convert macros, see Switching to Sheets from Microsoft Excel, Automate tasks in Google Sheets and Bruce Mcpherson's VBA to Google Apps Script guide.

Note that you may not need to actually convert all your VBA macros to Google Apps Script. In many cases, you can use Google Sheets array formulas to get the same functionality. So before you start converting your code, take a look at whether you can accomplish the same with the likes of filter(), query(), unique(), sort(), sortn() or arrayformula().

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.