You now have vast knowledge about functions in Excel, but you may have already noticed that not everything can be solved by them. Sometimes we will need to think outside the box. Sometimes we want to optimize a process with something already prepared. At these times, we will use something called a MACRO, which exists both in Excel and in Google Sheets.A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording mouse clicks and keystrokes. Remember that it is always good to check whether the function already exists; we do not need to reinvent the wheel!Choosing the MacroUnfortunately, a MACRO is not a universal language. The Google Sheets Macro is different from the Excel Macro, which is different from the LibreOffice Macro, the three largest spreadsheet applications nowadays. Because of this, the course will have to choose one Macro for us to explore in depth.The Macro chosen for this module was Google Sheets. The reason is that Google Sheets uses a universal language and works on practically all computers, mainly because it is opened through Google Chrome, a browser.Getting Started with MacrosSuppose that every month you create a report for your accounting manager. You want to format the names of customers with overdue accounts in red and also apply bold formatting. You can create and run a macro that quickly applies these formatting changes to the cells you select.You will first need to teach Google Sheets to perform your task. Before starting, click the "record Macro" button, which can be found as shown below:After clicking record, every action you take will be recorded inside the Macro. That is, if you delete a cell, add information, or run a function, everything will be recorded. Watch the video below about recording:In this case, you will color the cells you want and then make them bold. Right after recording, you can save your Macro.Now every time you want to run exactly the same commands you performed while recording, you can do the following: go to the Macros tab and look for the name of your saved Macro. Click it, and it will immediately start running everything it recorded.How Is the Macro Saved?When you save a Macro, it will become part of your spreadsheet. But where is it saved? In the tools tab, we can look for it by clicking "Script Editor". A Macro is nothing more than code or a script.When you click, you will be taken to Google's script editor. In the case of my Macro, I see the following screen:On this screen, I can see that a function called "Minhanovamacro" was created, which creates the variable "spreadsheet" by calling the "SpreadsheetApp" class. This function activates the cells of the spreadsheets I worked on during the recording and changes the value of each one according to the recording. That's it, a lot of information? Well, the Google Sheets Macro is saved in a text document using the JavaScript programming language. So, before we go deeper into Macros, we will need to learn JavaScript, the programming language that dominates the internet.ExercisesSee the spreadsheet at this link here. Record the following Macros:1. A Macro that deletes all user data and leaves only the questions.2. A Macro that changes the questions according to the city. Example: Rio de Janeiro will ask "Do you use perfume?", while São Paulo will ask "Do you drink socially?".3. A Macro that colors the user's answers red.
Войдите и присоединяйтесь к беседе
— Комментарии 0
, Реакции 1
Прокомментируйте первым