1

I have a code which exports Google sheets into csv and stores it in my local storage. I want to store the csv file with the same name as the google sheet name. Can someone help me out with this ? So right now, this code saves the csv file as sheet1.csv , how can I make it have the original sheet name ?

import pandas as pd
from Google import Create_Service

def sheets_to_csv(GOOGLE_SHEET_ID):
    CLIENT_SECRET_FILE = 'secret.json'
    API_SERVICE_NAME = 'sheets'
    API_VERSION = 'v4'
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

    service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)

    try:
        gsheets = service.spreadsheets().get(spreadsheetId=GOOGLE_SHEET_ID).execute()
        sheets = gsheets['sheets']

        for sheet in sheets:
            dataset = service.spreadsheets().values().get(
                spreadsheetId=GOOGLE_SHEET_ID,
                range=sheet['properties']['title'],
                majorDimension='ROWS'
            ).execute()
            df = pd.DataFrame(dataset['values'])
            df.columns = df.iloc[0]
            df.drop(df.index[0], inplace=True)
            df.to_csv(sheet['properties']['title'] + '.csv', index=False)
            print()

    except Exception as e:
        print(e)

sheets_to_csv('1LqynjF33-mrO9M5INf4wJvJuY57Hhy4vjv_FjtuM')
Tanaike
  • 181,128
  • 11
  • 97
  • 165
VIOLET
  • 71
  • 1
  • 9
  • In your script, it seems that each sheet is exported as the CSV file. In this case, when the Spreadsheet title is used to the filename of the CSV file, and when there are several sheets in the Google Spreadsheet, the same filenames are existing. So I cannot understand your question. I apologize for my poor English skill. Can I ask you about the detail of your goal? – Tanaike Feb 11 '22 at 04:41
  • yes that's correct, each sheet is being exported as the CSV file. But i would like to fetch the name of the overall sheet and name the csv files as Name-sheet1.csv, Name-sheet2.csv and so on.. I hope I have explained my problem properly – VIOLET Feb 11 '22 at 04:51
  • Thank you for replying. From your replying, I understood your goal. So, I posted a modification point as an answer. Could you please confirm it? – Tanaike Feb 11 '22 at 05:04

1 Answers1

2

From your following reply,

each sheet is being exported as the CSV file. But i would like to fetch the name of the overall sheet and name the csv files as Name-sheet1.csv, Name-sheet2.csv and so on..

I understood your goal as follows.

  • You want to retrieve the Spreadsheet title and use it as the filename of CSV file like Name-sheet1.csv, Name-sheet2.csv.

In your script, how about the following modification?

From:

df.to_csv(sheet['properties']['title'] + '.csv', index=False)

To:

df.to_csv(gsheets['properties']['title'] + '-' + sheet['properties']['title'] + '.csv', index=False)

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165