Bulk update user custom attributes

[DamianLofthouse]

I am managing my organisation of around 600 users, and we are trying to take advantage of custom attributes in order to build dynamic groups to control user access.

I can see there is a bulk update option for users via csv upload, but this only appears to include system attributes and not my custom ones.

Is there a way, either via the admin portal or via API that will allow me to bulk update the custom attributes across my organisation without going through each person individually?

[icrew]

You can do this with the free, open source, command-line, indispensable GAM tool. Check out:

• GAM: https://github.com/jay0lee/GAM/wiki
• GAM Advanced (a variant with a few more features): https://github.com/taers232c/GAMADV-XTD3/wiki
• and the mailing list at https://groups.google.com/forum/#!forum/google-apps-manager

for more details.

Specifically, check out https://github.com/GAM-team/GAM/wiki/Command-Reference:-Users#gam-who-update-user-options

Hope that helps,

Ian

[jerry_gassie]

Ideally, Google will establish the means to apply app-specific custom attributes so that the individual accounts won't have to be modified.  It's a maintenance nightmare.  Other IdP SAML implementations allow for this.

[vrnazre]

Here is a app script code which uses a Google sheet to retrive the values. Make sure to add the Admin SDK service to the project, Also make sure to replace necessary parameters in the below code. This I had designed for a Custom Schema with one Category and Single Valued Field.

function myFunction() {

  var app = SpreadsheetApp;

  var ss = app.openById("Sheet ID here"); // Add your sheet ID here

  var sheet = ss.getSheetByName("Sheet1"); // Add your sheet name here

  var lr = sheet.getLastRow();

  var optArgs = {

    projection: "full",

  };

  var category_cs = "Privileges";  // fill in your  Custom Schema Category.

  var field_cs = "Privileges";    // Fill in your custom schema field.

  for (var i = 2; i <= lr; i++) {

    // get status from sheet if already updated

    var update_status = sheet.getRange(i, 3).getValue();

    // Check status if already updated

    if (update_status != "Yes") {

      var userEmail = sheet.getRange(i, 1).getValue(); // get email addresses are in the 1st column

      var customSchemaValue = sheet.getRange(i, 2).getValue(); // get the values of custom schema from the 2nd column

      // checking if user has any custom schema

      var load = AdminDirectory.Users.get(userEmail, optArgs);

      var schema = load.customSchemas || {};

      // dynamically updating the schema with values in each iteration

      schema[category_cs] = schema[category_cs] || {};

      schema[category_cs][field_cs] = customSchemaValue;

      // Update the user's custom schemas using Admin SDK

      load.customSchemas = schema;

      AdminDirectory.Users.update(load, userEmail);

      // set the status as yes once updated in the sheet

      sheet.getRange(i, 3).setValue("Yes");

    }

  }

}

[b_gorsky1]

@vrnazre That is great. I set it up and tested it and it was so simple to do!

I'd like to automate it. Do you think this would work?

1. Set up the Google Sheet with all users with the "Updated?" column initially set to "YES" (assuming that all users have the custom attribute set already)
2. In the Apps Script, enable either:
   • a 'Time Trigger' to run every hour or
   • a 'From Spreadsheet' trigger based 'On Edit' 
3. Then when I want to update a users attribute, I can just set the new attribute and clear the "YES" value from the "Updated?" column

[vrnazre]

The thing is I had added the "Yes" status to make sure the completed ones don't get updated again. As there is some quota tied to the API. Yes, definitely you can automate it based on your requirements. You can further make this code better by simply retriving the schema category and field from the sheet itself(like a sheet context based browser input box or from a some fixed cell in the sheet) 

If you want to give this control to an end user, just set up a form and add necessary fields in the form and set a trigger to have on-submit.

[b_gorsky1]

@vrnazre Thank you for this.

[b_gorsky1]

I know this is a stretch, but is there any way to keep this Sheet synced with the Google Workspace Directory so that if an admin updates an individual user in Google Admin Console it would update the Sheet? Essentially a bi-directional sync between the Sheet and Google Directory.

[Caine]

Hi @vrnazre I got this error when I use the code.

GoogleJsonResponseException: API call to directory.users.update failed with error: Invalid Input: custom_schema

myFunction

code:gs:31