1

I'm on a windows 10 computer using a Chrome browser.

My problem is getting permission to run a Macro. For an individual user there are two pop ups that can appear when you record a macro and try to run it. One pop up has an 'Advance' link which warns you of the danger, but then lets you proceed with the 'unsafe' macro.

Sometimes, however, you get a pop up saying the approval process has been temporarily suspended and there is no 'Advanced' link to allow you to proceed. In essence, you cannot record and run a Macro.

I've been messing with this for days trying to figure out what causes one popup to appear as opposed to the other. I am completely mystified.

Last night someone shared a sheet with me. I made a copy, renamed it and moved the copy into my root drive which I share with no one. I recorded a simple macro, was told I needed permission, got the popup with the advanced link and was able to run the Macro.

This morning the person shared another sheet with me and I repeated the exact same steps of copying, renaming, moving the copy o my non-shared root folder and tried to record a Macro. No luck. I got the popup saying permissions were temporarily suspended with no advance link to let me move ahead and run the macro. How can this act so differently 12 hours later?

Can anyone offer me an explanation of what is happening? I'm now two days behind on delivering a promised project trying to figure this out. Thanks for any help anyone can provide.

Rubén
  • 34,714
  • 9
  • 70
  • 166
JHL
  • 11
  • 2
  • I wonder if it might be possible to authorize it from the script editor – Cooper Sep 24 '20 at 14:46
  • @Cooper The same will happen no matter if the script is run from the UI or the Script editor. – Rubén Sep 24 '20 at 17:17
  • I actually am trying to authorize it from the spreadsheets script editor. – JHL Sep 25 '20 at 16:12
  • @JHL You should use the exact text of the error. If you search that error, you would've found the answer. Anyway the linked duplicate should help you know the status of the issue. – TheMaster Sep 26 '20 at 06:14

1 Answers1

1

Instead of using a Google Sheets Macro use a G Suite editor add-on.

The cause of the "Advanced link" is not being shown is because there are "too many" users running the unverified script

From https://developers.google.com/apps-script/guides/client-verification

User cap

The number of users who can authorize an app via the unverified app flow is capped to limit possible abuse. See OAuth application user limits for details.

NOTE: You might not have to publish an add-on as nowadays the verification process can be done without adding the G Suite Marketplace SDK, but if you will need to use the same code on multiple spreadsheets you will only need to do the verification once whil having multiple copies of a spreadsheet with bounded code will require to do the verification for each copy.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Not sure why there are 'too many users. This is a spreadsheet that I made a copy of from another sheet. It didn't have any scripts or macros in it when it arrived (or I made a copy).. I created a macro for it, so as far as I can tell I am the only user with authorization to the spreadsheet or any macro it has in it. – JHL Sep 25 '20 at 16:16
  • @JHL Please edit the question to add those details. – Rubén Sep 25 '20 at 17:26