Category: Advanced Google Services

List All Users in an Organisation’s Google Workspace Account with Google Apps Script

Note! This tutorial is for Google Workspace for organisations and not the free consumer account, unfortunately. 

While the Google Apps Script docs provide a great example of how to get a list of users in a Domain on a Google Workspace account, it is not in the scope of the documentation to go into the weeds and explain all the ways we can search for all users.

Weeds sound much more like the purview of a goat. A coding goat, perhaps 🐐. Me. I’m talking about me…yeesh!

In this tutorial, we will cover how to access your Google Workspace organisation’s user data, what data you can retrieve and how it looks, who can retrieve it and a couple of ways to display what you need.

This post is intended as a resource reference that compliments the Google Docs on the Admin SDK. Links to the Google documentation are provided throughout the post. It is worth a bookmark if you intend on using the Admin SDK a lot in Google Apps Script.

Use the contents page to navigate to what you need.

Continue reading “List All Users in an Organisation’s Google Workspace Account with Google Apps Script”

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2022)

Google Apps Script: DriveApp, Advanced Drive Service, SpreadsheetApp,  DocumentApp Javascript: spread operator, map, regex

Ah, bureaucracy at its finest.  Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. A… p…d…f.

Gee, thanks.

After some prostrations to the great Google Apps Script gods, I had a though.

“Hey, can’t we convert a PDF to a Google Doc with just a click of the button? Surely the great Google Apps Script devs have made it so we can do it programmatically too.”

And you know what? They bloody well did. The big legends.

The Scenario

I’ve just received a bunch of PDFs. The PFDs are all labelled by the class number. Take a look at the files in my Google Drive:

PDFs of sections in Google Drive

Each PDF file contains a list of student IDs that I need to extract and put into a Google Sheet.

PDF file with student IDs

The aim is to have a list of student IDs in column A and their corresponding sections in column B.

As you can see, we have some pretty standard text in the PDF that should be easy for Google to recognise so that we can extract the IDs.

The list of names in the demo sheets were randomly generated by AI!

NOTE! As always, I have tried to create this tutorial for varying levels. Feel free to follow along, or just grab what you need and get stuck into your own project.

If you are playing along, you can find a copy of the PDF files below. Simply add them to your own Drive before you get started:

PDF files by Section.

Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet (Updated Feb 2022)”

Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 3                                         <<Intro>>

In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice of each question item from the survey results in a Google Sheet.

This time we are going to add a final element to our mix. Let’s say we have multiple questions and multiple groups. We want to find out the count for each choice for each question for each group.

Continue reading “Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite [Update 06 Apr 2022]

Google Apps Script, G Suite Admins, Google Calendar, Calendar API,  AdminDirectory, GroupsApp 

Note! This approach no longer force subscribes users to a Calendar. The new approach can be found here. [update 06 Apr 2022]

It appears that the approach below no longer force-subscribes users in a Group to a calendar. Google Made this change in March of 2020, some 7 months after the post was written.

Service accounts created on or after March 2, 2020 are only able to invite guests using domain-wide delegation of authority .

There is also a note in the Sharing Calendars chapter of the Calendar API information stating:

Note: Sharing a calendar with a user no longer automatically inserts the calendar into their CalendarList. If the user should be able to see and interact with the shared calendar, you will also need to call the CalendarList: insert() method.

While this explanation is no doubt helpful for general use, using the insert() method in CalendarList requires that the script be run by the user that needs to be shared.

One hint to a workaround can be found in the update notes referring to the ability to invite guests using domain-wide authority using a service account.

This is a pretty gnarly process to set up and write about and has taken some time to write about and test, but you can now find the tutorial here:

https://yagisanatode.com/2022/04/05/how-to-force-subscribe-a-user-in-your-domain-to-a-google-calendar-in-google-apps-script/

Having said all that, the script below does still contain some interesting use cases for using the Admin SDK service and Advanced Calendar API service in Google Apps Script, so I will leave it up until I can publish a solution.

The script below can now be modified to to extract the users in a group and then use a Service Account on each user to force subscribe them using the code in the tutorial link above.

Big thanks to Jeremy for bringing this to my attention and apologies, I could not find a quick fix.

~ Yagi 06 April 2022

Note! This article is for G Suite users and admins. You won’t be able to apply the same code to Consumer accounts. 

Google Groups in  G Suite can be a really convenient and clean way to share users to drive folders and file locations, email users and set up Google+ community locations. However, I have come across some issues with sharing Google Calendar to Google Groups.

When you assign a Google Group email to a Google Calendar it sends out an email inviting the users in the group to accept the calendar into their list of shared calendars. The user must then accept the invitation before the calendar is added to their Google Calendar list of shared calendars…calendar.

The users receive an email message like this:

Hello Goat_GroupAlpha,

We are writing to let you know that Billy.Goat@yagisanatode.com has given you access to view events on the Google Calendar called “Test”.

Click this link to add the calendar.

– The Google Calendar Team

The Problem

In a large organisation, not all users in a group will add the calendar using the link. This might be okay but if the calendar is important or the user, as is often the case, simply just forgot to add it, then there will be emails to you the admin in the future asking you why they don’t have the calendar that everyone else has and has now missed some important event…sigh.

Another issue is that when a new user is added to a group when the group has already been added to a calendar, then that user will not receive an email invitation to the calendar and won’t be able to see the calendar.

Almost a Solution

Continue reading “Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite [Update 06 Apr 2022]”