Enabling Macros in Google Sheets

Learn how macros run in Google Sheets through Apps Script, how to record one, and how to review and approve the authorization prompt before any code runs.

9 min read0 views
— · 0 votes
Contents
  1. What a macro means in Google Sheets
  2. Recording a macro for a repeated task
  3. Steps to enable and run a macro
  4. Reading the authorization prompt
  5. Macros inside downloaded estimate templates
  6. When a macro will not run
  7. Keeping macro use safe over time
  8. Questions about enabling Sheets macros
  9. How do I enable macros in Google Sheets?
  10. Can Google Sheets open a macro-enabled spreadsheet file?
  11. Is it safe to approve the authorization prompt for a downloaded template?
  12. What is the difference between a macro and an add-on?
  13. Why does my recorded macro change the wrong cells?
  14. Can I stop a Google Sheets macro from running?

Google Sheets does not run desktop-style macro files. A macro in Sheets is an Apps Script project attached to the spreadsheet, and enabling it means opening or creating that script and approving the authorization prompt. The spreadsheet owner decides who can run the script, and you should read the code before you approve anything.

  • Google Sheets handles macros through Apps Script, not through downloaded macro-enabled spreadsheet files.
  • You can record a macro to capture repeated cell steps and let Sheets write the script for you.
  • The authorization prompt appears the first time a script needs your account, and you approve scopes, not a whole file.
  • Read the script or the add-on permissions before approving, especially in a template you downloaded.
  • Edit access to the script editor is separate from edit access to the cells.

What a macro means in Google Sheets

A macro in Google Sheets is a short program written in Apps Script, the scripting language built into Google Workspace. The spreadsheet stores the script alongside its cells, so the macro travels with the file when you copy it. A downloaded file saved in a desktop macro format has no macro that Sheets can run on its own.

The script editor is the window where that code sits, and it is reachable from the Extensions menu of any spreadsheet you can edit. Code written there can read cells, change values, send email, or call outside services, depending on the permissions it asks for. That reach is why a macro from an unfamiliar template deserves a look before it runs.

Google Sheets add-ons are a different path to the same idea, because an add-on is a packaged script that appears in a side panel. Add-ons install through the marketplace and show their permissions before you connect them. A template that asks you to paste code or install an unlisted add-on is asking you to trust its author.

Recording a macro for a repeated task

Macro recording in Google Sheets captures the cell steps you perform and writes them into an Apps Script function. Recording suits a short, fixed sequence such as formatting a block of estimate lines or copying a column of figures into a summary area. The recorder does not capture every kind of action, so check the result in the script editor.

Recorded code often refers to cells by their position, and a recorded macro can act on the wrong rows once the layout shifts. Open the script after recording and read which ranges it touches. Renaming variables or replacing fixed ranges with relative ones takes some scripting knowledge and makes the macro safer to reuse.

A recorded macro is a starting point rather than a finished tool. Save the spreadsheet version you tested, then run the macro on a copy before you rely on it. If the code touches data you cannot restore, keep a backup of the sheet.

Steps to enable and run a macro

Enabling a macro in Google Sheets means granting the script permission to act on your behalf, and the steps below cover a script already in the file. The first run always stops at the authorization prompt, and nothing changes until you approve. A spreadsheet shared with you may hide the script editor if the owner restricted it.

Choose the account you want the macro to use when the prompt asks, because the script acts with that account's access. Read each scope on the consent screen and match it to what the code claims to do. A script that reads a range should not need permission to send mail on your behalf.

Run the macro from the Extensions menu after approval and watch the first execution on a copy of the data. Google Workspace administrators can block script authorization for an organization, so a prompt that never appears may be a policy decision rather than a fault.

  • Open the spreadsheet and confirm you have edit access to it.
  • Open Extensions, then Apps Script, to see the project attached to the file.
  • Read the code or the recorded function before running anything.
  • Start the macro from the Extensions menu and read the authorization prompt.
  • Pick the account and review each permission scope on the consent screen.
  • Approve the scopes, then run the macro on a copy and check the result.

Reading the authorization prompt

The authorization prompt lists the permissions a script requests, and each line is a scope that grants a defined kind of access. Approval covers the scopes shown, not a vague trust in the file. A macro that only formats cells should request a narrow scope tied to the current spreadsheet.

A prompt asking for broad access to all your spreadsheets, your mail, or your drive deserves a pause. Compare the requested scopes with the job the macro claims to do, and decline when they do not match. The script editor shows which services the code calls, and that list is the honest statement of what the macro can reach.

Permission granted to a script stays with the file, so a later edit to the code can use the same access. Revisit the script after someone else changes it, and remove access you no longer want. A spreadsheet owner can revoke a script by deleting the project or removing the user's authorization.

Macros inside downloaded estimate templates

A downloaded comparison template can carry a script that fills columns or totals line items, and that convenience is exactly where risk enters. The file may come from an unknown author, and the code may reach beyond the sheet it arrived in. Treat any script in a downloaded template as untrusted until you have read it.

Open the script editor on a copy of the template and read the functions before you run them. Look for code that sends data to an outside address, deletes rows, or asks for permissions unrelated to estimate work. If the code is long or obfuscated, ask the author for a plain version or rebuild the calculation yourself.

Line items you plan to discuss with a local service provider often hold prices, addresses, and notes. A script with broad access can copy that material elsewhere. Keep sensitive figures out of a template you have not vetted, and share only the columns a provider needs.

When a macro will not run

A macro that fails to run usually points to access, authorization, or a changed layout rather than a broken file. Check that you still have edit access to the spreadsheet, because view-only access hides the script editor and blocks execution. An administrator policy can also stop authorization before the prompt appears.

A macro that ran before and now errors may be reading rows that moved after you added estimate lines. Open the script and confirm the ranges still match the sheet. Error messages name the failing line, and that line usually shows which reference drifted.

Revoking access and approving again can clear a stale permission, and it also forces you to re-read the scopes. If the script was written by someone else, ask for an updated version instead of editing code you do not understand. A macro that touches billing or private data belongs with a trained specialist.

Keeping macro use safe over time

Safe macro use in Google Sheets rests on knowing who wrote the code and what it can reach. Keep a plain copy of the estimate data that no script can alter, and export it before you test a new macro. Version history in the spreadsheet lets you restore cells, but it cannot undo mail sent by a script.

Limit edit access to the spreadsheet to people who need it, since anyone with edit rights can change the script. Review the script editor when a file passes between hands, and remove projects you no longer use. A template that only needs to display figures can stay script-free.

Write down the steps a macro performs so another person can check it without reading code. That record helps when you hand the file to a service provider or a colleague. A short written description of each function is enough to spot a mismatch later.

Questions about enabling Sheets macros

How do I enable macros in Google Sheets?

You enable a macro in Google Sheets by opening the Apps Script project attached to the spreadsheet and approving the authorization prompt when the script first runs. Start the macro from the Extensions menu, choose the account, and review each permission scope before you approve. Nothing runs until you consent.

Can Google Sheets open a macro-enabled spreadsheet file?

Google Sheets cannot run the macro code stored in a desktop macro-enabled spreadsheet file. Importing the file brings the cells across, and the macro itself does not carry over. You would need to recreate the steps as an Apps Script project or record a new macro in Sheets.

Is it safe to approve the authorization prompt for a downloaded template?

Approval is safe only when the requested scopes match what the code does and you trust the author. Read the script in the editor and compare each permission with the task it claims to perform. Decline broad access to mail, drive, or all spreadsheets when the macro only formats estimate lines.

What is the difference between a macro and an add-on?

A macro is a script stored inside one spreadsheet and run from its Extensions menu. An add-on is a packaged script installed through the marketplace and shown in a side panel across files. Both run in Apps Script and both ask for permissions before they act.

Why does my recorded macro change the wrong cells?

A recorded macro often refers to fixed cell positions, so it changes the wrong cells once rows or columns move. Open the script editor and read which ranges the function touches. Adjusting those references, or rewriting them to be relative, keeps the macro aligned with your sheet.

Can I stop a Google Sheets macro from running?

You can stop a macro by removing the script project or revoking authorization, and an administrator can block script execution for an organization. Deleting the Apps Script project detaches the code from the spreadsheet. The spreadsheet owner controls who may edit and run the script.

Read next

Latest articles