delimiter change - drivefile download

[Matt Colwell]

I need to download a google sheet as a delimited text file.

So far, I've only been successful in getting the file as a CSV.  Is there a way to change the delimiter to a pipe character (or something else)? 

Or... Is there a way to download it as an Excel file?  It looks like there's an option for that, but I'm struggling with the syntax.

Thanks 

[Ross Scroggs]

Matt,

Show the command you're using.

Ross

--
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/2d4ffe63-774e-4072-8893-584499748f2cn%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Ross Scroggs]

Matt,

Try: gam user us...@domain.com get drivefile <FileID> format tsv

This gives you a tab separated file; you can change the tabs to |

Ross

--

Ross Scroggs

ross.s...@gmail.com

[Matt Colwell]

I've been using this:

gam user <USER> get drivefile  <FILEID>  csvsheet id:<SHEETID> targetname <LOCALFILE>

I just got the excel download working with this command:

gam user <USER> get drivefile  <FILEID>  format microsoft targetname <LOCALFILE>.xlsx 


If I grab the file in Excel I'll need to convert it into something I can use in a script, so that would be some extra steps.

[Matt Colwell]

I get this error:
Not Downloaded: Format (tsv) not available

Do I need to specify the sheet ID somehow?

[Ross Scroggs]

Matt,

Get 6.42.03, the command will now work.

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/4ab44b15-1f2a-4b86-b507-ed35b317bef7n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Ross Scroggs]

Matt,

Let's suppose your downloaded file in test.tsv, here's how to convert the tabs to |

Mac

tr '\t' "|" < test.tsv > testvb.tsv

Linux

sed -i 's/\t/|/g' test.tsv

Windows Powershell

(Get-Content test.tsv).Replace("`t", "|")

Ross

--

Ross Scroggs

ross.s...@gmail.com

[Matt Colwell]

Thanks Ross, the 6.42.03 version did the trick.

TSVs are usable as is, but I appreciate the code snippets for sed and powershell

[Roger Frutiger]

Ross,

I am having the same issue with GAM 6.53, when I try to download a sheet as a tsv I get the error: ERROR: Format (tsv) not available, but if I specify csv the file downloads correctly. The particular files I am working with have commas in the data so a simple replace of comma to tab wouldn't work. I think because the csv quotes the values with commas I can probably pipe it through Export-Csv and set the delimiter, but I would prefer to just get the file from Google in the correct format.

[Roger Frutiger]

Oh yeah the command I am using is as follows

gam user <file owner> get drivefile id "<drive file id>" format tsv

[Ross Scroggs]

It's a bug in Standard GAM, it works in Advanced GAM:

$ gam user tests...@rdschool.org get drivefile id 1uK-Ca4obeBiojvhWFfMH12lHz71GTwBmJGbL-KnY2tM format tsv

User: tests...@rdschool.org, Download 1 Drive File

  User: tests...@rdschool.org, Drive File: Activity Test, Downloaded to: /Users/admin/Documents/GamWork/Activity Test.tsv, Type: Google Doc

https://github.com/taers232c/GAMADV-XTD3/wiki/How-to-Upgrade-from-Standard-GAM

I'll look at a fix for standard GAM

Ross

----

Ross Scroggs

Ross.S...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/7087b6f2-0120-4a6b-b68a-279418f133ecn%40googlegroups.com.