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])
});