1. Automating the Google Spreadsheet Tasks with Python and TSSFL Open Discussion Forums -- This topic explains the authentication process and how to work with Google Spreadsheets from TSSFL ODF.
2. Automate Multiple Excel Sheets and Produce Reports Using Python -- This topic is about combining multiple excel sheets and automating reports from the combined excel data.
In this topic, we present a Python snippet below that is able to do the following:
- Read data from several Google sheets/worksheets and create Pandas dataframe for each.
- Combine the dataframes created in the previous step.
- Clean data to remove unrequired values.
- Store the data from the combined dataframe into a new Google sheet -- Master Sheet.
- import gspread
- import urllib.request
- urllib.request.urlretrieve("https://www.dropbox.com/s/m728v370159b2xm/credentials.json?dl=1", "credentials.json")
- gc = gspread.service_account(filename="credentials.json")
- wb = gc.open_by_url("https://docs.google.com/spreadsheets/d/1O19ketp1DpZwUytV_WqAlxafOBfwiCHiR-0F5eTir6k/edit#gid=0") #Open spreadsheet,
- #print(wb.worksheets()) #Try wb.worksheets
- #print(wb.title)
- import pandas as pd
- list_of_dataframes = []
- for ws in wb.worksheets():
- if ws.title != "Master_Sheet":
- rows = ws.get_all_values()
- #Create data frames
- df = pd.DataFrame.from_records(rows[1:], columns=rows[0]) #Omit the first/header row, pass columns
- list_of_dataframes.append(df)
- #Print the first dataframe
- #print(list_of_dataframes[0])
- combined_dataframes = pd.concat(list_of_dataframes)
- print(combined_dataframes)
- #Do some cleaning, replace NAN with blank string
- clean_df = combined_dataframes.fillna("") #Try combined_dataframes.dropna("")
- #Let's create a list of lists for the dataframes including columns
- combined_Data = [clean_df.columns.tolist()] + clean_df.to_numpy().tolist()
- mastersheet = wb.worksheet("Master_Sheet")
- #Let's update the Master Sheet, start pasting from cell A1
- mastersheet.update("A1", combined_Data, value_input_option="USER_ENTERED")
- import os
- os.remove("credentials.json")
Here the data in Sheet1 and Sheet2 have been combined by column names to a data in the Master_Sheet:
See related approach that uses Google Colab and a different authentication process: