Demand Sage is provided as a Google Sheets Add-on. This is convenient for our customers because it allows us to provide control of their data without ever having to leave the Google Sheet they are working in. Unfortunately, this also means we have to deal with a bug in Google Sheets that affects all Sheets Add-ons.
The bug Itself
Google’s applications (Gmail, Analytics, Sheets, etc.) allow you to be logged into multiple Google Accounts at the same time. For example, clicking on the icon in the upper right corner of the Google Doc I’m writing this from shows that I’m currently logged in as “demandsagedemo@gmail.com” and as “henry@demandsage.com”:
For most applications, selecting the account you want works just fine. However, with Add-ons there is a bug where the Add-on will always revert back to the first account you logged in with (The “default” account) regardless of what you select in this list.
For example, let’s say I’m logged in as above, with the demo account first, and then my work account second. However, my work account is the one that I’ve set up Demand Sage with. When I open my Spreadsheet and bring up the report library, I’ll likely get this error:
...even though I absolutely do have permission to access the document. This is happening because while henry@demandsage.com has access to everything, and is the currently activated account, Google Add-ons is incorrectly trying to use demandsagedemo@gmail.com.
This situation will happen any time a customer uses Demand Sage with a work account but their work account isn’t the first account that was logged in as and it will happen with any Google Sheets Add-on. The most common situation we see are users who are logged in with their personal accounts first and work accounts second.
Possible WorkArounds
Google has been aware of this bug since November, 2017 and still hasn’t fixed it so it’s best to plan on working around this issue for the time being. While there is not great solution, there are a few options:
- Log out of all accounts and log in with the account that has access to Demand Sage (usually a work account) first. This is the most reliable long term solution.
- Use an incognito / private window. An incognito window is logged out of all accounts by default so logging in with the work account will cause it to be the only logged in account. This is great for a quick fix, but logging in multiple times can be cumbersome.
- Use a seperate browser for different accounts. This is a variation of #2. By devoting an entire browser to work and another to personal accounts it avoids this problem and because the session isn’t in an incognito / private window you can stay logged in over time.
Additional Details About The Bug
This issue was first reported in November, 2017 [https://issuetracker.google.com/issues/69270374]. Google explained that it was caused by Sheet’s multi user updates:
Thank you for your reports. The issue originally reported is due to recent improvements in Sheets' ability to handle multiple logged in accounts. These improvements expose limitations in Apps Script's ability to do the same. In particular, users may experience issues if they (1) are logged into more than one gmail.com account or more than one account belonging to the same G Suite domain, (2) have selected an account other than the default account while using Docs, Sheets, Slides, or Forms, and (3) use add-ons or the script editor.
Several different fixes are proposed online, including reverting to an older version of Google’s Javascript engine, however none of these fix it, they simply change the way the error gets reported.
From a developer standpoint, the best approach right now is to store the email address of the user who authorized the Add-on somewhere accessible (in the DOM if you are showing a frame, for example) and compare that to the user that is Add-ons is currently operating as using the getEffectiveUser API [https://developers.google.com/apps-script/reference/base/session#getEffectiveUser()]. And if they disagree throwing an error and directing your users to a post like this one.
