def mainFunction(arrayOfArguments): pathBelowRepos = pathToThisPythonFile spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj( True, pathBelowRepos, googleAccountUsername=arrayOfArguments[2]).open(arrayOfArguments[1]) with open(arrayOfArguments[3], 'r') as filehandle: invoicesArray = json.load(filehandle) clearAndResizeParameters = [ { 'sheetObj': spreadsheetLevelObj.worksheet('Invoices'), 'resizeRows': 2, 'startingRowIndexToClear': 0, 'resizeColumns': 1 }, ] myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters) myGspreadFunc.displayArray(spreadsheetLevelObj.worksheet('Invoices'), invoicesArray) myGspreadFunc.setFiltersOnSpreadsheet(spreadsheetLevelObj) myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
def mainFunction(arrayOfArguments): def getArrayOfPDFFiles(pathToRoot, foldersToExclude): # p(foldersToExclude) def ifPDFFile(fileObj): # p(fileObj) if fileObj.is_file() and fileObj.suffix == '.pdf': return fileObj return False # folderPathsToExclude = [] # for folderToExclude in foldersToExclude: # folderPathsToExclude.append(Path(pathToRoot, folderToExclude)) return myPyFunc.getArrayOfFileObjInTreeBreadthFirst(Path(pathToRoot), ifPDFFile, pathsToExclude=foldersToExclude) googleAccountUsername = arrayOfArguments[1] googleSpreadsheetTitle = arrayOfArguments[2] googleSheetTitleToSaveListTo = arrayOfArguments[3] pathToRootToBeginSearching = arrayOfArguments[4] arrayOfFoldersToExclude = arrayOfArguments[5:] arrayOfFoldersToExclude = [e.replace('\\\\', '\\') for e in arrayOfFoldersToExclude] pathBelowRepos = pathToThisPythonFile spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(True, pathBelowRepos, googleAccountUsername=googleAccountUsername).open(googleSpreadsheetTitle) arrayOfPDFFilesFromDisk = getArrayOfPDFFiles(pathToRootToBeginSearching, arrayOfFoldersToExclude) arrayOfPDFFilesFromDisk = [[str(e)] for e in arrayOfPDFFilesFromDisk] # arrayUploadToGoogleSheets = [] # for filePath in arrayOfPDFFilesFromDisk: # arrayUploadToGoogleSheets.append([filePath, '']) arrayOfPDFFilesFromDisk.insert(0, ['File Path']) # p(arrayOfPDFFilesFromDisk) clearAndResizeParameters = [ { 'sheetObj': spreadsheetLevelObj.worksheet(googleSheetTitleToSaveListTo), 'resizeRows': 2, 'startingRowIndexToClear': 0, 'resizeColumns': 1 }, ] myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters) myGspreadFunc.displayArray(spreadsheetLevelObj.worksheet(googleSheetTitleToSaveListTo), arrayOfPDFFilesFromDisk)
def mainFunction(arrayOfArguments): pathBelowRepos = pathToThisPythonFile spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj(True, pathBelowRepos, googleAccountUsername=arrayOfArguments[2]).open(arrayOfArguments[1]) invoicesArray = spreadsheetLevelObj.worksheet('Invoices').get_all_values() with open(arrayOfArguments[3], 'w') as filehandle: json.dump(invoicesArray, filehandle)
def mainFunction(arrayOfArguments): pyautogui.PAUSE = 0.01 sendingKeystrokes = True # gspObj = gspread.service_account(filename=myPyFunc.addToPath(pathToRepos, arrayOfPartsToAddToPath)) # p(arrayOfArguments) spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj( True, arrayOfArguments[3], googleAccountUsername=arrayOfArguments[1], pathStrToJSONCredentialsFile=arrayOfArguments[4]).open( 'Transactions To Post') sheetName = arrayOfArguments[2] toPostFromArray = spreadsheetLevelObj.worksheet(sheetName).get_all_values() # charactersNeedingShift = (list(range(123, 127)) + list(range(94, 96)) + list(range(62, 91)) + [60, 58] + list(range(40, 44)) + list(range(33, 39))) # p(charactersNeedingShift) columnNameToIndexObj = {} for columnIndexNumber, columnName in enumerate(toPostFromArray[0]): columnNameToIndexObj[columnName] = columnIndexNumber columnNameToNumberOfTabsObj = {} if sendingKeystrokes: with pynput.mouse.Listener( on_click=myPyFunc.functionOnClick) as listenerObj: print("Click on 'Clear' to begin posting...") listenerObj.join() if 'Bank Transactions' in sheetName: columnNameToNumberOfTabsObj['Checkbook ID'] = 2 columnNameToNumberOfTabsObj['Amount'] = 6 arrayOfKeysResetDropDown = ['down', 'up', 'up', 'up'] # p(columnNameToIndexObj) for row in toPostFromArray[1:]: #toPostFromArray[1728:1729]: # optionData = row[columnNameToIndexObj['Option']] if row[columnNameToIndexObj[ 'Status']] == '' and optionData != 'Enter/Edit': if optionData != 'Enter Transaction' or ( optionData == 'Enter Transaction' and row[columnNameToIndexObj['Type']] not in ['Check', 'Decrease Adjustment']): columnNameToNumberOfTabsObj['Account'] = 2 else: columnNameToNumberOfTabsObj['Account'] = 1 if myPyFunc.numLockIsOff(): pyautogui.press('numlock') # p(columnNameToNumberOfTabsObj) # p(columnNameToIndexObj) for columnIndexNumber, columnData in enumerate(row): for columnIndexNumberFromObj, columnNameFromObj in enumerate( columnNameToIndexObj): if columnIndexNumberFromObj == columnIndexNumber: currentColumnName = columnNameFromObj if currentColumnName not in [ 'Status', 'Person', 'Notes1', 'Notes2' ]: #< 9: # # p(currentColumnName) if currentColumnName in ['Option', 'Type']: pyautogui.press(arrayOfKeysResetDropDown) numberOfDownKeyPresses = 0 if columnData in ['Enter Receipt', 'Cash']: numberOfDownKeyPresses = 1 if columnData == 'Increase Adjustment': numberOfDownKeyPresses = 2 if columnData == 'Decrease Adjustment': numberOfDownKeyPresses = 3 myPyAutoGui.repetitiveKeyPress( numberOfDownKeyPresses, 'down') if currentColumnName == 'Transaction Date': # p(columnData) dateObj = datetime.datetime.strptime( columnData, '%m/%d/%Y') columnData = dateObj.strftime('%m%d%Y') if currentColumnName == 'Account': columnData = columnData.replace('-', '') if currentColumnName in ['Amount', 'Amount2']: columnData = columnData.lstrip('$').replace( '.', '').replace(',', '') if currentColumnName not in ['Option', 'Type']: myPyAutoGui.typeCharactersOnRemoteDesktop( columnData) if currentColumnName in columnNameToNumberOfTabsObj: myPyAutoGui.repetitiveKeyPress( columnNameToNumberOfTabsObj[currentColumnName], 'tab') else: myPyAutoGui.repetitiveKeyPress(1, 'tab') if not myPyFunc.numLockIsOff(): pyautogui.press('numlock') with pynput.mouse.Listener( on_click=myPyFunc.functionOnClick) as listenerObj: print( "Click on 'Post' or 'Clear' to continue with this entry..." ) listenerObj.join() elif sheetName == 'Bank Transfers': # p(columnNameToIndexObj) columnNameToNumberOfTabsObj[ columnNameToIndexObj['Transfer From Checkbook ID']] = 2 for row in toPostFromArray[1:]: if row[columnNameToIndexObj['Status']] == '': if myPyFunc.numLockIsOff(): pyautogui.press('numlock') myPyAutoGui.repetitiveKeyPress(2, "tab") rowToEnumerate = row[0:5] # p(rowToEnumerate) for columnIndexNumber, columnData in enumerate(rowToEnumerate): # p(columnData) if columnIndexNumber == columnNameToIndexObj[ 'Transfer Date']: dateObj = datetime.datetime.strptime( columnData, '%m/%d/%Y') columnData = dateObj.strftime('%m%d%Y') if columnIndexNumber == columnNameToIndexObj['Amount']: columnData = columnData.lstrip('$').replace( '.', '').replace(',', '') myPyAutoGui.typeCharactersOnRemoteDesktop(columnData) # for characterToType in columnData: # if ord(characterToType) in charactersNeedingShift: # priorPyAutoGuiPause = pyautogui.PAUSE # pyautogui.PAUSE = .000000000001 # pyautogui.keyDown('shift') # pyautogui.press(characterToType) # pyautogui.keyUp('shift') # pyautogui.PAUSE = priorPyAutoGuiPause # else: # pyautogui.press(characterToType) if columnIndexNumber in columnNameToNumberOfTabsObj: pass myPyAutoGui.repetitiveKeyPress( columnNameToNumberOfTabsObj[columnIndexNumber], 'tab') else: pass myPyAutoGui.repetitiveKeyPress(1, 'tab') if not myPyFunc.numLockIsOff(): pyautogui.press('numlock') with pynput.mouse.Listener( on_click=myPyFunc.functionOnClick) as listenerObj: print( "Click on 'Post' or 'Clear' to continue with this entry..." ) listenerObj.join()
def ocrPDFFiles(arrayOfArguments): pathBelowRepos = pathToThisPythonFile spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj( True, pathBelowRepos, googleAccountUsername=arrayOfArguments[1]).open(arrayOfArguments[2]) filesSheetName = arrayOfArguments[3] googleSheetsFileArray = spreadsheetLevelObj.worksheet( filesSheetName).get_all_values() filePathColIdx = 0 completedColIdx = 3 lastRowIndexToOCR = None for rowIndex, row in enumerate(googleSheetsFileArray): if row[completedColIdx] == '': lastRowIndexToOCR = rowIndex currentGroupCount = 0 currentGroupRowIndices = [] for rowIndex, row in enumerate(googleSheetsFileArray): if row[completedColIdx] == '': fileObjPath = Path(row[filePathColIdx]) addFileToAcrobatOCRList(fileObjPath) currentGroupCount = currentGroupCount + 1 currentGroupRowIndices.append(rowIndex) if currentGroupCount == int( arrayOfArguments[5]) or rowIndex == lastRowIndexToOCR: myPyAutoGui.clickWhenLocalPNGAppears( 'nextButtonBeginOCR', pathToThisPythonFile.parents[0]) myPyAutoGui.clickWhenLocalPNGAppears( 'closeActionCompleted', pathToThisPythonFile.parents[0]) myPyAutoGui.waitUntilLocalPNGDisappears( 'closeActionCompleted', pathToThisPythonFile.parents[0]) for currentGroupRowIndex in currentGroupRowIndices: googleSheetsFileArray[currentGroupRowIndex][ completedColIdx] = 'Yes - ' + str( datetime.datetime.now().strftime("%Y-%m-%d %H:%M")) clearAndResizeParameters = [ { 'sheetObj': spreadsheetLevelObj.worksheet(arrayOfArguments[4]), 'resizeRows': 2, 'startingRowIndexToClear': 0, 'resizeColumns': 1 }, ] p('Updating Google Sheets...') myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters) myGspreadFunc.displayArray( spreadsheetLevelObj.worksheet(arrayOfArguments[4]), googleSheetsFileArray) # myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj) p('Done updating Google Sheets.') currentGroupCount = 0 currentGroupRowIndices = [] pyautogui.press(['alt', 'f', 'w', 'down', 'down', 'enter'])
def mainFunction(arrayOfArguments): pathBelowRepos = pathToThisPythonFile spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj( True, pathBelowRepos, googleAccountUsername=arrayOfArguments[1]).open(arrayOfArguments[2]) arrayFilenameColIdx = 0 arrayCompletedStatusColIdx = 1 filesToReviewArray = spreadsheetLevelObj.worksheet( arrayOfArguments[3]).get_all_values() filesToReviewArrayFirstRow = filesToReviewArray.pop(0) filesCompletedArray = spreadsheetLevelObj.worksheet( arrayOfArguments[4]).get_all_values() filesCompletedArray = list( filter(lambda x: x[arrayCompletedStatusColIdx] != '', filesCompletedArray)) matchedArray = [] matchedArray.append(filesToReviewArrayFirstRow + [''] + filesCompletedArray[0]) # p(filesCompletedWithCompletedStatusArray) def filenameComparisonFunction(firstArrayCurrentRow, filesCompletedArrayCurrentRow): if firstArrayCurrentRow[ arrayFilenameColIdx] == filesCompletedArrayCurrentRow[ arrayFilenameColIdx]: return True return False def rowForMatchedArray(filesToReviewArrayCurrentRow): rowToReturn = filesToReviewArrayCurrentRow rowIndicesThatMatch = myPyFunc.rowIndicesInSecondFromTestsOnFirst( [filenameComparisonFunction], filesToReviewArrayCurrentRow, filesCompletedArray) if len(rowIndicesThatMatch) == 1: rowToReturn.extend([''] + filesCompletedArray.pop(rowIndicesThatMatch[0])) elif len(rowIndicesThatMatch) > 1: p('More than one row matches on the first pass') return rowToReturn myPyFunc.transferToArray(filesToReviewArray, matchedArray, rowForMatchedArray) clearAndResizeParameters = [{ 'sheetObj': spreadsheetLevelObj.worksheet(arrayOfArguments[5]), 'resizeRows': 2, 'startingRowIndexToClear': 0, 'resizeColumns': 1 }, { 'sheetObj': spreadsheetLevelObj.worksheet(arrayOfArguments[6]), 'resizeRows': 2, 'startingRowIndexToClear': 0, 'resizeColumns': 1 }] myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters) myGspreadFunc.displayArray( spreadsheetLevelObj.worksheet(arrayOfArguments[5]), matchedArray) myGspreadFunc.displayArray( spreadsheetLevelObj.worksheet(arrayOfArguments[6]), filesCompletedArray) myGspreadFunc.autoAlignColumnsInSpreadsheet(spreadsheetLevelObj)
def mainFunction(arrayOfArguments): # p(arrayOfArguments[3]) pathBelowRepos = pathToThisPythonFile spreadsheetLevelObj = myGspreadFunc.getSpreadsheetLevelObj( True, pathBelowRepos, googleAccountUsername=arrayOfArguments[1]).open(arrayOfArguments[2]) if arrayOfArguments[4] == 'From Google Sheets': invoicesArray = spreadsheetLevelObj.worksheet( 'Invoices').get_all_values() elif arrayOfArguments[4] == 'From Local File': with open(arrayOfArguments[5], 'r') as filehandle: invoicesArray = json.load(filehandle) invoicePulledColIdx = 0 typeColIdx = 1 journalEntryColIdx = 2 dateColIdx = 4 acctNumColIdx = 5 debitColIdx = 7 creditColIdx = 8 nameColIdx = 11 notesColIdx = 18 def paste(): pyautogui.keyDown('ctrl') pyautogui.press('v') pyautogui.keyUp('ctrl') def clearColumnFieldAndPaste(strToPaste): pyperclip.copy(strToPaste) pyautogui.press('backspace') # m.clickWhenLocalPNGAppears('emptyColumnName', parentDir) paste() if m.locateOnScreenLocal('searchWindow', parentDir): m.clickWhenLocalPNGAppears('closeGPWindow', parentDir) for row in invoicesArray: if row[invoicePulledColIdx] == '': m.clickWhenLocalPNGAppears('search', parentDir) m.clickWhenLocalPNGAppears('searchWindow', parentDir) if row[typeColIdx]: clearColumnFieldAndPaste('TRX Date') pyautogui.press(['tab'] * 3) datePartsArray = row[dateColIdx].split('/') dateStr = datePartsArray[0].zfill(2) + datePartsArray[1].zfill( 2) + datePartsArray[2] pyautogui.write(dateStr) else: clearColumnFieldAndPaste('Journal Entry') pyautogui.press(['tab'] * 2) pyautogui.write(row[journalEntryColIdx]) pyautogui.press('tab') pyautogui.press(['tab'] * 2) # exit() if row[typeColIdx]: if m.locateOnScreenLocal('accountNumberColumnName', parentDir): clearColumnFieldAndPaste('') pyautogui.press('tab') else: clearColumnFieldAndPaste('Account Number') pyautogui.press(['tab'] * 2) pyautogui.write(row[acctNumColIdx]) pyautogui.press('tab') pyautogui.press(['tab'] * 3) # if row[debitColIdx] != '228.94': # exit() pyautogui.write(row[debitColIdx]) pyautogui.press(['tab'] * 5) pyautogui.write(row[creditColIdx]) pyautogui.press('enter') m.waitUntilLocalPNGAppears('completedOneTransactionSmallScreen', parentDir) pyautogui.press(['tab'] * 15) while not m.locateOnScreenLocal('accountBlue', parentDir): p('Looking for accountBlue...') pydirectinput.press('down') while not m.locateOnScreenLocal('blue', parentDir): p('Looking for blue...') pydirectinput.press('down') m.doubleClickWhenLocalPNGAppears('blue', parentDir) m.clickWhenLocalPNGAppears('sourceDocument', parentDir) m.clickWhenLocalPNGAppears('imageButton', parentDir) while not m.locateOnScreenLocal('relatedDocumentsIcon', parentDir): m.clickWhenLocalPNGAppears('bistrackIcon', parentDir) m.clickWhenLocalPNGAppears('relatedDocumentsIcon', parentDir) m.clickWhenLocalPNGAppears('relatedDocumentsWindow', parentDir) while not m.locateOnScreenLocal('openInvoice', parentDir): pydirectinput.press('down') m.clickWhenLocalPNGAppears('openInvoice', parentDir) m.clickWhenLocalPNGAppears('print', parentDir) m.waitUntilLocalPNGAppears('selectPrinter', parentDir) pyautogui.press(['c', 'u', 'enter']) while not m.locateOnScreenLocal('cutePDFSaveAsIcon', parentDir): p('Looking for cutePDFSaveAs...') if m.getCoordinatesIfLocalPNGIsShowing( 'gpInvoiceWindowNotHighlighted', parentDir): m.clickWhenLocalPNGAppears('gpInvoiceWindowNotHighlighted', parentDir) # elif m.getCoordinatesIfLocalPNGIsShowing('cutePDFSaveAsIcon', parentDir): # m.clickWhenLocalPNGAppears('cutePDFSaveAsIcon', parentDir) while not m.locateOnScreenLocal('cutePDFSaveAs', parentDir): if m.getCoordinatesIfLocalPNGIsShowing('cutePDFSaveAsIcon', parentDir): m.clickWhenLocalPNGAppears('cutePDFSaveAsIcon', parentDir) m.clickWhenLocalPNGAppears('cutePDFSaveAs', parentDir) pyautogui.press(['tab'] * 5) #5) if row[typeColIdx]: pyperclip.copy( arrayOfArguments[3] + '\\' + row[typeColIdx].replace('\\', '').replace('&', '')) paste() else: pyperclip.copy(arrayOfArguments[3] + '\\' + row[journalEntryColIdx] + ' - ' + row[acctNumColIdx] + ' - ' + row[nameColIdx].replace('\\', '').replace( '&', '').replace('/', '') + ' - ' + row[notesColIdx]) paste() pyautogui.press('enter') m.clickLocalPNGWhenAppearsAndWaitUntilLocaPNGDisappears( 'closeGPInvoice', 'print', parentDir) while m.locateOnScreenLocal('closeRelatedDocuments', parentDir): m.clickWhenLocalPNGAppears('closeRelatedDocuments', parentDir) m.waitUntilLocalPNGDisappears('relatedDocumentsWindow', parentDir) m.clickWhenLocalPNGAppears('payablesEntry', parentDir) m.clickLocalPNGWhenAppearsAndWaitUntilLocaPNGDisappears( 'closeGPWindow', 'payablesEntryActive', parentDir) m.clickLocalPNGWhenAppearsAndWaitUntilLocaPNGDisappears( 'closeGPWindow', 'transactionEntry', parentDir) row[invoicePulledColIdx] = 'Yes' if arrayOfArguments[4] == 'From Google Sheets': clearAndResizeParameters = [ { 'sheetObj': spreadsheetLevelObj.worksheet('Invoices'), 'resizeRows': 2, 'startingRowIndexToClear': 0, 'resizeColumns': 1 }, ] myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters) myGspreadFunc.displayArray( spreadsheetLevelObj.worksheet('Invoices'), invoicesArray) # myGspreadFunc.setFiltersOnSpreadsheet(spreadsheetLevelObj) myGspreadFunc.autoAlignColumnsInSpreadsheet( spreadsheetLevelObj) elif arrayOfArguments[4] == 'From Local File': with open(arrayOfArguments[5], 'w') as filehandle: json.dump(invoicesArray, filehandle)