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

VARSHINI P

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

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:

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How do I reference a sheet in google sheets by using name of the sheet name from a cell within the main sheet?

How to use sheet ID in Google Sheets API?

How to add a sheet in Google Sheets using API?

How can I get single sheet from a spreadsheet collection from Google Sheets with Google Sheets API v4?

How to get the value from another sheet with variable sheet name using Google Sheets?

How can I rearrange a Google Sheet using the data within the sheet?

Using Google Apps Script, how can I replace text in a Google Sheets template to make a new Sheet?

How to add a dropdown list to google sheet using Google Sheets API python

Delete data from a sheet in google sheets with API - using Postman

Google Sheets API - Python - Creating a Sheet

How to protect a sheet via Google Sheets API?

How can I filter multiple tabs/sheets in google sheet

Write to Sheet with Google Sheets API

How to fetch Specific row from google-sheet by using Google-sheet-apis(Node.js)?

How to get the value from another sheet with rows using Google Sheets?

A GET API (google sheets API) returns an excel sheet in windows 1252 encoding format. How can I download the sheet as excel using nodeJS?

How to get value from a sheet to another sheet with a criteria in Google Sheets?

How can I display the sheet contents in a PDF for a given range, from a shared google sheets document using URL parameters?

Publish Google Sheet document to the web using Google Sheets API

How can I create multiple csv files for different tables from the same sheet in google sheets

How do I get 5 rows from the end of my sheet in Google Sheets using Apps Script?

Looking up Google Sheets values using sheet id instead of sheet title (Google Drive Rest API V4)

How do I iterate over all the sheets in a google sheet using python 3?

Using Apps Script, how can I display a google doc checklist from information in a google sheet?

How to Copy data from One Google Sheet to another Sheet in the Same file Using Sheet Name as Criteria and find the sheet base on filter criteria

Using a cell's value in a function to fetch data from a different sheet - google sheets

How can I reset the color of a Google Sheet through the API

How can I enable "Data Labels" in a Google Sheet via the API?

Google Sheets Find Previously Created Sheet Using Name