Grant access to resources 🚣 from a Google Sheet in Google Workplace (formerly G Suite)

Share documents, events, and communications with users outside (or inside) your Google Workspace domain.

Google Sheet with an Apps Script to automate access to resources via Google Groups

Use a Google Group to work cross-functionally with vendors, partners, customers, and volunteers outside of your domain, and scale access to work assignments and leverage security controls.

You can bulk add users to a Google Sheet and keep track of their onboarding with…

• an external user’s Google email (this can be their own G Suite account
• a G Suite user account you created for them within your domain
• or a free Gmail account) to your G Suite Google group (not available for consumer Google Groups)

Table of Contents

• 4 min video overview (optional)
• Top 6 benefits of this solution
• Technology highlights
• Steps to try it out
• Next step for beginners
• Full code

Follow me on Twitter here.

4 min video overview (optional)

Top 6 benefits of this solution:

1. Adds a new external user to a G Suite Google Group that you create (example: externalteam@mydomain.com) once, and it allows users to open all resources that are shared with that group moving forward (calendar, files, training site, dashboards (built on DataStudio), etc.
2. When adding one or more users to your Google Group, users can receive a welcome email with the links they can access.Creating a Google Site is optional but recommended to centralize information in a beautiful website interface.
3. The template of the welcome email is composed in a Google Doc, and the script renders it as HTML. You can use separate Google Docs for different recipients to customize messaging.
4. Capture the email addresses of users in your Sheet, and add them in bulk to the desired Google Groups at a later time by changing the column “Allowed” to “Yes” for each row of users.
5. You or members of your team, can add users to different Google Groups as long as the person who made a copy of this sheet has manager or owner rights in each of those Google Groups.
6. Removing a user from a Group (via the Google Group’s interface) will revoke their access to all resources. You can also limit documents to be available to that group for a specific period of time.

Technology highlights

• Install a trigger with a click so the script is setup to run every time the Sheet is edited. To learn more visit the Apps Script trigger guide)
• Add members to a Google Group for Business using the Admin Directory API

Try it

50 second walk through

Note: This includes the old Apps Script interface as of April 2020

Steps

1.Copy the Sheet that contains the script here: External Sustainability Group List from your G Suite account.

2.Enter for testing purposes, a Gmail address you own and a Google Group you have rights to manage its membership. You can leave all the other fields populated as they are.

You can learn about group permissions here.

Screenshot of information to enter into the sheet

Note: The membership status will be populated by the words Newly added if the user was successfully added to the group, or Already added if it recognizes the user is already a member of that group.

3. Enter “Yes” in the “Allowed” column.

4. Next, click the custom menu called Install Trigger > onEdit, this will install a trigger that will run every time a value is changed in the sheet.

5. When prompted, click the Review permissions and click Allow so the script can email on your behalf.

Note: If you get a warning that This app isn’t verified continue with the verification process by clicking Advanced and then scroll down and click the grey text at the bottom that begins with Go to…

6. Check to see your trigger was created by visiting Tools > Script Editor in your Sheet. When you arrive to the Apps Script editor, select the 🕒 clock icon at the top.

7. If there is no trigger, return to your spreadsheet and click Install Trigger > onEdit once more.

8. Then refresh the trigger page to ensure one is created.

Caution: You don’t want to have multiple onEdit scripts, only one.

9. Mark the column allowed with the drop-down option “Yes.”This will populate the last field confirming an email was sent or if that user was already added to that group.

10. When it says “sent,” check your inbox to see the email, and then your Google Group’s interface to see the new member added.

Note: you can delete a user from the Google Group to keep testing with the same user in your Sheet.

Customize your messaging

• If you wish to change the subject lines of your emails, replace the text in the “Email subject” column.
• If you wish to change the email template that is sent out, replace the URL in the “Email template” column with your preferred Google Doc. If you wish to include any of the column values in the template, enter them as such in the template {{Column_name}} like this: Welcome, we have added your {{Email}} to this {{Google_Group}} in order give you access to the following resources…

Note: If you encounter any issues with the welcome email, change the permission levels of the Google Doc templates to more open settings.

Next steps for beginners

To get started with Google Apps Script, try out the codelab which guides you through the creation of your first script.

Full code