Interacts with the Activities.list endpoint available on GSuite domains that lists lots of Meets activity information. It's the same endpoint used in its own reporting console, such as the Meets Quality Tool.
It downloads row-by-row all of this raw information, going back days number of days, saves into the spreadsheet into meet data sheet.
Additional sheets contain pivot tables that provide insights into the data, such as IP location info, duration of calls, frequency of use. Leadership boards are derived from those pivot tables.
MIT, credit @clssrmtechtools Adam Morris Website, Email
Leaders in school in distance learning mode who want to see how their Google Meets service is being used (send them to this write-up), curious GSuite admins, data enthusiasts, AppsScripts writers of libraries, and spreadsheet junkies.
Since this is an OpenSource tool for fellow GSuite admins, I see no real downside to releasing the code in the following manner: It's a spreadsheet with script contained. You're supposed to open up the Script Editor and execute it.
-
Make a copy of this spreadsheet;
-
Tools —> Script Editor;
-
Change the
daysvariables to see how back in time to go; -
Execute, and wait. Depending on how much data you're downloading, it's gonna take a while;
-
No, you can't make this process concurrent (because like a lot of Google APIs, it pages according to tokens that are only obtainable with each roundtrip);
-
Yes, you could make this an add-on, which I welcome. All of this it MIT licensed opensource, baby.
In a conversation with @schoraria911, I realized that some of the libraries that I wrote were kinda obscure. A project like this that tied together some of those libraries would be worth my time.
It would also be worth everyone's time to have a tool like this.
As a GSuite admin in a school in distance learning mode, I knew this would be useful to a very specific community.
The raw data from the API endpoint comes back in jsons, which doesn't fit well into a spreadsheet. So what I needed was a piece of code that could take a bunch of jsons and convert them into "rows" or 2d arrays. This is an interesting problem that I had solved previously from another project.
The key is to take nested jsons and convert them into flat jsons, where each key uses dot-and-brackets notation to write the original path to the value. Easy example:
let obj = {
an: {
array: [1, 2]
},
path: {
to: {
value: 'value'
}
}
};
// convert that to:
let obj = {
'an.array[0]': 1,
'an.array[1]': 2,
'path.to.value': 'value'
};If all my jsons can be converted to that, then we have our headers for our columns, and rows would just be the values. Easy!
The javascript library dot-object was available, which provided an easy way to work with javascript objects in the manner described above, turning a nested objects into a single flat object.
So I wrote a library dottie that brought dot-object's ability to AppsScripts. I could then add a method jsonsToRows which converted an array of jsons into spreadsheet-friendly rows. In that way, all I would have to do is call .setValues and we have all the data written to a spreadsheet.
Simple right? If only!
Just like in any project, there was an unexpected corner case which needed a solution.
So I had all the Meets data written to rows, and when I started looking at making pivot tables, I realized that the columns didn't match up.
email | events[0].parameters[0].name | events[0].parameters.value
[email protected] | ip_location | MY
but then the next row would have this:
[email protected] | duration | 12345
That is how the data itself was organized, where not all of the rows had the the datapoints matching in the columns. Had I made a huge blunder somewhere? Checking out the documenation of the response at this endpoint, this is what it gives as the format of the parameters field:
"parameters": [
{
"name": string,
"value": string,
"intValue": long,
"boolValue": boolean,
// more...
}
]Ugh. So instead of the name being a property name, it was a value which appeared in different order in the data.
So I had to code up a loop in there that updated the event object itself with properties defined by name field, whose value was either value or intValue or boolValue.
(If you're scratching your head wondering why on earth would Google do it this way, it's because this particular endpoint has to have parameter names as values, since it's an endpoint shared throughout the entire reporting ecosystem. The Meets features has things like ip_location and duration but the email reporting tools will have different values for parameters.)
The project needs a way to reach out to an API endpoint and returns the data in json format. The AppsScripts platform already has "advanced service" for this many endpoints, including AdminReports, but when I first tried it, it took an endless amount of time to work. It was downloading all the information for all users. Even when I downloaded all the information for just one user, it caused me significant delay while I was building it.
Instead, I used a nifty little library that called Requests that allows me to take a lower-level approach. I just got 10 or so data points and was able to get started that way. No need for me to wait minutes just to get to the next stage of the project. It lets me look at the reference documentation so that I can really pinpoint how this is supposed to work, so I can quickly get off the ground.
Advanced Services are usually great, but for bulk tasks like this I find it slow and cumbersome. Stripping it down in to his basic parts makes it much more performant.
Code which this project uses Requests is illustrated here:
// creates endpoint object, which knows how to do oauth
const endpoint = Requests.init({oauth: "me", baseUrl: 'https://www.googleapis.com/admin/reports/v1/activity/users/${user}/applications/${app}'});
// create HTTP get request, changing user and app to "all" and "meet"
const request = endpoint.get({user: 'all', app: 'meet'}, options);
// reach out to the internet, and returns response object
const response = request.fetch();
// check to make sure it worked
if (!response.ok) throw new Error("Returned " + response.statusCode);
// convert it to json
const json = response.json;