So I’m fairly new to Apps Script, and I’m trying to export some pieces of user information from the Google Admin Console to a Google Sheet, then replace it with updated information. However I’m having two issues:
- It looks like some of the parameters I’m using for some pieces of today are incorrect. For example I’m able to return the names, emails and OU paths, but the rest are blank.
// Updates user info
function getuserInfo() {
let page = '';
let pageToken = '';
let staffList = [];
do{
page = AdminDirectory.Users.list({
customer: 'my_customer',
maxResults: 10,
pageToken: pageToken,
})
// Store only selected info for each user.
let staffMember = page.users.map(user => {
return {
'firstName' : user.name.givenName
'lastName' : user.name.familyName,
'email' : user.primaryEmail,
'ouPath' : user.orgUnitPath,
'status' : user.Status,
'lastsignIn' : user.lastsignIn,
'emailUsage' : user.getStoragedUsed(GmailApp),
'driveUsage' : user.getstoragedUsed(DriveApp),
'totalStorage' : user.getstoragedUsed,
'mfaEnrolled' : //Not Sure
'mfaEnforced' : //Not Sure,
'adminRoles' : //Not Sure,
}
})
staffList = staffList.concat(staffMember)
pageToken = page.nextPageToken;
}while(pageToken)
return staffList;
}
- The next issue is I’m having is adding the information to the connected Sheet. I’d like to have each piece of user information appear in separate columns (Columns A to K), however i’m not sure
/// Get a reference to the sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test');
var range = sheet.getRange("A:K");
//Clears Old Content so Sheet is up to date each time it is ran
range.clearContent();
// Write the headers to the sheet
var headers = sheet.getRange("A1:K1").
sheet.ders.setValues(['firstName','lastName','email','ouPath','status','lastsignIn','emailUsage','driveUsage','totalStorage','mfaEnrolled','mfaEnforced','adminRoles']);
// Loop through the users and write them to the sheet
for (var i = 0; i < users.length; i++) {
var user = users[i];
sheet.appendRow([user.name.givenName,user.name.familyName,user.primaryEmail,user.orgUnitPath,,user.lastsignIn,GmailApp.getStoragedUsed,DriveApp.getStorageUsed,user.getStoragedUsed,user.mfaEnrolled,user.mfaEnforced,user.adminRoles]);
}}
I’m assuming there’s quite a few errors in here, but not sure how to check.
Trying to export some pieces of user information from the Google Admin Console to a Google Sheet, and then clear the data in the Sheet and replace it with updated information.
What actually happened: No error code, script just continously runs and no output in the sheet.