If you go to connect to your Google Sheet only to find a strange dialog box that looks like this…
…it’s because your Google Workspace Admin has blocked third-party apps like GMass from accessing your Google Drive. Therefore, we can’t display the normal interface where you can choose your Google Sheets from a pretty drop-down menu — but we do generate this alternate interface so that you can still connect to your Sheet without losing any functionality.
But, you can still connect to your Sheet! There’s just a little bit of extra work involved.
The steps are:
- Pull up your Sheet in a separate browser tab.
- Find the Sheet’s ID in the URL and copy it to your clipboard.
- Paste it into the GMass field.
Pull up your Sheet in a separate browser tab.
Navigate to your Sheet just like you would anytime you want to see or edit your Sheet. Start at sheets.google.com if you’re not sure how to get to your Sheet. Once you’re looking at your Sheet, your browser will look like this:
We’re interested in the URL for the Sheet, and specifically the part of the URL that represents your Sheet’s ID. If the URL is:
then the Sheet’s ID is:
Copy it to your clipboard
Copy that to your computer’s clipboard using CTRL+C (Windows) or CMD+C (Mac).
Paste it into GMass
Now all you need to do is paste it into the GMass field in Gmail, and GMass will load the specific worksheets in that Sheet and ask you to choose — just like in the regular interface.
That’s it, and now you’re on your way!
Read on if you’re interested in the technical details behind this alternate workflow.
Why isn’t the Google Picker working?
You might notice that if you click the “Choose Sheet” link, you’ll get a screen that looks like this:
GMass uses the Google Picker (to work around Google Drive access restrictions that your Google Workspace Admin has imposed) to let you pick a file, and when you are able to pick a file, you don’t have to hunt down your Sheet’s ID manually and copy/paste it. Recently, though, it seems that the Google Picker has stopped working, and until we see an update from Google about it, there’s not much we can do.
What should my Google Workspace Admin change?
If you want to see the normal interface where you can choose your Sheet from the standard dropdown menu, your Google Workspace Admin needs to lift the restrictions in place on your Google Drive account. There are two places inside the Google Admin Console where restrictions on Drive may be in place.
Under “Drive and Docs” settings
Change OFF to ON
Under “API controls” settings
Go to third party access.
Under Google services, go to “View list”
Select Drive and change it to Unrestricted if it’s currently Restricted.
Lastly, you can take one final step and whitelist GMass
For good measure, you can “whitelist” GMass by forcing it to be a trusted app. However, even making GMass “trusted” doesn’t avoid all the blocks that your administrator has in place. Trusting GMass will not override the “Drive and Docs” setting above that allows access to the Drive SDK. As long as the Drive SDK API is disabled, the alternate Sheets interface will show in GMass, even if GMass is trusted. However, whitelisting GMass will solve an issue is if Google Drive is marked “Restricted” under “API Controls.” If Google Drive is “restricted” here, then the only way to use GMass with Sheets is to put in an exception for GMass by “trusting” it specifically.
To trust GMass:
1. Choose to “View list” under Configured apps.
2. Then go to Add app and search by OAuth App Name.
3. Then just search for “gmass,” and choose the first “GMass” app that comes up.
4. Choose the only OAuth Client ID that shows up.
5. Set “GMass” to “Trusted.”