Having issues with the Appscript pulling data from Google workspace using Reports API

Question

The following code is pulling data to Google sheets from Google workspace using Reports API. However it is giving me only last 2 days of data not sure why, there are no limits set in the script.

The code is the reference of the following question:

How to pull deleted, archived, suspended users data to Google sheets from Admin SDK >> Reports API using Appscript

function listUsers() {

var sheet = SpreadsheetApp.getActive().getActiveSheet();
var values = [];
var userKey = 'all';
var applicationName = 'admin';
var optionalArgs = {
  maxResults: 100
};

var response = AdminReports.Activities.list(userKey, applicationName, optionalArgs);
var activities = response.items;
if (activities && activities.length > 0) {
  Logger.log('REPORTS:');
  for (i = 0; i < activities.length; i++) {
    var activity = activities[i];

    //ONLY GET DATA IF ACTION IS EITHER "SUSPEND_USER", "DELETE_USER", or "ARCHIVE_USER"
    if(activity.events[0].name == "SUSPEND_USER" || activity.events[0].name == "DELETE_USER" || activity.events[0].name == "ARCHIVE_USER"){
        Logger.log('%s: %s (%s)', activity.id.time, activity.events[0].parameters[0].value,
        activity.events[0].name);
        //RETRIEVES THE TIMESTAMP, USER'S EMAIL, & THE EVENT NAME THAT WAS PERFORMED TO THE USER
        values = [[activity.id.time, activity.events[0].parameters[0].value,activity.events[0].name]]; 

        //SET THE DATA TO SHEET
        var lrow = sheet.getLastRow()+1;
        sheet.getRange("A"+lrow+":C"+lrow).setValues(values); 
    }

  }
} else {
  Logger.log('No reports found.');
}

}

I'm guessing that you need to make it a page request and use pageToken to go through all of the available pages. — Cooper, May 23 '21 at 19:37
Here's a simple example: https://stackoverflow.com/a/55307907/7215091 — Cooper, May 23 '21 at 21:42

score 1 · Accepted Answer · answered May 24 '21 at 08:53

Your code contains a limit for the maximal value of results per page:

var optionalArgs = {
  maxResults: 100
};

Set this value to 1000 instead of 100 or remove it - the default value for maxResults is 1000 - as per documentation

Should you want to retrieve more than 1000 results, you will need to retrieve nextPageToken from the response and pass it as pageToken to the optionalArgs for the next request.

You can reapet the request in a loop until you obtain all results.

Code modification:

function listUsers() {

    var sheet = SpreadsheetApp.getActive().getActiveSheet();
    var userKey = 'all';
    var applicationName = 'admin';
    var optionalArgs = {
        maxResults: 1000
    };

    var response = AdminReports.Activities.list(userKey, applicationName, optionalArgs);
    var activities = response.items;
    if (activities && activities.length > 0) {
        Logger.log('REPORTS:');
        for (i = 0; i < activities.length; i++) {
            var activity = activities[i];

            //ONLY GET DATA IF ACTION IS EITHER "SUSPEND_USER", "DELETE_USER", or "ARCHIVE_USER"
            if (activity.events[0].name == "SUSPEND_USER" || activity.events[0].name == "DELETE_USER" || activity.events[0].name == "ARCHIVE_USER") {
                Logger.log('%s: %s (%s)', activity.id.time, activity.events[0].parameters[0].value,
                    activity.events[0].name);
                //RETRIEVES THE TIMESTAMP, USER'S EMAIL, & THE EVENT NAME THAT WAS PERFORMED TO THE USER
                values = [
                    [activity.id.time, activity.events[0].parameters[0].value, activity.events[0].name]
                ];

                //SET THE DATA TO SHEET
                var lrow = sheet.getLastRow() + 1;
                sheet.getRange("A" + lrow + ":C" + lrow).setValues(values);
            }

        }
    } else {
        Logger.log('No reports found.');
    }


    //////// ADD THE FOLLOWING TO YOUR EXISTING CODE:


    while (response.nextPageToken) {
        var optionalArgs = {
            maxResults: 1000,
            pageToken: response.nextPageToken
        }
        var response = AdminReports.Activities.list(userKey, applicationName, optionalArgs);
        var activities = response.items;
        if (activities && activities.length > 0) {
            Logger.log('REPORTS:');
            for (i = 0; i < activities.length; i++) {
                var activity = activities[i];

                //ONLY GET DATA IF ACTION IS EITHER "SUSPEND_USER", "DELETE_USER", or "ARCHIVE_USER"
                if (activity.events[0].name == "SUSPEND_USER" || activity.events[0].name == "DELETE_USER" || activity.events[0].name == "ARCHIVE_USER") {
                    Logger.log('%s: %s (%s)', activity.id.time, activity.events[0].parameters[0].value,
                        activity.events[0].name);
                    //RETRIEVES THE TIMESTAMP, USER'S EMAIL, & THE EVENT NAME THAT WAS PERFORMED TO THE USER
                    values = [
                        [activity.id.time, activity.events[0].parameters[0].value, activity.events[0].name]
                    ];

                    //SET THE DATA TO SHEET
                    var lrow = sheet.getLastRow() + 1;
                    sheet.getRange("A" + lrow + ":C" + lrow).setValues(values);
                }

            }
        } else {
            Logger.log('No reports found.');
        }
    }
}

Thank you Sir, it works fine now. 1 last thing, this code is giving me event date, user email, event name. Is it possible that I can pull more information about these user in the same code like user name, user creation date, etc? — Mohammed Zaid, May 24 '21 at 23:03
Not directly, but once you obtain the user email, you can call the method `activities.list` again - now with the `eventName` `CREATE_USER` and setting `filters` to `USER_EMAIL==XXX` - whereby `XXX` is the email of the deleted user. See [this](https://developers.google.com/admin-sdk/reports/reference/rest/v1/activities/list) and [this](https://developers.google.com/admin-sdk/reports/v1/appendix/activity/admin-user-settings#CREATE_USER) documentation for correct implementation. — ziganotschka, May 26 '21 at 09:16
How to FILTER the data by date? Say i want data only for last 10 days, I tried this but not working: if (activity.events[0].name == "DELETE_USER" && Utilities.formatDate(new Date(activity.id.time), "GMT+1", "mm/dd/yyyy") > Utilities.formatDate(new Date('05/20/2021'), "GMT+1", "dd/mm/yyyy")) — Mohammed Zaid, May 29 '21 at 19:18

Having issues with the Appscript pulling data from Google workspace using Reports API

1 Answers1

Your code contains a limit for the maximal value of results per page:

Linked