Stepping through code in Google Apps Script (equivalent VBA-GAS )

Frank Montemorano picture Frank Montemorano · Jun 24, 2013 · Viewed 11.8k times · Source

When writing my VBA macros I often used "F8" so as to debug the macro line by line. Is there a similar feature in Google Apps Script?

Answer

Mogsdad picture Mogsdad · Jun 24, 2013

Similar, but not the same.

Google Apps Script is developed in a dedicated IDE1 called the Script Editor, which provides support for single-step execution and breakpoints, among other things. Screenshot - Script Editor

For a quick introduction to debugging using the IDE, see this video. The Troubleshooting section of the online documentation includes a quick overview of the basics.

Debugging Functions with parameters

In the IDE you can select any function in your script for execution, and then select the "run" or "Debug" icons to start. Unfortunately, there is no way to pass parameters to a function this way, so here are a few ways you can deal with that.

  1. Set defaults. There are numerous ways to define defaults in javascript, here is a picture of the debugger operating on a function using the simplest of them. The function toText() from this answer accepts a number as a parameter, so for this example we are forcing the default value to 21. The picture shows the debugger paused at line 40; if we continue stepping through the function we expect to end up with a result s == 'Twenty-one'.

    Screenshot - OR for default

  2. Write a test function. This is a better approach than setting defaults, because it allows you to write multiple test cases, and it avoids polluting your target function with debug code. As an example, the target function flipFlopAndFly() and its test function test_flipFlopAndFly() were provided in this answer. The test function accesses the spreadsheet to provide appropriate data for testing the target, so we can easily modify the data for different tests. Note also, this function includes error checking, so it would not be appropriate to test by forcing default values.

There are many variations on these basic patterns, so feel free to adapt them to your own situation. It will help your debugging capability to think while you are writing about how you would step through your code:

  • Is each important object and value stored in a var, so you can see it?
  • Is your function result (return value) in a var?

Custom Functions

When developing or debugging custom functions that will be called from a spreadsheet, remember that you cannot "jump" into the IDE. If you need to step through the script, you will need to work entirely within the IDE to observe execution of your code. You can use the techniques described above to debug custom functions.


1 Integrated Development Environment