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