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)
