Download Google Spreadsheet and save as xls

Question

Am trying to write python procedure to download a spreadsheet from google spreedsheets and save it as .xls. here is my code

import os
import sys
from getpass import getpass

import gdata.docs.service
import gdata.spreadsheet.service



'''
    get user information from the command line argument and 
    pass it to the download method
'''
def get_gdoc_information():
    email ="mygmailaccount"
    password ="mypassword"
    gdoc_id = ['google_id1','googleid2','googleidn']
    for doc_id in gdoc_id:
        try:
            download(doc_id, email, password)
        except Exception, e:
            raise e

#python gdoc.py 1m5F5TXAQ1ayVbDmUCyzXbpMQSYrP429K1FZigfD3bvk#gid=0
def download(doc_id, email, password, download_path=None, ):
    print "Downloading the XLS file with id %s" % doc_id

    gd_client = gdata.docs.service.DocsService()


    #auth using ClientLogin
    gs_client = gdata.spreadsheet.service.SpreadsheetsService()
    gs_client.ClientLogin(email, password)

    #getting the key(resource id and tab id from the ID)

    resource    = doc_id.split('#')[0]
    tab         = doc_id.split('#')[1].split('=')[1]
    resource_id = 'spreadsheet:'+resource

    if download_path is None:
        download_path = os.path.abspath(os.path.dirname(__file__))

    file_name = os.path.join(download_path, '%s.xls' % (doc_id))

    print 'Downloading spreadsheet to %s...' % file_name

    docs_token = gd_client.GetClientLoginToken()
    gd_client.SetClientLoginToken(gs_client.GetClientLoginToken())
    gd_client.Export(resource_id, file_name, gid=tab)
    gd_client.SetClientLoginToken(docs_token)

    print "Download Completed!"


if __name__=='__main__':
    get_gdoc_information()

Whenever I try to run it, i get a gdata error below

gdata.service.RequestError: {'status': 401, 'body': '<HTML>\n<HEAD>\n<TITLE>Unauthorized</TITLE>\n</HEAD>\n<BODY BGCOLOR="#FFFFFF" TEXT="#000000">\n<H1>Unauthorized</H1>\n<H2>Error 401</H2>\n</BODY>\n</HTML>\n', 'reason': 'Unauthorized'}

Am using gdata library. I have been struggling with this whole day and cant seem to figure out what's happening. Can anyone please figure out and assit? Any other minimal script that can achieve my purpose as described above is will be much appreciated. Thank you

score 2 · Answer 1 · edited May 23 '17 at 11:47

(Feb 2017) Most answers (including the code in the OP) are now out-of-date as ClientLogin authentication was deprecated back in 2012(!), and GData APIs are the previous generation of Google APIs. While not all GData APIs have been deprecated, all newer Google APIs do not use the Google Data protocol, including the latest Google Sheets API (v4), which is much more powerful & flexible than older API releases.

However, note that the Sheets API is primarily for programmatically accessing spreadsheet operations & functionality (formatting cells, cell validation, resizing columns, creating charts, pivot tables, etc.), but to perform file-level access such as exporting to XLS(X), use the Google Drive API instead. Examples of using the Drive API:

Exporting a Google Sheet as CSV (blog post)
"Poor man's plain text to PDF" converter (blog post) (*)

(*) - TL;DR: upload plain text file to Drive, import/convert to Google Docs format, then export that Doc as PDF. Post above uses Drive API v2; this follow-up post describes migrating it to Drive API v3, and here's a developer video combining both "poor man's converter" posts.

The solution to the OP is to perform the same operation as what you see in the "Exporting a Google Sheet as CSV" post above but change the export MIMEtype from text/csv to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. For other import/export formats to/from Drive, see this related question SO answer as well as the downloading files from Drive docs page.

To learn more about how to use Google APIs with Python in general, check out my blog as well as a variety of Google developer videos (series 1 and series 2) I'm producing.

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

your error does suggest an login problem. Maybe you need to change your settings in your google account or try another way of a login.

Try looking here: SyntaxError using gdata-python-client to access Google Book Search Data API

or here: Download a spreadsheet from Google Docs using Python

I'm sorry to post this as an answer but I can't post comments yet.

Regards

edited May 23 '17 at 10:29

Community

1
1

answered Apr 05 '16 at 13:55

Tim Stopfer

116
8

score 0 · Answer 3 · answered Dec 11 '16 at 14:27

0

You could also try the library pygsheets.

import pygsheets

gc = pygsheets.authorize()

# Open spreadsheet and then workseet
sh = gc.open('my new ssheet')
wks = sh.sheet1

#export as csv
wks.export(pygsheets.ExportType.MS_Excel)

answered Dec 11 '16 at 14:27

Nithin

5,470
37
44

Download Google Spreadsheet and save as xls

3 Answers3