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:

Add a second sheet:

Enter any values in the first 2 rows:

And return to the first sheet.

Now click on Extensions > Macros > Record macro:

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:

• Select the 3 cells (A1 to C1)
• Copy them (right click "Copy" or Ctrl + C)
• Display the second sheet
• Paste them on the second sheet in row 3 (right click "Paste" or Ctrl + V)
• Display the first sheet
• Erase the content of the 3 cells

Then click on Save:

And name it "example":

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):

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:

And add a shape of your choice:

Customize the shape and add text if you wish.

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

And enter the name of the script chosen during recording:

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:

Click on OK and then on your account:

Then click on Advanced at the bottom left:

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

Finally, click on Allow:

Running a Macro

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

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):

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).