google-apps-scriptgoogle-sheetsgoogle-domain-api

User Drive's usage without shared files


I'd like to create a report of storage usage for all my users. To do so I use AdminReports app, like so (found in google example, somewhere. Just had to adapt the "parameters" and the "row" arrays) :

function generateUserUsageReport() {
  var today = new Date();
  var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
  var timezone = Session.getScriptTimeZone();
  var date = Utilities.formatDate(oneWeekAgo, timezone, 'yyyy-MM-dd');


  var parameters = [
    'accounts:gmail_used_quota_in_mb',
    'accounts:drive_used_quota_in_mb',
    'accounts:total_quota_in_mb ',
    'accounts:used_quota_in_percentage'
  ];
  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,
          parseInt(parameterValues['accounts:drive_used_quota_in_mb']),
          parseInt(parameterValues['accounts:gmail_used_quota_in_mb']),
          parseInt(parameterValues['accounts:total_quota_in_mb']),
          ((parseInt(parameterValues['accounts:gmail_used_quota_in_mb'])+parseInt(parameterValues['accounts:drive_used_quota_in_mb']))/parseInt(parameterValues['accounts:total_quota_in_mb']))*100
        ];
        rows.push(row);
      }
    }
    pageToken = page.nextPageToken;
  } while (pageToken);

  if (rows.length > 0) {
    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getActiveSheet();

    // Append the headers.
    var headers = [['Date', 'User mail', 'Drive use','Gmail use', 'Total available',
        'Total(%)']];
    sheet.getRange(1, 1, 1, 6).setValues(headers);

    // Append the results.
    sheet.getRange(2, 1, rows.length, 6).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;
  }, {});
}

The issue I have is that the parameters "accounts:drive_used_quota_in_mb" gives you the drive usage WITH the shared files (which is irrelevant to calculate the storage used by a user ( to determine whether he needs more space or not)).

I even tried to use 'accounts:used_quota_in_percentage' which seemed to be exactly what I need, but it calculate the percentage the same way i do : ((drive + mail)/total space)*100, and no way to ignore shared files to do so.

I'm working on the possibility to check every files of the drive, but you know the next problem : slowness.. (just for 1User with few docs, it take 1-2minutes)

Is there a way to do so by script, with another class, or something that is done for it in google that I didn't see?


Solution

  • Ok, there is no issue, I just freaked out because a user was at 30Go consumption although he has his account for only 2month.

    But after discussing with him, he did upload heavy files one week ago, and since, he deleted it.

    And executing the script for only 2days ago gives the correct result, since he deleted these files between these two dates.

    The reason of my mistake is that my script was providing stats that was 1 Week old (without me being conscious of that), and I was checking the veracity of theses stats on the web interface, that incidates nowadays stats.