Google Classroom

[Kristina Sedbrook]

Is there a GAM script to show the last time a user logged into classroom?

[Waseem Mohamed Laher]

Would like to request same.

[Ross Scroggs]

Kristina/Waseem,

The Google APIs don't supply exactly what you're asking for; here's the closest thing I found.

https://developers.google.com/admin-sdk/reports/v1/appendix/usage/user/classroom

Basic GAM:

export GAM_CSV_ROW_FILTER="'\"classroom:role\":regex:student'"

gam report user parameters classroom:timestamp_last_interaction,classroom:role > UserClassroomInteraction.csv

Advanced GAM

gam config csv_output_row_filter "'\"classroom:role\":regex:student'" redirect csv UserClassroomInteraction.csv report user parameters classroom:timestamp_last_interaction,classroom:role

Ross

--

ross.s...@gmail.com

On Jan 13, 2021, at 11:13 AM, Kristina Sedbrook <kristina...@dish.com> wrote:

Is there a GAM script to show the last time a user logged into classroom?

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/c0846aeb-d211-4a1d-884d-d96bfbb400e3n%40googlegroups.com.

[Waseem Mohamed Laher]

Thanks Ross

Will try the Basic GAM code you provided.

Thanks for the assistance.

 Waseem Mohamed Laher 

You received this message because you are subscribed to a topic in the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-manager/lmqZ4a8MHBo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/F0ECFBE9-5FF9-4453-80B2-BFD5B8F1C519%40gmail.com.

[Simon Laycock]

I have a pretty decent system going for this now, thanks to Ross's help a week or so ago. Here is the process:

1. Run a batch file hourly which gets the last classroom interaction value for all students and replaces a sheet in a Google Spreadsheet with these values.
2. Inside that spreadsheet, run a Google Apps Script function every hour to take the latest GAM data and add it to a "register". The code matches the column using part of the timestamp and the row using the student email address.
3. Use importrange() in another spreadsheet to bring this data in so I can give other staff view access and there is no chance of breaking it. Conditional formatting highlights people who aren't logging in, and filter views allow staff to filter to certain groups.
4. Use pivot tables for analysis.

The first spreadsheet looks like this:

And the final spreadsheet I share with staff looks like this:

There's also some analysis of average days spent per week. This needs some more work:

This is the GAM command running in the batch file. It uses regex to remove some old students with letters, the number 4 or 12-15 at the start of their username:

gam config csv_output_row_drop_filter "email:regex:(^1[2345].*$)|(^[a-z].*$)|(^4.*$)" report user ou /Students parameters classroom:timestamp_last_interaction todrive tdfileid SHEET ID GOES HERE tdtitle "Classroom Daily Activity Report" tdupdatesheet tdsheet "GAMData" tdnobrowser tdnoemail

The apps script looks like this. I'm sure it could be improved, but it works:

/**

 * @OnlyCurrentDoc

 */

  

function appendDailyData() {

  //Get the list of valid emails

  var emails = [];

  registerSheet = SpreadsheetApp.getActive().getSheetByName("Register");

  lastRow = registerSheet.getLastRow();

  emailData = registerSheet.getRange(1, 1, lastRow, 1).getValues();

  emailData.forEach(function(row) {

    emails.push(row[0]);

    })

  

  //Get the list of available dates as text strings

  var dates = [];

  registerSheet = SpreadsheetApp.getActive().getSheetByName("Register");

  lastColumn = registerSheet.getLastColumn();

  datesData = registerSheet.getRange(1, 1, 1, lastColumn).getValues();  

  for (var row in datesData) {

    for (var col in datesData[row]) {

      dates.push(String(datesData[row][col]));

    }

  }

  

  console.log(dates);

  

  //Get the new data from GAMData

  var GAMrows = [];

  GAMSheet = SpreadsheetApp.getActive().getSheetByName("GAMData");

  lastRow = GAMSheet.getLastRow();

  GAMData = GAMSheet.getRange(1, 1, lastRow, 3).getValues();

  

  //For each row in GAMData, check if it matches an email in the register tab. If so, get the row in the register to update. 

  numRows=0;

  rowToUpdate=0;

  columnToUpdate=0;

  GAMData.forEach(function(row) {

    numRows+=1;

    if (emails.includes(row[0])) {

      //Get the row in the register.

      rowToUpdate = emails.indexOf(row[0])+1; 

    

      //Next check if it matches a date in row 1 of the register.

      //var theDate = String(row[1]);

      var theDate = String(row[2]).substring(0,10); //This takes the date from the last interaction rather than the date column

      console.log(theDate);

      if (dates.includes(theDate)) { 

        //Get the column in the register

        columnToUpdate = dates.indexOf(theDate)+1;

        

        

        //If both are matched then update the cell with the time accessed.

        if (theDate == row[2].substring(0,10)) {

          registerSheet.getRange(rowToUpdate,columnToUpdate).setValue(row[2].substring(11,16));

        }

        

      

      

      }

    }

    

    else {

      columnToUpdate="None"

    };

    }) //End of FOR loop

  

  

} //End of function