Linux Goodies

In Pursuit Of The Perfect O/S


HOME

ASTRO LINUX
GIFTS
RPN
CALC
MENU

How to Make a Google Sheets Macro

Amazon Computers



What is a Macro?

You may be wondering what the hullabaloo is about Google Sheets and Macros. In fact, you may be wondering what a macro is. So lets start with that, and discuss macros in a general sense.

Macros are found in many types of computer applications. The utility of a macro is to allow the user of the application to record a series of steps that they find they use often. I'm writing this web page using Linux VIM for example. And if I decide I want to change all of my header lines to a different size (h2 html tag instead of h3 perhaps), and also possibly modify the text of each header, I usually record a VIM macro that I can invoke which will find and adjust the next header in my HTML file, and leave me at that spot in the document in case I want to further alter it. Then when finished with a header, I can run the macro again to automatically find and alter the size of the next header.

While I don't like the term Power User, I suppose you could say that Power Users generally learn to use macros if available in whatever applications they often use. I prefer Experienced Users.

So if you want to move up a notch in the experience chain for Google Sheets users, you may want to learn macros. With that knowledge, you can automate most any sequence of steps you find yourself doing repeatedly and thus make your future work easier and likely less error prone. And you can do this without having to learn how to program scripts directly.

How to Create a Google Sheets Macro

Below is a screen shot of a portion of a Google Sheets workbook that has two columns of data.

Simple Google Sheet Workbook

Now for a contrived example, assume these columns of data are side lengths of a number of right triangles, and I want to compute the hypotenuse of each triangle (square root of sum of squares). But, I expect to have files like this often, and need to do the same operation each time. I can create a macro to accomplish this. I'll construct a macro that creates columns of the squares of the values before computing the hypotenuse. It's an unnecessary step mathematically, but instructional as to creating macros.

The first thing I have to do is inform Google Sheets that I wish to record the following sequence of steps as a macro. I do this by clicking on Tools, then Macros, then Record macro. The following image illustrates this step.

Select Google Sheets Create Macro

When I do these clicks, a pop up appears as shown in the image below that lets me know I'm building a macro. It remains there until I'm finished, and then allows me to save (or cancel) the macro.

Create Google Sheets Macro

On the macro window near the bottom of the illustration, you can see that I have the option to choose either absolute references, or relative references. If the data on which I'm going to run the macro is always going to be in the same cells, I should pick absolute references. But if I am going to build a macro that will be used on data that might be most anyplace in a workbook, I should pick relative references. For this contrived instance, I chose absolute references.



In my example, I want to insert a column between columns A and B to allow a column for the squares of column A values. When recording a macro, I can use most any spreadsheet function or operation that I would normally use. So as the following image illustrates, I right-click on column B and select insert 1 left. This inserts a column that I'll use to hold the squares of column A values.

Insert Column in Spreadsheet

With the new column inserted (and the step recorded), I want to add labels to the inserted column and two columns past column C. I also want to compute the squares of the values in column A. I used the ARRAYFORMULA function for this step, as you can see in the following image. Again, as you see the macro indicator at the bottom of the image, you know that the entry of the equation is being recorded in my macro.

Apply ARRAYFORMULA to Square Column Values

In the following image you can see that I also added an array formula to compute the squares of the second triangle side values, now residing in column C, with the squares in column D. In the image you see that I'm inserting the final formula that will sum the squares of the sides (sum columns B and D) and take the square root. This will complete my macro. After this equation executes, I click SAVE on the macro window at the bottom of the image. It gives me the opportunity to provide a name for the macro. I used test.

Apply ARRAYFORMULA to Compute Hypotenuse

Now to test the macro, I cleared the workbook and started again with just columns A and B, my triangle sides. To run the macro, I select Tools, then Macros, then test, the name of the macro that shows in the bottom of the Macros sub-window. Clicking on test will do all of the steps recorded when the macro was created. Incidentally, the first time you run your new macro, you'll probably be presented with a pop up window that asks you to grant permissions to run the macro. This will only happen once in that workbook. All subsequent uses of the macro will not ask permission again.

Test New Google Sheets Macro

The following image shows the results of running the macro: new column inserted, labels added to the columns, the squared value columns created, and the hypotenuse column calculated.

Test New Google Sheets Macro

You Can Edit Your Macro Scripts

In the following image you can see that I'm about the select the Tools then Script editor. This will allow me to inspect the macro that Google Sheets created for me. This is the same editor you could use to write your own scripts of new functions. If you learn a little about the scripting, you can use the script editor to make enhancements to your macros if you wish.

Edit Google Sheets Macro

So, what kind of enhancements might you want to include in a macro? Well, in the case of this simple macro, one obvious limitation is that it's hard-wired to operate on 20 rows. If I loaded data with a different number of rows, this macro would still expect to operate on exactly 20. I could enhance it so that it could detect the size of a highlighted region, and figure out how many rows are being operated on, and then do the computations in a loop instead of using an ARRAYFORMULA, depending upon the number of detected rows.

Some Differences between Scripts and Macros

If you aren't into scripting in Google Sheets, it's pretty obvious what advantage macros offer you. They let you automate processes without having to know scripting. And even if you want to do some enhancements to your macros, you only need to learn enough scripting to add those enhancements.

But if you are already into scripting in Google Sheets, you may wonder what macros could do for you. It turns out they can do something for you. You may have discovered that from within a function script that you've created that you cannot affect any part of the current workbook other than the cell the scripted function is entered into, and the below and to the right adjacent cells should you return a matrix. But you cannot just change the value of some other cell somewhere else in the workbook.

Turns out, with a macro, you can. A macro can have access to anywhere in your current sheet. That's likely why you have to grant permission to run the macro, but not to run your scripted functions.

A difference however that falls to the advantage of function scripts is that they can be put into libraries of your own. Then, if you are in a completely different workbook you can go to Tools then Script editor and select Resources, where you can make reference to your libraries. You still have to create functions within your script to call those library functions, but they are just simple reference scripts.

For example, I have a library full of matrix routines. I call it matlib. In the spreadsheet where I created the library, I can get a reference number for that library from within the Script editor. I enter that reference number into the Script editor Resources of my new spreadsheet, to get access. Then, for example, to call my mbias function that will let me add a bias to every element of a range, I add the calling function as follows:

function mbias(mat,v){
  var ret = matlib.mbias(mat,v);
return ret;

This reference function is necessary because the current workbook can only run what it sees in its own Script editor. It can't see directly the functions in a library. The reference functions I put in the workbook's Script editor, however, can call my library functions.

Any way, the point being that scripted functions can be placed into libraries and referenced from other workbooks.

Macros -- cannot. That is, you cannot place macros in a library and thus get to them from a different workbook.

How to have Macros in Each New Sheet

As designed, macros live in the Script editor of the workbook in which they were created. They can be invoked in any sheet of that workbook, but cannot be placed in libraries and thus referenced in other workbooks.

But!!

You can go to a workbook whose Script editor has said macro, copy it to the clipboard (highlight and CNTL-c), then go to the Script editor of the desired workbook, and paste the macro there (CNTL-v).

If you do that, then when you go to the Tools/Macro menu, you'll see the following:

Import Macro

You see that in the Record macro window, there is now an Import option. The workbook has detected that there are more scripts in the editor than just the test script we created earlier. Clicking on Import in this case gives the following:

Import Macro

In this case, you see that I've pasted a whatif script from another workbook into this workbook's Script editor, and now I can select this as an additional macro to be available in this workbook. I will be asked to give permission on the first attempt to use it, as it's never been used in this sheet.

A little less convenient than the library feature available for regular scripts, but not a big deal really. Just know that you can copy a macro from one of your workbooks to another by just copying it from one workbook Script editor to the other.

There is also another way to do this. When I am creating either multiple macros that I use in working with some kind of data, or a complex macro that gets a bit big, I do it in a test workbook. All that's in the test workbook is some test data and the macros in the Script editor. If I know I'm going to be working in a workbook that will need these macros, I just enter the workbook where I designed the scripts, and save it as a copy, which I rename to be the new sheet I want. I replace my test data with my real data, and all of my macros are already there.

A final, and perhaps best way to have some of your favorite macros always available is to do the above cut and paste of the macros into the script window for a new workbook. Then name that workbook some generic name like maybe New Project. Then while the workbook is open, bookmark the page. Now whenever you start a new project, instead of using the default Blank workbook from Google Sheets, you click on your bookmarked workbook. It will come up in the browser, complete with your favorate macros. Just rename the workbook to the desired project name and go to work. The macros will be there.

A Case in Point: What If Macro

To illustrate the usefulness of macros and how worthwhile it is to learn how to move them around, I present the What If macro.

A little history about the macro. I was proof-reading a paper my son wrote for his master's work when I ran across a spreadsheet feature I was totally unaware of. He did a Sensitivity Analysis of some data, and sure enough in Excel with which he did his analysis there was a utility just for that. I call it a utility in that it was a menu selection, not just a function that you type into a cell.

It is a very clever utility. It allows you to create either 1D (single column) or 2D (table) analysis tables where the utility dutifully replaces one or two cells of some analysis area in your sheet with a range of other exploratory values. It allows you to monitor any result cell for how it is affected by the varying exploratory values.

It's neat in that the utility simply substitutes the one or two cells in some user created table with the exploratory values, lets the spreadsheet do the analysis set up previously by the user, and then records in a column (1D) or table (2D) the corresponding result values. Clever in that it doesn't care what the analysis equations are or what they do, it just replaces a cell or two and lets the spreadsheet do whatever was originally created to do, then obtains the result of interest.

A What If Example

The following spreadsheet images gives a brain simple example.

What If Analysis

Imagine we have the sheet shown above. It has just a few parameters for a loan payment calculation. Cells B1 through B3 contain respectively the yearly interest rate, the loan value, and the number of payback periods. Cell B5 has the resulting payment, obtained using a built-in spreadsheet function.

But suppose we'd like to explore the effect on payment of several different interest rates. We could just manually keep changing the value in cell B1 and looking at the result. But if we had the clever Sensitivity Analysis utility, or what others call a What If utility, we could easily build as many values as we want, and have them recorded in the spreadsheet.

What If Analysis

You see the start of that analysis in the above image. Below the initial cells that contain the loan parameters and payment result is a column of interest rates that we might like to examine. And if we were in a spreadsheet that had such an analysis utility, it would be a piece of cake.

But if we are in love with Google Sheets because of it's low price (free) and many conveniences, we are in a pickle. Google Sheets doesn't have such a utility. Not built in anyway.

Google Sheets Add-ons To The Rescue

Ahah!

If you check out the Add-ons and look for what if you'll find that there is a function that can come to the rescue. I've tried it, and it works just like the Excel function. Just like it other than it is a bit slow. On single column analysis it's not noticeable, but on a table of maybe a hundred cells, it's a bit slow. But it works and is freely available.

However, I saw this as a perhaps a double opportunity. For one thing, I thought such a utility would be nice to have. For another, I'd found that recently (as of this writing), Google Sheets had added macros, and I thought maybe this was a good opportunity to learn how to use them. I'd already realized that I wouldn't be able to do this utility with a regular script function because of the lack of permission to alter cells of an existing sheet. So I tried the macro solution.

I could see that setting up a macro to step by step to place the exploratory values into the interest cell (B1) and placing the payment result back next to the respective interest value was doable, but tedious. And the resulting macro would only work for a specific size table and specific locations of the user table elements.

So I did a few of the exploratory values just to see the nature of the resulting script. Then, because I had some experience with scripting functions, I hunted down a few Google Sheets API functions I needed and turned the macro into a generic macro that would work much like the Add-ons utility. I got it to work, and surprisingly, it ran about 4 times faster than the Add-ons version. So I use it.

The next few images show how the macro works. First, as the previous image shows, I created two columns, one for the interest values to be explored, and one for the Payments that would result. I also placed in the first cell of my interest column the cell address for where the interest parameter is stored, B1. The address reference is stored just as a text string.

Below that I put a reference to the result cell that I wish to monitor, payment in this case. So I put =B5 in cell C10. Then below that I put a column of exploratory interest values I was interested in.

To run the macro, I highlighted the indicated region to include the cell reference of the parameter I want to change, down through the last interest rate, and including also the column for the results. Then I just, as indicated, click on Tools, then Macros, then my whatif macro.

What If Analysis 1D

Then I sit back and watch as the magic happens. Each exploratory value is placed in the Interest cell at B1, and each result is entered next to the respective exploratory interest rate, resulting the the sheet shown below.

What If Analysis 1D

A 2-dimensional What If Table Example

The macro checks what is highlighted before it runs, and if it sees more than 2 columns highlighted, it assumes you are indicating a 2D table of values, with the 1st column being exploratory values for one cell, and the 1st row of the data area (one below the cell parameter addresses) being the exploratory values for the second cell. Such a setup would look like the image below.

What If Analysis 2D

As the example illustrates, two cell indicators must be provided. Next to the B1 reference is a B3 reference, indicating that both interest rate and number of periods are to be explored. Down the first column is the list of interest rates which will sequentially replace the interest value, and along the first row of the data portion of the table are number of period values which will replace the Nperiods portion of the initial user sheet area. Again, just below the first cell indicator is the reference to the result cell, payment (B5) in this case.

To run the macro, I highlight from the first cell indicator to the bottom right to include all interest values and all number of periods values. The following image illustrates.

What If Analysis 2D

With the area highlighted, I'm ready to click on the macro again. Clicking on it causes all cells of the table to be filled with payments corresponding to the combination of interest and periods of the respective row/col. The following image shows the result.

What If Analysis 2D

So in just a few seconds time I have a table of payment values exploring several combinations of interest and number of payment periods. Cool huh?

This macro is likely a bit more that you will make at first, mainly because in order to generalize it so that it would work with different sized What If tables, some coding had to be done after the skeletal macro was created. But given what the macro accomplishes, it shows the kind of power that can be had with macros.

So in this utility, you can have your choice. You can use the Add-ons what if function, which operates very similarly (except you must enter the exploratory cell addresses into a prompt each time), or you can download the script offered here and use it as a macro. The macro requires the exploratory cell address(s) to just be part of the range on which it's operating, no prompts. With the macro version, you have the code in your hands, so to speak. You can learn from it, and very likely improve it.

I offer no warranty that the macro will work for you or not even mess up your spreadsheet. I accept no responsibility in either case.

But if you want to experiment with it just to get an idea how you can generalize your own macros, and possibly get some valuable use out of this one, here it is: What If macro. Just copy it and paste it into the Script editor of your workbook (I'd advise using test data to start with), use the Tools / Macros / Import sequence to make it available, and give it a shot.