def getFromSheet():
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("ebayPractice-f9627f3e653b.json", scope)
client = gspread.authorize(creds)
input = client.open("OrderInformationsWork").worksheet("Input")
sellerIdFromSheet = input.cell(4,3).value.strip()
noOfMonths = int(input.cell(5,2).value)
return (sellerIdFromSheet,noOfMonths)
def updateToGSheet(data, error=None):
scope = [
"https://spreadsheets.google.com/feeds",
'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
"ebayPractice-f9627f3e653b.json", scope)
client = gspread.authorize(creds)
sheet1 = client.open("OrderInformationsWork").worksheet("aroundmountain")
eachRow1 = [
'Title', 'Price', 'Watch', 'Sold', 'CategoryID', 'Duration', 'Viewed',
'TimeLeft before Listing Ends', 'Last Updated at: ' +
str(datetime.datetime.now()) + ' by ' + getpass.getuser()
]
#heading
if (error is not None):
errors = [
'Failed to update sheet with reason : ' + str(error) + ' at ' +
str(datetime.datetime.now())
]
print("error with ", error)
print()
logger.debug(error)
sheet1.clear()
sheet1.append_row(errors)
return
allRowsValues = list()
allRowsValues.append(eachRow1)
for eachItem in data:
#print(eachItem)
eachRow = [
eachItem['Title'],
float(eachItem['SellingStatus']['CurrentPrice']['value']),
int(eachItem['WatchCount']),
int(eachItem['SellingStatus']['QuantitySold']),
int(eachItem['PrimaryCategory']['CategoryID']),
eachItem['ListingDuration'], eachItem['HitCount'],
eachItem['TimeLeft']
]
allRowsValues.append(eachRow)
#print("all rows are ",allRowsValues)
sheet1.clear()
sheet1.append_rows(allRowsValues)
sheet1.format(
"A1:H1", {
"textFormat": {
"bold": True,
"fontSize": 12,
"foregroundColor": {
"red": 1.0,
"green": 0.0,
"blue": 0.0
}
}
})
sheet1.format(
"K2:L1", {
"textFormat": {
"bold": True,
"fontSize": 12,
"foregroundColor": {
"red": 0.0,
"green": 1.0,
"blue": 0.0
}
}
})
"""uncomment this when testing locally"""
# load_dotenv()
HCTI_API_ENDPOINT = "https://hcti.io/v1/image"
HCTI_API_USER_ID = str(os.environ.get("HCTI_USER_ID"))
HCTI_API_KEY = str(os.environ.get("HCTI_API_KEY"))
GOOGLE_CREDS = os.environ.get('GOOGLE_APPLICATION_CREDENTIALS')
SHEET_NAME = str(os.environ.get("SHEET_NAME"))
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(GOOGLE_CREDS)
client = gspread.authorize(creds)
sheet = client.open(SHEET_NAME)
class DispBoatAllocCommand:
@staticmethod
def getWorksheetName(today: date) -> str:
"""The typical format of the worksheet name for boat allocation of the week is "mmm d1d1/m1m1 - d2d2/m2m2"
where d1 and m1 is the date of the start of the current week and d2 and m2 is the end of the current week
this function generates the worksheet name from the current date, so that we can retrieve the data for
today's boat
Returns:
str: the worksheet name where today's boat allocation is located
"""
month = today.strftime("%b")
startOfWeek = today - timedelta(days=today.weekday())
def updateToGSheet(data, error=None, sellerIdFromSheet="", noOfMonths="0"):
scope = [
"https://spreadsheets.google.com/feeds",
'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
"ebayPractice-f9627f3e653b.json", scope)
client = gspread.authorize(creds)
outputSheet = client.open("OrderInformationsWork").worksheet("Output")
allRowsValues = [[
'', '', '', 'Seller Details : ' + str(sellerIdFromSheet) + ' For ' +
str(noOfMonths) + ' Month(s)', '', '',
'Sheet Last Updated at: ' + str(datetime.datetime.now())
], [],
[
'Title', 'Price', 'Watch', 'Sold', 'CategoryID',
'Duration', 'Hit Count'
]]
if (error is not None):
errors = [
'Failed to update sheet with reason : ',
str(error), ' at ',
str(datetime.datetime.now())
]
print("error with ", error)
outputSheet.clear()
outputSheet.append_row(errors)
raise Exception(error)
return
#allRowsValues.append(eachRow1)
for eachItem in data:
#print(eachItem)
watchCont = 0 if eachItem['listingInfo'].get(
'watchCount') is None else int(
eachItem['listingInfo']['watchCount'])
QuantitySold = 0 if eachItem.get('QuantitySold') is None else int(
eachItem['QuantitySold'])
HitCount = 0 if eachItem.get('HitCount') is None else int(
eachItem['HitCount'])
eachRow = [
eachItem.get('title'),
float(eachItem['sellingStatus']['currentPrice']['value']),
watchCont, QuantitySold,
int(eachItem['primaryCategory']['categoryId']),
int(eachItem.get('DurationCalc')), HitCount
]
allRowsValues.append(eachRow)
outputSheet.clear()
outputSheet.append_rows(allRowsValues)
#For starting heading
outputSheet.format(
"A1:F1", {
"textFormat": {
"bold": True,
"fontSize": 12,
"foregroundColor": {
"red": 1.0,
"green": 0.0,
"blue": 0.0
}
}
})
#for each attribute
outputSheet.format(
"A3:I3", {
"textFormat": {
"bold": True,
"fontSize": 12,
"foregroundColor": {
"red": 1.0,
"green": 0.0,
"blue": 0.0
}
}
})
#to print timestamp at right side of sheet
outputSheet.format(
"G1:I1", {
"textFormat": {
"bold": False,
"fontSize": 12,
"foregroundColor": {
"red": 0.0,
"green": 1.0,
"blue": 0.0
}
}
}) #ok
# reset format
outputSheet.merge_cells('D1:F1')
outputSheet.merge_cells('G1:I1')
client.open("OrderInformationsWork").worksheet("Input").update_cell(
4, 2, "")