Apps Script Course: First Macro

Before starting to write a macro, let's focus on Google Sheets' macro recorder to better understand its utility and limitations.

This feature allows you to record your actions and automatically create an equivalent macro.


Recording a Macro

In a new Google Sheets document, enter any values in the first 3 cells of row 1:

google sheets cells first macro

Add a second sheet:

google sheets add sheet first macro

Enter any values in the first 2 rows:

google sheets cells c2 first macro

And return to the first sheet.

Now click on Extensions > Macros > Record macro:

google sheets record macro png first

Let's pretend we want to add the data from the 3 cells of the first sheet to the end of the second sheet.

Perform the following actions:

Then click on Save:

google sheets macro recorder first

And name it "example":

google sheets macro recorder name first

You can then find your macro in the macro.gs file that has been added to the editor (refresh the page if macro.gs is not displayed):

google sheets first macro png

Don't worry, you don't need to understand this code (which is not very readable).

Assigning a Macro

There are different methods to trigger the execution of a script (and we will see others later in this course). For this example, click on Insert > Drawing:

google sheets drawing png first macro

And add a shape of your choice:

google sheets drawing button first macro

Customize the shape and add text if you wish.

You can also insert an image (of a button, for example) directly onto the sheet and use this image instead of using the Drawing tool.

After inserting the shape onto the sheet, select it, click on the three dots on the right, then on Assign script:

google sheets button assign script first macro

And enter the name of the script chosen during recording:

google sheets assign script first macro

Authorization

Try launching the macro by clicking on the button.

A window will then appear, as the first time you run a macro in a new workbook, you must authorize it:

google sheets authorization macro first

Click on OK and then on your account:

login first macro

Then click on Advanced at the bottom left:

login advanced settings first macro

And on Go to Untitled project (unsafe) a bit lower:

login advanced settings 2 first macro

Finally, click on Allow:

login advanced settings 3 first macro

Running a Macro

For this test, enter 3 different values in cells A1 to C1 and launch the macro by clicking on the button:

google sheets button first macro

The script will then execute, repeat all the recorded actions, and finish on the first sheet.

If you go back to the second sheet, you will see that the script has performed exactly the same actions and pasted the 3 values in row 3 as before (over the previous data):

google sheets cells a3 first macro

If you thought the macro was going to add the data in sequence, that is not the case. The recorder can only be used for simple actions that are repeatable identically (such as deleting the content of a range of cells).

Moreover, the recorder adds all sorts of unnecessary actions, such as activating sheets (while the macro could very well insert the data on the second sheet without changing sheets, and more quickly).

Later in this course, we will see how to insert data on another sheet in sequence, and you will see that the code is much simpler than that generated by the recorder.

In the meantime, it's good to have tested the macro recorder at least once... And maybe one day it will help you when you don't know how to apply, for example, red borders to cells (you can then record a macro and search for the useful method in the generated macro).