import os import openpyxl import pandas as pd import wget from gspread_pandas import Spread, Client import sys sheet_link = sys.argv[1] sheet_name = sys.argv[2] parameters_dict = {} parameters_list = [] file = wget.download(sheet_link) book = openpyxl.load_workbook(file) sheet = book[sheet_name] for i in range(2, sheet.max_row + 1): # to get rows parameters_dict = {} for j in range(1, sheet.max_column + 1): # to get columns parameters_dict[sheet.cell(row=1, column=j).value] = sheet.cell(row=i, column=j).value parameters_list.append(parameters_dict) os.remove(os.path.join(os.getcwd(), file)) lists = parameters_list for each_list in lists: spread = Spread(each_list["gsheet_workbook_name"]) data = pd.read_excel(each_list["excel_location"], each_list["excel_sheet_name"]) spread.clear_sheet(spread.get_sheet_dims(each_list["gsheet_sheet_name"])[0], spread.get_sheet_dims(each_list["gsheet_sheet_name"])[1], each_list["gsheet_sheet_name"]) spread.df_to_sheet(data, index=False, sheet=each_list["gsheet_sheet_name"], start='A1', replace=True)
class GoogleSpread(object): def __init__(self, spread, sheet=0, creds=None, create_sheet=False, conf_file=None): if creds: self.creds = creds else: credentials = ParseConfiguration(conf_file).get_google_creds() self.creds = get_creds(config=credentials) self.spread = Spread(spread, sheet=sheet, creds=self.creds, create_sheet=create_sheet) @property def spread_url(self): return self.spread.url @property def worksheet_name(self): return self.spread.sheet @property def spread_name(self): return self.spread.spread def df_to_sheet(self, df, index=True, headers=True, start_cell=(1, 1), sheet=None, replace=False): self.spread.df_to_sheet(df, index=index, headers=headers, start=start_cell, sheet=sheet, replace=replace) def sheet_to_df(self, index=1, header_rows=1, start_row=1, sheet=None): return self.spread.sheet_to_df(index=index, header_rows=header_rows, start_row=start_row, sheet=sheet) def find_sheet(self, sheet): return self.spread.find_sheet(sheet) def open_sheet(self, sheet, create=False): self.spread.open_sheet(sheet, create=create) def update_cells(self, start, end, vals, sheet=None): self.spread.update_cells(start, end, vals, sheet=sheet) def get_sheet_dims(self, sheet=None): return self.spread.get_sheet_dims(sheet=sheet) def clear_sheet(self, rows=1, cols=1, sheet=None): self.spread.clear_sheet(rows=rows, cols=cols, sheet=sheet) def create_sheet(self, name, rows=1, cols=1): self.spread.create_sheet(name, rows=rows, cols=cols) def delete_sheet(self, sheet): self.spread.delete_sheet(sheet)