How do I export a Google sheet as a CSV in Python without using pandas?

user_1235929 0
Viewed 1333

I'm using Python 3.9 and the following version of Google Sheets ...

gsheets==0.5.1
gspread==3.6.0

I'm trying to export my Google sheet as a CSV file. In older versions of Python, I was using the Pandas module like so

    import gspread
    ...
    client = gspread.authorize(creds)
    sheet = client.open('My_Sheet_name')

    # get the third sheet of the Spreadsheet.  This
    # contains the data we want
    sheet_instance = sheet.get_worksheet(3)

    records_data = sheet_instance.get_all_records()

    records_df = pd.DataFrame.from_dict(records_data)

    # view the top records
    records_df.to_csv(sys.stdout)

How would I export the CSV without using Pandas? I ask because it would seem newer versions of Python (e.g. 3.9) do not support the pandas module yet.

gspread google-sheets csv python-3.x
2 Answers

I believe your goal as situation as follows.

  • You want to retrieve one of sheets in Google Spreadsheet as the CSV data.
  • You want to achieve this using gspread without using Pandas.
  • You have already been able to use gspread.

In this case, in order to achieve your goal, I would like to propose to use the endpoint for exporting the sheet as CSV data. The access token is retrieved from client of client = gspread.authorize(creds). When this proposal is reflected to your script, it becomes as follows.

Modified script:

client = gspread.authorize(creds)
sheet = client.open('My_Sheet_name')

# get the third sheet of the Spreadsheet.  This
# contains the data we want
sheet_instance = sheet.get_worksheet(2)  # Modified

# I added below script.
url = 'https://docs.google.com/spreadsheets/d/' + sheet.id + '/gviz/tq?tqx=out:csv&gid=' + str(sheet_instance.id)
headers = {'Authorization': 'Bearer ' + client.auth.token}
res = requests.get(url, headers=headers)
print(res.text)
  • In above script, please add import requests.
  • When above script is run, 3rd sheet is exported as the CSV data.

Note:

  • About sheet_instance = sheet.get_worksheet(3), your comment says get the third sheet of the Spreadsheet.. But the 1st number of get_worksheet is 0. So in this case, 4th sheet in the Spreadsheet is retrieved. Please be careful this.

  • In this case, I think that you can also use the endpoint as follows.

      url = 'https://docs.google.com/spreadsheets/d/' + sheet.id + '/export?format=csv&gid=' + str(sheet_instance.id)
user_71086530

You can use the DictWriter from the csv module to add each dictionary as a seperate line to the csv result:

import sys
from csv import DictWriter

dict_writer = DictWriter(sys.stdout, records_data[0].keys())
dict_writer.writeheader()
for data in records_data:
    dict_writer.writerow(data)

If you want to write the csv to a file instead of stdout, you can use this snippet instead:

from csv import DictWriter

with open('./path/to/the/file', 'w') as csvfile:
    dict_writer = DictWriter(csvfile, records_data[0].keys())
    dict_writer.writeheader()
    for data in records_data:
        dict_writer.writerow(data)

Example:

records_data contains the following values: [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}, {'a': 3, 'b': 4}]

Then the header is taken from the keys of an arbitrary element of the list (in this case the first one): a and b.

Then the values are added line by line to the csv:

a, b
1, 2
2, 3
3, 4
user_41137210
Related