How to create a macro in Google Sheets and assign a button to it

A macro is a small program that performs actions pre-recorded by the user. Google Sheets supports the creation of such programs, which greatly simplifies the routine activities that you have to deal with every day. I’ll show you how you can record a macro and launch its execution using the graphic button located right in the table.

Macro task example

First, I propose to briefly dwell on what exactly the macro will do, which I took as an example for this article. Its task is to transfer the recorded data from one sheet of the table to another, thereby forming a database in automatic mode. This will simplify the process of filling in information and save the user from having to follow the lines. I will analyze all the components of the table to achieve the goal.

1. I have a small form to fill out with product numbers, their name, serial number, date.
2. In the following image, you see a simple formula for counting the number of each item so that you do not have to enter it every time. The link in the function leads to the sheet with the database.
3. The date is also entered automatically.
4. Only the names for the goods and their serial numbers will be filled in manually, after which all this is transferred to the database sheet.
5. On this sheet, the entire table is repeated in structure, but so far it is empty, since everything will be transferred automatically using a macro, which will be discussed later.

You may have a completely different table with your own tasks that you perform all the time, but want to optimize with a macro. Using the instructions below, you will figure out how to organize such a program if you have not previously encountered a similar task.

Recording a macro in Google Sheets

A macro in Google Sheets remembers the actions performed by the user, and then repeats them every time it is run again. This will allow us to do something just once, and then entrust the implementation to a small program. By the way, you can record an almost unlimited number of such macros.

1. First, I’ll populate the dynamic data in my table. Do the same for you if the need arises.
2. After that, open the menu “Extensions” , hover over the item “Macros” and click the button “Record macro” .
3. A recording window will appear, which means you can start performing routine actions. Keep in mind that the macro records absolutely all changes, so try not to make unnecessary clicks and edits to cells.
4. I’ve copied the entire string to transfer to the database and use the “Values ​​Only” paste special to avoid pasting functions that are not needed here.
5. Next, I remove the dynamic values ​​so that the form is ready for filling with other products.
6. Finally, on the sheet where the line was moved, I create a new line above to provide room for further copying.
7. It remains only to click on  “Save” , thereby completing the recording of the macro. If you are not sure that you did everything correctly, click on  “Cancel” , restart the recording and repeat the steps.
8. Give the macro any name in English. Shortcuts can be omitted, since we will later figure out how to run the script using the graphic button.
9. Check the macro through the same menu “Macros” by finding it by name.
10. If you are satisfied with the result, proceed to the next step. Otherwise, delete the current program and create a new one.

Assigning a Button to Run a Macro

As you have already seen above, to run a macro, you have to search for it through the menu or memorize the hot key. It is much easier to create a graphical button, one click on which will quickly launch the program. Fortunately, in Google Sheets, this is implemented by inserting a simple picture, which is done like this:

1. Expand the “Insert” menu and click on the item “Picture” .
2. Create any drawing or insert a geometric figure, choosing the appropriate size for it. You can even use any text.
3. Position the drawing on the sheet, click on the three dots to the right of it and select Assign Script .
4. As a script, specify the exact name of the macro that you created earlier.
5. Now click on the picture with LMB and make sure that the script was successfully executed (a notification will appear on the screen).

Unfortunately, I cannot tell you about all the possible macro variations, since everything depends solely on what actions the user wants to perform. I showed only an example of recording a program and considered an interesting function with its launch through a graphic button. You just have to understand what routine tasks you want to automate, and do it with the help of macros in Google Sheets.