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')
CodePudding user response:
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)