Home > Mobile >  How can I fetch Google Sheet name from Google sheet ID using python and google sheets API?
How can I fetch Google Sheet name from Google sheet ID using python and google sheets API?

Time:02-11

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)

Reference:

  • Related