Create one tabbed spreadsheet from many text files

[Johnnie W Adams]

Hi, folks,

     Is there a programmatic way to upload a set of text files into a spreadsheet, one tab per sheet?

Thanks,

     John A

[Kim Nilsson]

I'll test an idea.

/Kim

[Kim Nilsson]

I use GAMADV-XTD3 to do this. I just tried it, and it worked brilliantly for me.

You need to have an existing Sheet with as many sheets as you have text files, and know the names of those sheets. Can be Sheet1...SheetX.
Put the names of your local files in a csv, with a column for sheet names.

I don't know/think you can do this unless you know the names of the sheets you want to update. Meaning, you can't have gam automatically create new sheets for you, depending on how many files you have in your files.csv.

Ross, I would love to be wrong here. :-)

gam csv FileWithNamesOfLocalFilesAndColumnWithSheetNames gam  user UserName update drivefile YourSheetID localfile ~FileNames gsheet ~SheetNames

Add the option columndelimiter <Character> at the end if you have a known delimiter in the text files. If it is a comma, you probably don't have to add the option, unless you have a different delimiter set in gam.cfg.

Taken from here.
https://github.com/taers232c/GAMADV-XTD3/wiki/Users-Drive-Files-Manage#update-files

/Kim

--

There is No Substitute!

[Maj Marshall Giguere]

The short answer is "yes".  The long answer is that it's complicated.  I have a spreadsheet with two sheets (tabs) that needs to be updated each month.  You can create/update spreadsheets with gamadv readily see gamadv create spreadsheet or update spreadsheet.  The main difficulty I found is that if you want different sheets (tabs) you need to convert your csv file(s) to JSON and pass the JSON to gam.  I took a detour to make my life easier since only the contents of the sheets (tabs) change monthly.  First I created an empty spreadsheet, manually, and added the tabs I wanted.  Each month I generate a single csv file for each sheet (tab) and then update the spreadsheet on my drive.  Yes, there are other methods using Pandas, Excel modules..., but for my purposes I get a csv directly from my database and I just wanted to update it regularly.  I rolled the updater into a short bash shell script that pulls the query data and updates the spreadsheet one sheet (tab) at a time, added it to the crontab, bang, done.

Here's the update command for a single sheet (tab). Note you must know the ID of the target spreadsheet file.

$> gam user <spreadsheet owner id> update drivefile id <spreadsheet file id>  retainname localfile ./<local sheet csv file>.csv gsheet "<tab name>" 

You will need to break each sheet (tab) into a separate csv file.

I hope this is useful.

-Marsh

Maj Marshall E. Giguere, CAP

NH Wing Director of IT

U.S. Air Force Auxiliary

nhwg.cap.gov

GoCivilAirPatrol.com

 

--
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/CAGfWJCMKc3-gjaMB3_QhZKbr3X5KVvbCkwUxEVcQ4_Q73HDcJg%40mail.gmail.com.

[Kim Nilsson]

John,

Marsh added one smart option to the command, retainname. This way the Sheets file isn't renamed for each uploaded text file.

Then Ross called and said "give me a few minutes...", and now we can use the option addsheet when we don't want to pre-create the receiving sheets!

GAMADV-XTD3 6.04.12 will create new sheets for us. Just change gsheet to addsheet. 

... update drivefile id <spreadsheet file id>  retainname localfile ~FileNames addsheet ~sheetNames

Now, if you, like Marsh, need to do this regularly, and want to update the same sheets, in the same Sheets file, then you need to use gsheet, so gam knows exactly where to put the data. 

But there's no need to mess around with JSON, if your csv is a proper csv with a unique delimiter between your data points. Sheets and gam will cooperate and create a proper Sheets file for you.