Adding group members from CSV misses some emails

352 views
Skip to first unread message

Steve Martens

unread,
Nov 1, 2017, 11:55:58 AM11/1/17
to GAM for G Suite
Hello,

I've been using GAM for a few years, but really only scratching the surface in how I use it, so please forgive my ignorance.

I'm using the command

gam csv file.csv gam update group ~Group add member ~Email

to add many email addresses to many groups.  The process seems to work quite well but  over time I'm discovering that some email addresses which are present in the CSV are missing in the groups.  I've check the Groups Reports to ensure the users did not remove themselves from the groups, no record of such.  It seems that some just didn't get added.

How can I verify why some are missing?  What could be causing them to not be added?

Thanks for your assistance,

Steve

Andy Poirier

unread,
Nov 1, 2017, 12:09:58 PM11/1/17
to google-ap...@googlegroups.com
Are they aliases that are missing or the main email address of the user? I don't think it matters either way but if there's a pattern like that it could be helpful for troubleshooting.

Andy Poirier

Sr. IT Engineer


W E D D I N G W I R E


301 231 9473

an...@weddingwire.com

website • facebook • instagram • linkedin



--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/26361ba7-8387-4eb5-98ff-6dc5864eb301%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Steve Martens

unread,
Nov 1, 2017, 12:13:04 PM11/1/17
to GAM for G Suite
They are not aliases, just the main email.  There are roughly 1600 email addresses on the CSV being added to 36 different groups.  Just two columns, Group and Email.  Seems pretty straightforward, but perhaps some get dropped?  There are potentially duplicate emails on the list as well, but I believe the first would be added and the second would fail and the process would continue, correct?

Steve


On Wednesday, November 1, 2017 at 10:09:58 AM UTC-6, Andy Poirier wrote:
Are they aliases that are missing or the main email address of the user? I don't think it matters either way but if there's a pattern like that it could be helpful for troubleshooting.

Andy Poirier

Sr. IT Engineer


W E D D I N G W I R E


301 231 9473

an...@weddingwire.com

website • facebook • instagram • linkedin



On Wed, Nov 1, 2017 at 11:50 AM, Steve Martens <s.ma...@gcaschool.com> wrote:
Hello,

I've been using GAM for a few years, but really only scratching the surface in how I use it, so please forgive my ignorance.

I'm using the command

gam csv file.csv gam update group ~Group add member ~Email

to add many email addresses to many groups.  The process seems to work quite well but  over time I'm discovering that some email addresses which are present in the CSV are missing in the groups.  I've check the Groups Reports to ensure the users did not remove themselves from the groups, no record of such.  It seems that some just didn't get added.

How can I verify why some are missing?  What could be causing them to not be added?

Thanks for your assistance,

Steve

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-ap...@googlegroups.com.

Ross Scroggs

unread,
Nov 1, 2017, 12:23:34 PM11/1/17
to google-ap...@googlegroups.com
Steve,

If you are on Gam 4.32, there should have been a status line for every add, what did they say?

Ross

On Wed, Nov 1, 2017 at 9:13 AM, Steve Martens <s.ma...@gcaschool.com> wrote:
They are not aliases, just the main email.  There are roughly 1600 email addresses on the CSV being added to 36 different groups.  Just two columns, Group and Email.  Seems pretty straightforward, but perhaps some get dropped?  There are potentially duplicate emails on the list as well, but I believe the first would be added and the second would fail and the process would continue, correct?

Steve

On Wednesday, November 1, 2017 at 10:09:58 AM UTC-6, Andy Poirier wrote:
Are they aliases that are missing or the main email address of the user? I don't think it matters either way but if there's a pattern like that it could be helpful for troubleshooting.

Andy Poirier

Sr. IT Engineer


W E D D I N G W I R E


301 231 9473

an...@weddingwire.com

website • facebook • instagram • linkedin



On Wed, Nov 1, 2017 at 11:50 AM, Steve Martens <s.ma...@gcaschool.com> wrote:
Hello,

I've been using GAM for a few years, but really only scratching the surface in how I use it, so please forgive my ignorance.

I'm using the command

gam csv file.csv gam update group ~Group add member ~Email

to add many email addresses to many groups.  The process seems to work quite well but  over time I'm discovering that some email addresses which are present in the CSV are missing in the groups.  I've check the Groups Reports to ensure the users did not remove themselves from the groups, no record of such.  It seems that some just didn't get added.

How can I verify why some are missing?  What could be causing them to not be added?

Thanks for your assistance,

Steve

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsubscribe...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/88d41dae-d168-4f17-95e2-07e2372b1a7c%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--

Steve Martens

unread,
Nov 1, 2017, 12:44:28 PM11/1/17
to GAM for G Suite
I no longer have the status info from the original group update, as it was months ago.  However, I ran the same command with a subset of the data using a test group to get results now.

Most of them just have "Added (1/1)", so those all work just fine.

For any duplicate addresses, one of them shows "Add Failed: 412: conditionNotMet - Condition not met (1/1)" and the other of the duplicate addresses appears several lines later with "Added (1/1)".  Seems like that's what should be expected.

There were 44 emails in this test, all to be added to the same group.  Three have duplicates and one failed because the address does not exist, so one should expect 40 addresses added successfully, but there are only 37 in the group.  Upon closer inspection it's the addresses with duplicates on the list.  Neither of the two was added for all three sets of duplicates.  This explains why there are only 37 on the list.

So quite simply, I can remove duplicates from the CSV before running this command.  But does it make sense?  Should it have worked?

Thanks!

Steve
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.



--

Ross Scroggs

unread,
Nov 1, 2017, 1:45:45 PM11/1/17
to google-ap...@googlegroups.com
Steve,

I can duplicate you situation.
The three duplicates are in the group but they have no role.
Gam can't see this, the API says that there are 37 members.
If you go to the admin console, it says the group has 40 members but then only shows 37.
But, if you click View in Groups service >>, then click Manage (not Members) at the upper right, you'll see the three no-role users.
You can try to delete them in the admin console but it doesn't seem to work.
Gam, however, can delete them.

Ross

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsubscribe...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsubscribe...@googlegroups.com.

To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.



--

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/cbe7c9e8-125e-4ebb-a833-47e9532b9e12%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--

Ross Scroggs

unread,
Nov 1, 2017, 2:00:29 PM11/1/17
to google-ap...@googlegroups.com
Steve,

Adding a member to a group seems to take a small amount of time; if requests to add the same member occur within that window of time, Google gets confused.
In my Advanced Gam (https://github.com/taers232c/GAMADV-X) you can do the following:
gam update group add member csvfile file.csv:Email
The duplicates are eliminated before the adds are performed.

Ross

To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.



--



--

Steve Martens

unread,
Nov 1, 2017, 2:23:43 PM11/1/17
to GAM for G Suite
Ross, thanks for all the help.  I believe you've correctly identified the source of the problem, that is, duplicate requests in the same time window.

I have reviewed all impacted groups in Groups Services and found none showing emails without roles.  I have also added all those addresses that were duplicates in my first run - this was successful.  This leads me to believe that those emails without roles fell off the groups at some point, or perhaps the problem manifested differently several months ago when I ran the original command.  

In either scenario, I'm quite grateful for a better understanding and your assistance in getting there.

I'll look at Advanced Gam - thanks for the recommendation.

Steve
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.

To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.



--

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.

To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.



--



--

Peter Smulders

unread,
Nov 1, 2017, 3:34:21 PM11/1/17
to GAM for G Suite
As an aside: (and Ross correct me if I'm wrong) adding members to groups one by one in this way (afaik gam CSV is really just a parameterised GAM batch) is slower than sorting them into files per group beforehand and letting GAM deal with whole files containing lists of users to add to or sync with group membership.

In the past, I have used the construct to take a mixed file (with some people purposely ending up in more than one group) and process it through some loops and logic, all the while adding to 'sorted buckets'. At the end of such a script, I loop through the list of files (one per group) and have 'GAM batch' deal with a few dozen 'GAM update group sync file (etc)' commands.

The upshot of such (pre)processing is not necessarily performance (although it takes maximum advantage of GAMs parallel processing and internal optimisations for batched operations) but the ability to inject some business knowledge into the process and dynamically deal with as little or as many groups as happen to be involved.

--peter

Peter Smulders

unread,
Nov 1, 2017, 3:41:22 PM11/1/17
to GAM for G Suite
For bonus points: removing duplicates in shell can be done in a myriad of ways. I use this to both sort and dedupe datasets in place, leaving the top line where it is (so I won't have to deal with piping from one file to the other and then deleting or moving files):

(echo '2,$sort u'; echo 'w') | vim -e FILE_WITH_POSSIBLE_DUPLICATE_LINES

--peter

Ross Scroggs

unread,
Nov 1, 2017, 8:26:15 PM11/1/17
to google-ap...@googlegroups.com

Peter,


I'll refer to Jay's version of GAM as GAM any my advanced version as GAMADV-X (https://github.com/taers232c/GAMADV-X).


Lets suppose you have a CSV file GroupUsers.csv:

Group,Email

gro...@domain.com,us...@domain.com

gro...@domain.com,us...@domain.com

gro...@domain.com,us...@domain.com

gro...@domain.com,us...@domain.com

gro...@domain.com,us...@domain.com

gro...@domain.com,us...@domain.com

gro...@domain.com,us...@domain.com

...


In both versions you can do the following:

gam csv GroupUsers.csv gam update group ~Group add member ~Email > GroupUsers.log 2>&1

A separate process is used to handle each Group/Email pair.

Both versions will make Google mad if you have duplicates.


In GAM and GAMADV-X, the output from the various processes is intermingled and can be very hard to interpret.


In GAMADV-X you can get clean output:

gam redirect stdout ./GroupUsers.log multiprocess redirect stderr stdout csv GroupUsers.csv gam update group ~Group add member ~Email

"redirect stdout ./GroupUsers.log multiprocess redirect stderr stdout" - tells GAMADV-X to intelligently handle the redirect for multiple processes.

You'll still make Google mad if you have duplicates.


GAMADV-X has a completely different approach that you can use that eliminates the duplicates problem.

gam redirect stdout ./GroupUsers.log redirect stderr stdout update group csvkmd GroupUsers.csv keyfield Group datafield Email add member csvdata Email

"redirect stdout ./GroupUsers.log redirect stderr stdout" - no multiprocess is specified as there are not multiple processes

"csvkmd GroupUsers.csv keyfield Group datafield Email" - at the place where the group name goes, we specify the CSV file, the field that is the key field,

Group in this case, and the the data field associated with the key field, Email in this case. GAMADV-X reads the CSV file, and combines all of the data fields

for each unique key field value; duplicates are stripped out.

"csvdata Email" - at the place where the member address goes, we specify the data field name. For each group, the datafield for that group is selected.


GAMADV-X steps through each group and adds the members; there is no parallelism but GAMADV-X adds the members in batches of 20 so it is quick.


Suppose you only want to update two groups, you can have GAMADV-X select specific rows/groups.

gam redirect stdout ./GroupUsers.log redirect stderr stdout update group csvkmd GroupUsers.csv keyfield Group matchfield Group gro...@domain.com matchfield Group gro...@domain.com datafield Email add member csvdata Email


Suppose you have a CSV file with multiple space separated email addresses per row:

Group,Email

gro...@domain.com,us...@domain.com us...@domain.com us...@domain.com us...@domain.com

gro...@domain.com,us...@domain.com us...@domain.com

gro...@domain.com,us...@domain.com

...


gam redirect stdout ./GroupUsers.log redirect stderr stdout update group csvkmd GroupUsers.csv keyfield Group datafield Email delimiter " " add member csvdata Email

delimiter " " - tells GAMADV-X that there are potentially multiple items in the data field and to split them up.


The examples above all show adding members, which, in some respects, is a special case. The more typical case would be sync members.

Suppose your student information system outputs a file nightly with parent email addresses; there is a group per grade and each student can have up to four parent email addresses.

The CSV file has one row per student per grade with one to four email addresses in separate columns.

If there are twins in a grade, there will be two identical rows.


Group,Email1,Email2,Email3,Email4

gra...@domain.com,pare...@domain.com,pare...@domain.com,pare...@domain.com,pare...@domain.com

gra...@domain.com,pare...@domain.com,pare...@domain.com,pare...@domain.com,pare...@domain.com

gra...@domain.com,pare...@domain.com,pare...@domain.com,,

gra...@domain.com,pare...@domain.com,pare...@domain.com,,

gra...@domain.com,pare...@domain.com,pare...@domain.com,,

gra...@domain.com,pare...@domain.com,,,

gra...@domain.com,pare...@domain.com,pare...@domain.com,,

...


gam redirect stdout ./GroupUsers.log redirect stderr stdout update group csvkmd GroupUsers.csv keyfield Group datafield Email1:Email2:Email3:Email4 add member csvdata Email1:Email2:Email3:Email4

"datafield Email1:Email2:Email3:Email4" - tells GAMADV-X that there are multiple data fields

"csvdata Email1:Email2:Email3:Email4" - the data field name(s)


GAMADV-X strips out the duplicates even if they come from separate rows, so grade6 only gets parent31 and parent32 once.


Ross



--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.

Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/337c1601-071f-41e1-af54-347e1a1a6b84%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--

Ross Scroggs

unread,
Nov 1, 2017, 10:33:12 PM11/1/17
to google-ap...@googlegroups.com
Peter,

You can also test csvkmd to verify that you'r getting the data you want.
GroupUsers.csv
Show unique keys
$ gam csvkmd users GroupUsers.csv keyfield Group datafield Email1:Email2:Email3:Email4 list 
Group

Show all unique Group/Email pairs
$ gam csvkmd users Groupusers.csv keyfield Group datafield Email1:Email2:Email3:Email4 list data csvdata Email1:Email2:Email3:Email4
Group,Email1:Email2:Email3:Email4

Show Groups with aggregated Email values

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for G Suite" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To post to this group, send email to google-ap...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages