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, "")