I have the following code, the purpose of which is to try and get some useful insight into the last login/activity information from Google as the 'last login date' metric isn't the most reliable.
const testState = true;
function generateUserReport() {
let state;
if ((test = true)) {
state = "[email protected]";
} else if ((test = false)) {
state = "all";
} else {
throw "State not defined";
}
Logger.log("Test State: "+ testState)
const daysToCheck = 4;
const now = new Date();
const daysAgo = new Date(now.getTime() - daysToCheck * 24 * 60 * 60 * 1000);
const timezone = Session.getScriptTimeZone();
const date = Utilities.formatDate(daysAgo, timezone, "yyyy-MM-dd");
const parameters = [
"accounts:first_name",
"accounts:last_name",
"accounts:last_login_time",
"gmail:timestamp_last_access",
"gmail:timestamp_last_imap",
"gmail:timestamp_last_interaction",
"gmail:timestamp_last_pop",
"gmail:timestamp_last_webmail",
"drive:timestamp_last_active_usage"
];
const rows = [];
let pageToken;
let page;
do {
page = AdminReports.UserUsageReport.get(state, date, {
parameters: parameters.join(","),
maxResults: 500,
pageToken
});
if (page.warnings) {
for (var i = 0; i < page.warnings.length; i++) {
const warning = page.warnings[i];
Logger.log(warning.message);
}
}
const reports = page.usageReports;
if (reports) {
for (var i = 0; i < reports.length; i++) {
const report = reports[i];
const parameterValues = getParameterValues(report.parameters);
const row = [
report.date,
report.entity.userEmail,
parameterValues["accounts:first_name"],
parameterValues["accounts:last_name"],
parameterValues["accounts:last_login_time"],
parameterValues["gmail:timestamp_last_access"],
parameterValues["gmail:timestamp_last_imap"],
parameterValues["gmail:timestamp_last_interaction"],
parameterValues["gmail:timestamp_last_pop"],
parameterValues["gmail:timestamp_last_webmail"],
parameterValues["drive:timestamp_last_active_usage"]
];
Logger.log("Logging row: \n"+row);
rows.push(row);
Logger.log("Logging rows after push: \n"+rows);
}
}
pageToken = page.nextPageToken;
} while (pageToken);
if (rows.length > 0) {
const spreadsheet = SpreadsheetApp.getActive();
const sheet = spreadsheet.getSheetByName("User Access Report");
sheet.clear(); // Append the headers.
const headers = [
"Date",
"User",
"First Name",
"Last Name",
"Account Last Login",
"Email Last Access",
"Email Interaction",
"IMAP",
"POP",
"WEBMAIL",
"DRIVE"
];
sheet.appendRow(headers);
// Append the results.
sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
Logger.log("Report spreadsheet completed");
// Send notification email on completion
MailApp.sendEmail({
to: "[email protected]",
subject: "The account Usage Report has completed",
htmlBody: `The script to get user account usage has completed.<br><br>Please click here view view it:<br>${spreadsheet.getUrl()}`,
name: "Script Helper"
});
} else {
Logger.log("No results returned.");
}
}
/**
* Gets a map of parameter names to values from an array of parameter objects.
* @param {Array} parameters An array of parameter objects.
* @return {Object} A map from parameter names to their values.
*/
function getParameterValues(parameters) {
return parameters.reduce((result, parameter) => {
const name = parameter.name;
let value;
if (parameter.intValue !== undefined) {
value = parameter.intValue;
} else if (parameter.stringValue !== undefined) {
value = parameter.stringValue;
} else if (parameter.datetimeValue !== undefined) {
value = new Date(parameter.datetimeValue);
} else if (parameter.boolValue !== undefined) {
value = parameter.boolValue;
}
result[name] = value;
Logger.log(`${name} Result: ${result[name]}`);
return result;
}, {});
}
It works, in part - but for some reason, it doesn't pull all of the returned information into the spreadsheet.
Columns A - E (headers: Date, User, First Name, Last Name, Account Last Login) all populate in the sheet as expected, however columns F-K (headers: Email Last Access, Email Interaction, IMAP, POP, WEBMAIL, DRIVE) do not populate at all and return null for some reason (despite the logging showing it finds values).
I have logged out each call of the getParameterValues function, and I can see that it is pulling the correct information, however, when I log out the row and rows it shows null. As far as I can see, it should work... so I'm confused and hoping that someone else can help or offer some advice?
Log below to show the results:
3:50:24 PM Notice Execution started
3:50:24 PM Info Test State: true
3:50:25 PM Info accounts:first_name Result: Frodo
3:50:25 PM Info accounts:last_name Result: Baggins
3:50:25 PM Info accounts:last_login_time Result: Wed Feb 24 2021 04:57:24 GMT-0500 (Eastern Standard Time)
3:50:25 PM Info gmail:last_access_time Result: Sat Feb 27 2021 01:37:20 GMT-0500 (Eastern Standard Time)
3:50:25 PM Info gmail:last_interaction_time Result: Fri Feb 26 2021 04:07:14 GMT-0500 (Eastern Standard Time)
3:50:25 PM Info gmail:last_webmail_time Result: Fri Feb 26 2021 12:41:25 GMT-0500 (Eastern Standard Time)
3:50:25 PM Info drive:last_active_usage_time Result: Fri Feb 26 2021 12:37:31 GMT-0500 (Eastern Standard Time)
3:50:25 PM Info Logging row:
2021-02-26,[email protected],Frodo,Baggins,Wed Feb 24 2021 04:57:24 GMT-0500 (Eastern Standard Time),,,,,,
3:50:25 PM Info Logging rows after push:
2021-02-26,[email protected],Frodo,Baggins,Wed Feb 24 2021 04:57:24 GMT-0500 (Eastern Standard Time),,,,,,
3:50:27 PM Info Report spreadsheet completed
3:50:28 PM Notice Execution completed
