Yes, it's possible! In this post we see how to create Excel macros in Google Sheets, and more specifically, we will compare the two leading spreadsheet applications, side by side, in regards to automation and the creation of macros. It is possible to replicate Excel macros in Google Sheets in many cases, yet there are some limits to achieve everything we do in Excel. On the other hand, macros in Google Sheets allow to easily integrate with other Google APIs and web resources, and can even be taken to another level to create entire web applications. However, the programming language we use to code the macros is quite different altogether. Microsoft Excel uses Visual Basic for Applications (VBA), while Google Sheets uses Google Apps Script. This post will help you leverage your Excel VBA knowledge to quickly get you up to speed with creating macros in Google Sheets.
Programming Environment
A programing environment or IDE (Integrated Development Environment) provides the user interface for code development, testing, and debugging. Microsoft Excel, and other Microsoft Office applications, count with an embedded IDE to create and edit macros; that’s the Visual Basic editor. We can access the Visual Basic editor in Excel through the Developer tab or pressing Alt + F11. If you do not know what the developer tab is, check out this page to read more and see how to add it to the ribbon.
The programming environment to code and edit macros in Google Sheets is the Google Apps Script editor. We can access the Google Apps Script editor through the tab Extensions -> Apps Script (at the time of writing this article – this has changed in the past and may change again). As with Excel, it is also possible to record macros in Google Sheets. The option to record a macro is also available under Extensions -> Macros.
The Visual Basic editor in Microsoft Excel allows to write and organize the code inside modules. There are different types of modules that serve as containers for code with different purposes. Furthermore, Excel’s Visual Basic editor allows to create and code powerful user forms. The macro code is added to the modules (or user forms) in the form of procedures. The Sub procedure is the most common type of procedure or macro which executes the VBA code between the start and end sub tags. The modules are on the left, under the Visual Basic project window, while the code is on the right hand side window. See more about modules and procedures in this other page.
The Google Apps Script editor has an equivalent to modules in the form of embedded files that contain the code. The files also appear on the left hand side window, while the code is written on the right. By default, a single file is shown (code.gs). Additional files can be added if needed, either with .gs (Google Script) or .html extensions. Other libraries and services can also be added here. Regular macros are written as functions inside the .gs files, while html files are used to create user forms within the Google spreadsheet, or stand-alone web apps. The functions with code may be seen as the equivalent to VBA procedures in Excel.
Programming Language
The programming language to write macros in Excel is Visual Basic for Applications (VBA), which is a subset of Visual Basic. On the other hand, the programming language to create macros in Google Sheets is Google Apps Script, which is quite similar to JavaScript. Both programing languages are object-based and can change the state of objects and apply certain actions to objects through properties or attributes, and methods or functions. For example, if we want to get the name of the active sheet in Google Sheets we would write the following.
SpreadsheetApp.getActiveSheet().getName();
That would actually be the same as writing the following with Excel VBA. However, note that such code would trigger an error in Excel unless assigned to a variable or displayed in a message box.
Application.ActiveSheet.Name
Excel VBA does not require to reference the Application object in this example, while it is necessary to do so in Google Apps Script. Another thing we may want to do is to store the name of the sheet in a variable so that we can use it later in the code. Again, it is quite similar to do that in Google Apps Script and Excel VBA, the only difference is the variable declaration. We should precede the name of the variable with “var” in Google Apps Script, in the exact same way it’s done with JavaScript (prior to using const and let). But it actually works also without it, as it does in Excel VBA if we do not declare the variable in advance.
sheetName = SpreadsheetApp.getActiveSheet().getName();
or
var sheetName = SpreadsheetApp.getActiveSheet().getName();
And here’s how we do it in Excel VBA. When the variable is not declared or a type is not specified in advance, Excel assigns the type Variant to the variable.
If we want to see the value of that variable when running the macro (while testing, for example), we can write the variable to the execution log in Google Apps Script or to the Immediate window in Excel VBA. There are other options or debugging tools to monitor expressions and variables in Excel, such as the Locals or Watch windows. But if we just want to see what’s the value of the variable at a certain moment, we use Debug.Print to display the value in the immediate window.
Debug.Print (sheetName)
We use Logger.log() to do the same in Google Apps Script, and if you are familiar with JavaScript, that’s basically the same we use in JavaScript to write a message to the console (usually the browser console accessible when pressing F12), which actually also works in the newest version of Google Apps Script and is console.log().
Logger.log (sheetName);
We may also want to display that in a message box. We use the MsgBox function to do so in Excel VBA. On the other hand, we need to access the spreadsheet UI in Google Sheets to display an alert dialog as indicated below.
MsgBox sheetName
vs
SpreadsheetApp.getUi().alert(sheetName);
How to run macros
There are several ways to run a macro in Excel. Check out this other post to see 10 different ways to run a VBA macro in Excel. There are also several ways to run a macro in Google Sheets. The fastest way is to use the “play” button to execute the macro or function right from the Google Apps Script editor. This is usually done while testing or debugging, and works similarly to the “play” button to run a macro in Excel from the Visual Basic editor.
We can also run a macro or function in Google Sheets from the list of available macros in the Extensions tab of the ribbon. We may need to import the macro first to be added there, and furthermore, we usually need to grant permissions when running a macro the first time.
We can also run a macro from a shape or picture in Google Sheets, as we often do in Excel too. After adding a shape (Insert->Drawing->Shape), or any other type of drawing or image, we click the three dots and assign a script. That’s the exact name of the function (case sensitive) written in the Google Apps Script editor.
Finally, we need to save the Excel file as macro-enabled to store VBA projects and macros. That’s a .xlsm file extension instead of the .xlsx of a regular Excel file. On the other hand, we do not need to worry about that in Google Sheets, the spreadsheet name is always the same.
Interested to know more about creating Excel macros in Google Sheets? Please write in the comments, let me know if there are any specific macros you need to automate in Google Sheets.
No comments:
Post a Comment