قالب وردپرس درنا توس
Home / Tips and Tricks / How to automate Google sheets with macros

How to automate Google sheets with macros



With Google Sheets, you can automate repetitive tasks with macros, and you can then bind them to keyboard shortcuts to quickly execute them. They are working on using Google Apps Script to capture your actions for later use.

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

What are macros?

A macro or macro instruction is a specific sequence of actions that allow you to automate a series of steps to increase productivity. They work by recording your actions and saving them to a file that is bound to the worksheet where 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 for you. This means that you can create complex macros without knowing how to write code. The next time you run it, Sheets does everything you did when you recorded the macro. Essentially, read Google Plates about how to manipulate a document you want with a single command.

RELATED: How to upload your Google Apps with Script Editor

Macros are a powerful feature that can basically do everything Ark can do.

  • Create brand new worksheets.
  • Use any Google Sheets feature, toolbar, menu or feature.
  • The sky is the limit

    How to record a macro in Google Sheets

    Turn on 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.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> [19659006] This opens the recording menu at the bottom of the window, with two options for capturing your actions: </p><div><script async src=

    • Absolute references: The macro is performed only on the exact cells you If you italicize cell B1, the macro will only italicize B1 regardless of which cell you clicked on.
    • Relative reference s: The macro will do data on the selected cells, no matter where they are If you italicize B1 and C1, you can reuse the same macro to italicize cells D1 and E1 later.

    Choose whether you want an absolute or relative reference, then you can start clicking, formatting and teaching Archives which order [19659006]  Select either Absolute references or Relative references

    After taking all actions for this macro, click "Save."

     Click Save when you is finished recording

    Enter a name for your macro. Google also lets you create shortcuts for up to ten macros. If you want to bind a macro to a hotkey, enter a number from 0-9 in the specified space. When finished, click "Save".

     Enter a name for your macro and then click Save

    RELATED: How to insert a Google Sheets spreadsheet into Google Docs

    If you need to tweak your macro's name or shortcut, you can edit a macro by clicking Tools> Macros> Managing Macros.

     To create a shortcut, click Tools> Macros> Manage Macros "width =" 531 "height =" 314 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" sidespeed.lazyLoadImages. loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon "19659006"] </p>
<p>  In addition to each macro, enter a number from 0-9 to bind to a shortcut "width =" 495 "height =" 375 "src =" / pagespeed_static / 1.JiBnMqyl6S. gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> from the window that opens, adjust to the desired one, and then click" Update . "</p>

<p>  The next time you press the shortcut associated with the macro, it will run without having to open the macro menu from the toolbar. </p><div><script async src=

    How to run a Macr oi Google Sheets

    If your macro is an absolute reference, you can run the macro by pressing the hotkey or go to Tools> Macros> Your Macro and then click the appropriate option.

     Click Tools> Macros> YourMacroName to run your macro "width =" 650 " height = "332" src = "/ pagesp eed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  Otherwise, if your macro is a relative reference, select the cells in your spreadsheet where you want the macro to run and then press the corresponding shortcut or click it from Tools> Macros> Your Macro </p>
<p>  <img class=

    Since recorded macros are stored as functions in Google Apps Script, you must copy the function and then paste it into New Sheet Macro File.

    Open the Google sheet with the macro you want to copy, and then click Tools> Macros> Manage Macros.

     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.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  Next, click the" More "icon next to the macro you want to copy, and then click" Edit Script. "</p>
<p>  <img class=

    All macros are saved in the same file, so if you have any macros saved you may need to be sifted through them. The function's name is the same as you gave it when you created it. or the macro (s) you want to copy, then press Ctrl + C. Be sure to copy everything up to the closing semicolon

     Select and copy the macro function with Ctrl + C

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

    ] Create a new macro in your other worksheet, click Tools> Macros> Record Macro "width =" 479 "height =" 244 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onl oad = "pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null, pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  Immediately click" Save "without recording any actions to create a placeholder function in the sheet macro file for us. You will delete this a little later. 19659006] <img class= ] Click "Save" again.

     Don't worry about naming it, click Save

    Open Google Apps Script by clicking Tools> Script Editor, and then opening macro.gs file from the left pane Delete the existing function and then press Ctrl + V to paste into the macro from your second sheet.

     In the macro.gs file, paste the macro function from the first worksheet

    Press Ctrl + S to save the script, close the tab and return to your spreadsheet.

    Your spreadsheet reads the macros.gs file and looks for changes made. to a macro from another sheet.

    Then click Tools> Macros> Import.

     Click Tools> Macros> Import to import macros to this document "width =" 478 "height =" 282 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages. loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages .loadIfVisibleAndMaybeBeacon (this); "/> </p>
<p>  Finally, click" Add function "under the macro you want to add. </p>
<p>  <img class= [19659006] Sorry, you must bind The macro manually to a keyboard shortcut again, just follow the instructions mentioned above, and you will be ready to use this macro on multiple sheets.


    That's all there is to create and use macros in Google Sheets. for the routines you can create for your spreadsheets to limit amo (f, b, e, v, n, t, s) {if (f.fbq) return; n = f.fbq = function () {n .callMethod n .callMethod.apply (n, argument):?; (! f._fbq)! n.queue.push (argument)} about f._fbq = n; n.push = n; n.loaded = 0; n.version = & # 39; 2.0 & nbsp; = n.queue = []; t = b.createElement (s); t.async = 0; t.src = v; s = b.getElementsByTagName (e) [0]; s.parentNode.insertBefore (t, s)} (window, document, & # 39; script & # 39 ;, & # 39; ht tps: //connect.facebook.net/en_US/fbevents.js&#39;); FBQ (& # 39; init & # 39 ;, & # 39; 335401813750447 & # 39;); FBQ (& # 39; track & # 39 ;, & # 39; page views & # 39;);
    Source link