Google Apps Script Not Outputting Returned Results To Sheet
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
Solution 1:
So it turns out that the returned values are named differently.
Here is the completed script for anyone who needs something similar in the future:
//Set to true if developing/testing - if running a live version, set to falselet testState = false;
functiongenerateUserReport() {
let target;
//Check if dev or prod and change values as requiredif (testState == true) {
target = "Frodo.Baggins@domain.com";
} elseif (testState == false) {
target = "all";
} else {
throw"State not defined";
}
Logger.log("Test State: " + testState);
const daysToCheck = 14;
const now = newDate();
const daysAgo = newDate(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_interaction",
"gmail:timestamp_last_imap",
"gmail:timestamp_last_pop",
"gmail:timestamp_last_webmail",
"drive:timestamp_last_active_usage"
];
const rows = [];
let pageToken;
let page;
Logger.log("Target for API call: " + target);
do {
page = AdminReports.UserUsageReport.get(target, 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:last_access_time"],
parameterValues["gmail:last_interaction_time"],
parameterValues["gmail:last_imap_time"],
parameterValues["gmail:last_pop_time"],
parameterValues["gmail:last_webmail_time"],
parameterValues["drive:last_active_usage_time"]
];
//If testing, log rowif (testState == true) {
Logger.log("Logging row: \n" + row);
} else {
}
rows.push(row);
//If testing, log rowsif (testState == true) {
Logger.log("Logging rows after push: \n" + rows);
} else {
}
}
}
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 completionMailApp.sendEmail({
to: "my.email@domain.com",
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.
*/functiongetParameterValues(parameters) {
return parameters.reduce((result, parameter) => {
const name = parameter.name;
let value;
if (parameter.intValue !== undefined) {
value = parameter.intValue;
} elseif (parameter.stringValue !== undefined) {
value = parameter.stringValue;
} elseif (parameter.datetimeValue !== undefined) {
value = newDate(parameter.datetimeValue);
} elseif (parameter.boolValue !== undefined) {
value = parameter.boolValue;
}
result[name] = value;
//If testing, logout the resultsif (testState == true) {
Logger.log(`${name} Result: ${result[name]}`);
} else {
}
return result;
}, {});
}
Note how the returned values are 'last_' :
parameterValues["gmail:last_access_time"],
parameterValues["gmail:last_interaction_time"],
parameterValues["gmail:last_imap_time"],
parameterValues["gmail:last_pop_time"],
parameterValues["gmail:last_webmail_time"],
parameterValues["drive:last_active_usage_time"]
And not 'timestamp_last_' :
"gmail:timestamp_last_access",
"gmail:timestamp_last_interaction",
"gmail:timestamp_last_imap",
"gmail:timestamp_last_pop",
"gmail:timestamp_last_webmail",
"drive:timestamp_last_active_usage"
As I was trying to use in my original script.
So there is a completed version of it if anyone else needs it.
This is much more reliable that just using Google's 'last login' metric.
I added a column into my sheet in Column L that uses the formula: =MAX(E2:K2)
to get the most recent date for the user.
Solution 2:
That is very strange. It seems to be an issue with the keys of the parameterValues
.
If you build the row Array in the following way, then the values which aren't really undefined and are printed to the sheet:
Example:
const row = [
report.date,
report.entity.userEmail
];
Object.keys(parameterValues).forEach((key) => {
row.push(parameterValues[key])
});
Post a Comment for "Google Apps Script Not Outputting Returned Results To Sheet"