Script in spreadsheet to save sheet as csv and upload to cloud storage

Question

I have a script that everyday gets data from Google analytics and then I delete the sheet, so next day I can download it again.

What I need is to save that sheet as .csv and upload it to a cloud storage everyday, any ideas about how can I do that ?

Thanks a lot!

You could do that; but wouldn't it be simpler just to download it (or rename it) to a filename which includes the date, so that you don't have to delete it each time? — jpaugh, Feb 05 '18 at 19:30
Yes, I can, but I don't want to have sheets for everyday in the spreadsheet, I onlye need them in cloud storage. Anyway, do you know if there is any way to upload .csv or .tsv from spreadsheet script to cloud storage? Thanks for your time — Manuel Alejandro, Feb 06 '18 at 18:51
I've never used Google Cloud Storage, but I found out more by clicking on the [tag:google-cloud-storage] tag on your question. Their [documentation](https://cloud.google.com/storage/docs/) has a QuickStart guide that shows exactly how to upload a file, using the `gsutil` command line utility. (The type of file should not matter.) — jpaugh, Feb 06 '18 at 19:00

score 3 · Answer 1 · answered Feb 07 '18 at 11:26

To download your file as csv you can make use of this thread, which basically consists on using the following:

https://docs.google.com/spreadsheets/d/{DOCID}/gviz/tq?tqx=out:csv&sheet={sheet_name}

Replacing {DOCID} with your Document ID and {sheet_name} with the name of the sheet. If you only have one sheet, there is a more straightforward way:

https://docs.google.com/spreadsheets/d/{DOCID}/export?format=csv

As already mentioned by @jpaugh, to upload the file to cloud storage you can use the official docs, specifically here: Where it is explained how to upload an object to a bucket using any of the available options, which are:

Using the Console
Using gsutil command
Using any of the client libraries: C#,GO,Java,Node.js, PHP, Python or Ruby
Using any of the REST APIS (JSON or XML).

score 0 · Answer 2 · answered Aug 07 '21 at 16:15

Doesn't get any simpler than using Pandas:

def build_sheet_url(doc_id, sheet_id):
    return f'https://docs.google.com/spreadsheets/d/{doc_id}/export?format=csv&gid={sheet_id}'

def write_df_to_gs(df, gs_key):
    df.to_csv(gs_key)

doc_id = 'DOC_ID'
sheet_id = 'SHEET_ID'
sheet_url = build_sheet_url(doc_id, sheet_id)
df = pd.read_csv(sheet_url)
gs_key = 'GS_KEY'
write_df_to_gs(df, gs_key)

Script in spreadsheet to save sheet as csv and upload to cloud storage

2 Answers2