قالب وردپرس درنا توس
Home / Tips and Tricks / Automating Google Sheets with Macros

Automating Google Sheets with Macros



Google Sheets lets you automate repetitive tasks with macros. You can then bind them to keyboard shortcuts for quick execution. You're using Google Apps Script to track your actions for later use.

RELATED: Learn how to use Excel macros to automate boring tasks

What are macros?

A macro or macro command is a specific sequence of actions that you can use to automate a series of steps to increase productivity. Record your actions and save them in a file attached to the spreadsheet. in which they were recorded.

When you record a macro in Google Sheets, it automatically creates an Apps script with all the code to replicate your actions. This means that you can create complex macros without knowing how code is written. The next time you run it, Sheets does everything you did when you took the macro. In essence, Google Sheets teaches you how to edit a document to your liking with a single command.

RELATED: How to Load Google Apps with the Script Editor

Macros are a powerful feature that can basically do everything that sheets can do. Here are some examples of functionality:

  • Apply formatting and styles.
  • Create completely new spreadsheets.
  • Use any Google Sheets feature, toolbar, menu, or feature.

The sky is the limit

Recording a macro in Google Sheets

Start a Google Sheet and click Tools> Macros> Record Macro.

 Click Tools> Macros> Record Macro "width =" 480 "height =" 291 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.favisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.la_LoadImages.loadIfVisierra ". 19659006] The recording menu will appear at the bottom of the window with two options for recording your actions: </p>
<ul>
<li>  <strong> Absolute References: </strong> The macro performs tasks only in the exact cells. that you record In cell B1, the macro is always italicized, regardless of which cell you clicked on. </li>
<li>  <strong> Relative Reference s: </strong> The macro performs tasks on the selected cells, regardless of where If you are italicizing B1 and C1, you can reuse the same macro to later italicize cells D1 and E1. </li>
</ul>
<p>  Select whether you want an absolute or relative reference and click You can use sheets to set the order You want to replicate these actions. </p>
<p>  <img class=

After you have captured all the actions for this macro, click Save.

 Click Save after shooting.

Enter a name for your macro. With Google, you can also create shortcuts for up to ten macros. If you want to bind a macro to a keyboard shortcut, enter a number between 0 and 9 in the appropriate field. When you're done, click "Save."

 Enter a name for your macro and click ]

RELATED: How to Insert a Google Spreadsheet in Google Docs

If If you need to change the name or shortcut of your macro, you can edit a macro by clicking Tools> Macros> Manage Macros.

 To create a keyboard shortcut, click Tools> Macros> Manage Macros. "width =" 531 "height =" 314 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lalaLoadAdr. this); "/> </p><div><script async src=

In the window that opens, make the desired settings and click" Refresh. "

 Next to each macro, enter a number between 0 and 9 to link .

The next time you press the keyboard shortcut associated with the macro, it will run without having to open the macro menu from the toolbar.

Running a Macr in Google Sheets

If If your macro is an absolute reference, you can run the macro by pressing the keyboard shortcut or by going to Tools> Macros> Your Macro and clicking the appropriate option.

 Click Tools> Macros> YourMacroName Running your macro "width =" 650 "height =" 332 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed. lazy LoadImages.loadIfVisibleAndMaybeBeacon (this); /> </p>
<p>  If your macro is a relative reference, otherwise mark the cells in your spreadsheet where you want the macro to run. Press the appropriate key combination, or click Tools> Macros> Your Macro on it. </p><div><script async src=

 If you created a relatively referenced macro, select the cells and click Tools> Macros> YourMacroName to run the macro "width =" "650" height = "209" src = "/pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); " onerror = "this.onerror.loadajoradourage"; ; "/> </p>
<p><strong>  RELATED: </strong> <strong><em>  How to Protect Cells from Editing in Google Sheets </em> </strong> </p>
<h2>  Importing Macros </h2>
<p>  As noted above, the macro is bound to a macro when it is captured Spreadsheet on which you recorded it What if you want to import a macro from another spreadsheet? This is not an easy and simple task, you can do it with this little workaround. </p>
<p>  Because macros are recorded as functions in Google Apps Script, you need to copy the function to import a macro and then paste it into the macro sheet of the new sheet. </p>
<p>  Open the Google Sheet with the macro you want to copy and click Tools> Macros> Manage Macros. </p>
<p>  <img class=

Next, click the More icon next to the macro you want to copy, and then click Edit Script.

 Click the

icon All macros are stored in the same file. So if you have some macros saved, you may need to go through them. The name of the function is the same as you specified when you created it.

Select the macros you want to copy, and then press Ctrl + C. Be sure to copy everything up to and including the closing semicolon.

 Select and copy the macro function with Ctrl + C

Now open the other spreadsheet into which you want to import the macro and click Tools> Macros> Record Macro.

] Create a new macro in your other table. Click Tools> Macros> Record Macro "width =" 479 "height =" 244 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.errerr = "; null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" /> </p>
<p>  Immediately click "Save" without recording an action to place a wildcard function in the sheet's macro file for us to create. You will delete them later. 19659006] <img class=

Click "Save" again.

 You do not have to worry about the name. Click Tools> Script Editor and open the file macros.gs in the left pane. Delete the existing function and press Ctrl + V to paste the macro from your other worksheet.

 In the macros.gs file, paste the macro function from the first table

Press Ctrl + S to save the script, close the tab, and return to your SP readsheet ,

Your spreadsheet reads the macros.gs file and looks for any changes that have been made to it. When a new feature is detected, you can use the import feature to add a macro from another sheet.

Then click Tools> Macros> Import.

 Click Tools> Macros> Import to import the macros This document "width =" 478 "height =" 282 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" pagespeed. lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLor .loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  Finally, click" Add Function "below the macro you want to add. </p><div><script async src=

 Finally, click next to the macro you want to add [19659006] Unfortunately, you must manually bind the macro to a keyboard shortcut again the aforementioned statement, and you can use this macro across multiple sheets. Using Macros in Google Sheets: There's no limit to the routines you can create for your spreadsheet to limit the amo. Without time, you spend those repetitive ones Exercises.


Source link