0

I am using this code to retrieve the User Usage Report. I am able to get the 7 parameters out of 12.enter image description here

Questions:

  1. Is it okay to combine accounts and gmail parameters? If yes, how would I fix the code to display the same format as the "Last Login"?

All shows "undefined": 'gmail:timestamp_last_access', 'gmail:timestamp_last_imap', 'gmail:timestamp_last_pop', 'gmail:timestamp_last_webmail'

  1. What is the parameter reference for Account Status = Active, Suspended, Deleted...etc.? I have tried searching but no luck.

I am using this code below. Thanks in advance for your time and help!

function generateUserReport() {
  var today = new Date();
  var twoDaysAgo = new Date(today.getTime() - 2 * 24 * 60 * 60 * 1000);
  var timezone = Session.getScriptTimeZone();
  var date = Utilities.formatDate(twoDaysAgo, timezone, "yyyy-MM-dd");

  var parameters = [
    'accounts:first_name',
    'accounts:last_name',
    'accounts:is_super_admin',
    'accounts:last_login_time',
    'accounts:gmail_used_quota_in_mb',
    'accounts:drive_used_quota_in_mb',
    'gmail:timestamp_last_access',
    'gmail:timestamp_last_imap',
    'gmail:timestamp_last_pop',
    'gmail:timestamp_last_webmail'
  ];
  var rows = [];
  var pageToken;
  var page;
  do {
    page = AdminReports.UserUsageReport.get('all',date, {
      parameters: parameters.join(','),
      maxResults: 500,
      pageToken: pageToken
    });
    if (page.warnings) {
      for (var i = 0; i < page.warnings.length; i++) {
        var warning = page.warnings[i];
        Logger.log(warning.message);
      }
    }
    var reports = page.usageReports;
    if (reports) {
      for (var i = 0; i < reports.length; i++) {
        var report = reports[i];
        var parameterValues = getParameterValues(report.parameters);
        var row = [
          report.date,
          report.entity.userEmail,
          parameterValues['accounts:first_name'],
          parameterValues['accounts:last_name'],         
          parameterValues['accounts:is_super_admin'],
          parameterValues['accounts:last_login_time'],
          parameterValues['accounts:gmail_used_quota_in_mb'],
          parameterValues['accounts:drive_used_quota_in_mb'],
          parameterValues['gmail:timestamp_last_access'],
          parameterValues['gmail:timestamp_last_imap'],
          parameterValues['gmail:timestamp_last_pop'],
          parameterValues['gmail:timestamp_last_webmail']
        ];
        rows.push(row);
      }
    }
    pageToken = page.nextPageToken;
  } while (pageToken);

  if (rows.length > 0) {
    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheetByName('UserSheet');
    sheet.clear();

    // Append the headers.
    var headers = ['Date','User','First Name', 'Last Name','Admin Account Status', 'Last Login', 'Email Usage', 'Drive Usage','Last Access Date',
                    'IMAP','POP','WEBMAIL'];
    sheet.appendRow(headers);

    // Append the results.
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

    Logger.log('Report spreadsheet created: %s', spreadsheet.getUrl());
  } 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(function(result, parameter) {
    var name = parameter.name;
    var 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;
    return result;
  }, {});
}
user7935276
  • 205
  • 2
  • 15
  • Maybe I lack experience but could you maybe explain what do you mean by *Is it okay to combine accounts and gmail parameters?* – Raserhin Oct 14 '20 at 15:46
  • Hi @Raserhin, This is my first time using the Reports API, so I am not familiar with it as well OR maybe I am not defining the gmail:timestamp_last_access', 'gmail:timestamp_last_imap', 'gmail:timestamp_last_pop', 'gmail:timestamp_last_webmail' correctly in my code above. – user7935276 Oct 15 '20 at 00:14
  • What is your main goal here? I'm struggling to understand what information you want to retrieve. But neverthless take a look at the [documentation](https://developers.google.com/admin-sdk/reports/v1/reference/userUsageReport/get) for this endpoint. The last parameter is providing this information `An example of an invalid request parameter is one that does not belong to the application. If no parameters are requested, all parameters are returned.` – Raserhin Oct 22 '20 at 14:41
  • That is what I thought. I guess I'll have to call those last four fields separately in another sheet. – user7935276 Oct 22 '20 at 22:43
  • Have you managed to solve your problem? I had tried but I couldn't solve it. – Raserhin Nov 09 '20 at 11:20
  • @Raserhin, I managed to find a solution - check this link: https://stackoverflow.com/questions/66443027/google-apps-script-not-outputting-returned-results-to-sheet – user7935276 Apr 10 '21 at 02:17

0 Answers0