CODEWITHSUNDEEP
pythongoogle-sheets-apigspread
meshhat
meshhat

Reputation: 3

Multiple Spreadsheets with Gspread

I'm really new with Python, and I’m working with gspread and Google Sheets. I have several spreadsheets I would like to pull data from. They all have the same name with an appended numerical value (e.g., SpreadSheet(1), SpreadSheet(2), SpreadSheet(3), etc.)

I would like to parse through each spreadsheet, pull the data, and generate a single data frame with the data. I can do this quite easily with a single spreadsheet, but I’m having trouble doing it with several.

I can create a list of the spreadsheets titles with the code below, but I'm not sure if that's the right direction.

titles_list = []
for spreadsheet in client.openall():
    titles_list.append(spreadsheet.title)

Upvotes: 0

Views: 2254

Answers (2)

Aerials
Aerials

Reputation: 4419

Using a mix of both your starting code and @Tanaike's answer here you have a snippet of code that does what you expect.

# Create an authorized client
client = gspread.authorize(credentials)
# Create a list to hold the values
values = []
# Get all spreadsheets
for spreadsheet in client.openall():
    # Get spreadsheet's worksheets
    worksheets = spreadsheet.worksheets()
    for ws in worksheets:
        # Append the values of the worksheet to values
        values.extend(ws.get_all_values())
# create df from values
df = pd.DataFrame(values)
print(df)

Hope I was clear.

Upvotes: 1

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to merge the values retrieved from all sheets in a Google Spreadsheet.
  • You want to convert the retrieved values to the dataframe.
  • Each sheet has 4 columns, 100 rows and no header rows.
  • You want to achieve this using gspread with python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

For this, how about this answer?

Flow:

  1. Retrieve all sheets in the Google Spreadsheet using worksheets().
  2. Retrieve all values from all sheets using get_all_values() and merge the values.
  3. Convert the retrieved values to the dataframe.

Sample script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
worksheets = spreadsheet.worksheets()
values = []
for ws in worksheets:
    values.extend(ws.get_all_values())
df = pd.DataFrame(values)
print(df)

References:

Upvotes: 0

Related Questions