Google denied access to this sheet

Problem

A query or a scheduled refresh fails with this message:

Google denied access to this sheet. The sheet may have been unshared, moved, or its permissions changed. Please verify the sheet's sharing settings.

You can also see one of these messages for the same problem:

Access to the destination spreadsheet was denied. Please check that TMR has edit permission and try again.

No one in your team has access to this Google Sheet. Please make sure the sheet is shared with your team members and try again. If sharing is correct, re-authorize Advanced Scheduler from My Account → Team Preferences.

You see the message in the add-on when you run the query. When a scheduled refresh fails, your team gets one email the next morning. The email lists each query that failed, with its schedule, its spreadsheet and the reason. The subject is, for example, "1 query failed in a schedule yesterday".

The query then stops. In Data Queries, it shows Inactive. Put the pointer on Inactive to see what to do.

Why this happens

This error is about one spreadsheet, not about your Google account. Google accepted the sign-in, but it refused access to the spreadsheet. Usual causes:

• Someone removed the Google account from the people who can open the spreadsheet.
• The access changed to Viewer or Commenter. Two Minute Reports writes data to the spreadsheet, so it needs Editor access.
• Someone moved the spreadsheet to a shared drive or a folder that the account cannot open.
• The owner of the spreadsheet changed, and the new owner changed the sharing.
• A Google Workspace administrator added a rule that stops sharing outside your organization.

Which Google account needs access

A refresh does not use the Google account of the person who looks at the spreadsheet. It uses the Google account of the person who created the query, with the access that this person gave to Advanced Scheduler.

If that account cannot open the spreadsheet, Two Minute Reports tries up to two other members of your team who gave access to Advanced Scheduler. It tries first the members who made queries in the same spreadsheet. If none of them can open it, you see "No one in your team has access to this Google Sheet".

How to fix it

1. Open the spreadsheet in Google Sheets, and select Share.
2. Make sure that the Google account of the person who created the query is in the list, with Editor access. If it is not, add the email address and select Editor.
3. If the spreadsheet is in a shared drive, the settings of the drive can stop sharing with people outside it. Then add the account to the shared drive as Contributor or higher.
4. Give Editor access also to the other team members who gave access to Advanced Scheduler. Then a refresh can continue if the access of one account changes again.

You do not have to start the query again. Two Minute Reports continues to examine the spreadsheet. When your team can open it again, the query is not inactive any more, and the next scheduled refresh runs it. To get the data immediately, run the query in the add-on.

If the error continues

• Make sure that you share the correct spreadsheet. A schedule can write to more than one spreadsheet. The email names the spreadsheet that failed.
• Ask your Google Workspace administrator if a rule stops sharing outside your organization. The administrator must let the account that runs the refresh open the spreadsheet.
• If the sharing is correct, give access to Advanced Scheduler again in My Account → Team Preferences. See Advanced Scheduler lost access.

If you need help, contact [email protected]. Send the message, the name of the query or the schedule, and the URL of the spreadsheet.