Anyway to tell gam to look at last row in a csv?

[Benjamin Wexler]

Is anybody aware of a way to tell gam to look at the last row of data in a google sheet. For new employees the data goes to a google sheet but I don't need all the data to be run, just the last row (and possibly any data after that.

[Kim Nilsson]

You could also add one more column, and include a check to see the value of that column.

NewEmployee

Yes

gam config csv_output_row_filter newemployee:regex:yes <the rest of your command here>

And after creating the user that Yes value is removed from the column, or replaced with a No.

[Rance Hall]

Benjamin:

 

I can’t recall a way to do this, but in your own story, you state that you don’t even want that.  Because you said yourself that you can’t guarantee you always want the last row.

 

Traditionally CSV tools either ignore the first row (or not optionally) or use the first row for field labels (GAM does this) but processes every row in the data set.

 

What you want is to test each row in the data set to see if you need to perform a task or not.

 

Then what you need to do is regularly purge the list of new employees, so that you don’t have to go through the “whole thing” each time.

 

If I’m not mistaken, Python proper has modules that can read data from a google sheet.  So, what I would do is write a small Python wrapper script that uses the data from the employee list Google sheet to test if a user has been created in Google, and if not create it.

 

Your Python script should just call GAM commands from the shell and use the output to test the result of a “get user info” or something like that to see if the user existed.

 

Consult GAM’s documentation to see what the query result would be if the user did not exist and then match against that.

 

I my judgement this is easier (for me at least) if I download the google sheet as a local csv file which greatly simplifies my wrapper script. 

 

Hope this helps

 

-- 

 

Rance Hall

Application Specialist

ESU 10

rance...@esu10.org

308-698-1919

 

 

Some days are better, some days are worse.

Look for the blessing instead of the curse.

 

 

 

 

 

 

From: google-ap...@googlegroups.com <google-ap...@googlegroups.com> on behalf of Benjamin Wexler <benjami...@gmail.com>
Date: Thursday, July 7, 2022 at 11:17 PM
To: GAM for Google Workspace <google-ap...@googlegroups.com>
Subject: [GAM] Anyway to tell gam to look at last row in a csv?

[EXTERNAL EMAIL]

Is anybody aware of a way to tell gam to look at the last row of data in a google sheet. For new employees the data goes to a google sheet but I don't need all the data to be run, just the last row (and possibly any data after that.

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/76c5a8e3-b20d-4114-819a-6ade46bd95adn%40googlegroups.com.

[Kim Nilsson]

GAMADV-XTD3 has built-in support for reading Google Sheets directly. I don't know if GAM does too.

https://github.com/taers232c/GAMADV-XTD3/wiki/Command-Data-From-Google-Docs-And-Sheets#read-data-from-a-google-sheet

So it can be referenced with csv. 

gam csv gsheet user SheetID sheetName gam update user ~userHeader gal off

[Ross Scroggs]

Benjamin,

I add a column Action to my spreadsheet and put an X in that column for any row I want to process.

gam csv_input_row_filter "Action:regex:X" csv filename.csv gam ...

After processing, I clear out the X's.

Ross

On Thu, Jul 7, 2022 at 9:17 PM Benjamin Wexler <benjami...@gmail.com> wrote:

Is anybody aware of a way to tell gam to look at the last row of data in a google sheet. For new employees the data goes to a google sheet but I don't need all the data to be run, just the last row (and possibly any data after that.

--

You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/76c5a8e3-b20d-4114-819a-6ade46bd95adn%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Kim Nilsson]

Ross,

Is it possible to let GAM clear the Xs? 

/Kim 

[Benjamin Wexler]

@ross - What do you mean by "column Action"?

For example, if I have a new employee form that has responses go to a spreadsheet. I'd want that sheet to automatically add an "x" in the new employee field that isn't on the form but is on the sheet and is dependent if the row has data.

Once the gam command runs to create that new user in google admin, as Kim says, then remove the "x" and therefore not run that row again with future rows of data that are added via the google form submission

[Benjamin Wexler]

Kim - I had meant GAMadv-xtd3, not GAM, my bad.

[Chris River]

I would do 3 sheets: "FormResponses", "ProcessedRows", "PendingProcessing":

• FormResponses contains the form responses (or whatever the original data is). This sheet should have a unique key to identify each row. If it's form-driven, you can likely just use the Timestamp column, as it is pretty unlikely 2 responses will come in at exactly the same time. If you'd like to use row number, this formula could be used: =ARRAYFORMULA(IF(ROW(A:A)=1, "Row Number", IF(NOT(ISBLANK(A:A)), ROW(A:A), IFERROR(1/0)))). Just insert that formula in the first row (the header row) of an empty column (except column A). It'll robustly handle row insertions anywhere below the first row, column insertions (if you insert a column left of A or move column A, it'll reference the new, moved, column location), and is agnostic as to what kind of data (text or numbers) are in column A, so the formula is very unlikely to break.
• ProcessedRows contains a list of unique keys to indicate which responses have already been processed. For example, a list of email addresses, or even just a list of row numbers. After GAM completes processing, you would have GAM insert a new row in this sheet with the key of the row that has been processed.
• PendingProcessing contains a filter formula that filters the FormResponses sheet to only include the rows that are not included in ProcessedRows. For example, if your unique key in FormResponses is located in column E (e.g. email addresses, or a formula that generates a unique key like row numbers), this formula might be =FILTER(FormResponses!A:D, ISNA(VLOOKUP(FormResponses!E:E, ProcessedRows!A:A, 1, 0))).

This setup ensures that PendingProcessing always only contains data that has not yet been processed, without having to worry about manual updates to the spreadsheet (and the issues that can come from that). You can even have the FormResponses sheet in a completely separate spreadsheet with permissions locked down, and do an IMPORTRANGE to import the data to your processing spreadsheet for improved robustness/security.