def reconcileArrays(oAuthMode,
googleSheetTitle,
firstArrayColumnsToMatch=None,
secondArrayColumnsToMatch=None,
googleAccountUsername=None):
pathToRepos = _myPyFunc.getPathUpFolderTree(pathToThisPythonFile, 'repos')
pathToThisProjectRoot = pathToThisPythonFile.parents[3]
gspObj = _myGspreadFunc.getSpreadsheetLevelObj(
oAuthMode,
pathToThisProjectRoot,
googleAccountUsername=googleAccountUsername)
gspSpreadsheet = gspObj.open(googleSheetTitle)
firstTableName = 'First Table'
secondTableName = 'Second Table'
matchedTableName = 'Matched'
didNotMatchTableName = 'Did Not Match'
gspFirstTable = gspSpreadsheet.worksheet(firstTableName)
gspSecondTable = gspSpreadsheet.worksheet(secondTableName)
gspMatchedTable = gspSpreadsheet.worksheet(matchedTableName)
gspDidNotMatchTable = gspSpreadsheet.worksheet(didNotMatchTableName)
firstArray = gspFirstTable.get_all_values()
secondArray = gspSecondTable.get_all_values()
firstArrayFirstRow = firstArray.pop(0)
secondArrayFirstRow = secondArray.pop(0)
matchedArray = [[firstTableName] + [''] * (len(firstArray[0])) +
[secondTableName] + [''] * (len(secondArray[0]) - 1)]
matchedArray.append(firstArrayFirstRow + [''] + secondArrayFirstRow)
# # p(matchedArray)
if not firstArrayColumnsToMatch:
firstArrayColumnsToMatch = [0]
if not secondArrayColumnsToMatch:
secondArrayColumnsToMatch = [0]
while firstArray:
firstArrayCurrentRow = firstArray.pop(0)
tempMatchedData = []
for secondArrayCurrentRowIndex in reversed(range(len(secondArray))):
if columnsMatch(firstArrayCurrentRow,
secondArray[secondArrayCurrentRowIndex],
firstArrayColumnsToMatch,
secondArrayColumnsToMatch):
secondArrayCurrentRow = secondArray.pop(
secondArrayCurrentRowIndex)
# if tempMatchedData:
# tempMatchedDataCurrentLength = len(tempMatchedData)
# tempMatchedData.append([str(tempMatchedData[0][firstArrayColumnsToMatch[0]]) + ': matched ' + str(tempMatchedDataCurrentLength) + ' additional row(s)'] + [''] * (len(firstArrayCurrentRow)) + secondArrayCurrentRow)
# else:
tempMatchedData.append(firstArrayCurrentRow + ['Matched'] +
secondArrayCurrentRow)
if tempMatchedData:
matchedArray.extend(tempMatchedData)
else:
matchedArray.append(firstArrayCurrentRow + ['Not Matched'])
clearAndResizeParameters = [{
'sheetObj': gspMatchedTable,
'resizeRows': 3,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}, {
'sheetObj': gspDidNotMatchTable,
'resizeRows': 2,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}]
_myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
_myGspreadFunc.displayArray(gspMatchedTable, matchedArray)
secondArray.insert(0, secondArrayFirstRow)
_myGspreadFunc.displayArray(gspDidNotMatchTable, secondArray)
_myGspreadFunc.autoResizeColumnsOnSheet(gspSpreadsheet, matchedTableName)
_myGspreadFunc.autoResizeColumnsOnSheet(gspSpreadsheet,
didNotMatchTableName)
def moveColumns(googleSheetTitle, googleAccountUsername=None):
pathToRepos = _myPyFunc.getPathUpFolderTree(pathToThisPythonFile, 'repos')
pathToThisProjectRoot = pathToThisPythonFile.parents[3]
oAuthMode = False
if googleAccountUsername:
oAuthMode = True
gspObj = _myGspreadFunc.getSpreadsheetLevelObj(
oAuthMode,
pathToThisProjectRoot,
googleAccountUsername=googleAccountUsername)
gspSpreadsheet = gspObj.open(googleSheetTitle)
sheetName = 'Scenarios'
gspScenarios = gspSpreadsheet.worksheet(sheetName)
scenarioArray = gspScenarios.get_all_values()
p(scenarioArray[1][1:])
def moveColumn(columnIndexToMove, destinationColumnIndex):
if columnIndexToMove > destinationColumnIndex:
destinationIndex = destinationColumnIndex
else:
destinationIndex = destinationColumnIndex + 1
requestObj = {
"requests": [
{
"moveDimension": {
"source": {
"sheetId":
gspSpreadsheet.worksheet(
sheetName)._properties['sheetId'],
"dimension":
"COLUMNS",
"startIndex":
columnIndexToMove,
"endIndex":
columnIndexToMove + 1
},
"destinationIndex": destinationIndex
}
},
# {
# "moveDimension": {
# "source": {
# "sheetId": sheetId,
# "dimension": "ROWS",
# "startIndex": 4,
# "endIndex": 10
# },
# "destinationIndex": 19
# }
# },
],
}
gspSpreadsheet.batch_update(requestObj)
def bubbleSort(arr):
indexingLength = len(arr) - 1
arrayIsSorted = False
while not arrayIsSorted:
arrayIsSorted = True
for currentIndex in range(0, indexingLength):
if arr[currentIndex] > arr[currentIndex + 1]:
arrayIsSorted = False
arr[currentIndex], arr[currentIndex +
1] = arr[currentIndex +
1], arr[currentIndex]
moveColumn(currentIndex + 1, currentIndex + 2)
return arr
p(bubbleSort(scenarioArray[1][1:]))
def mainFunction(arrayOfArguments):
pathToThisPythonFile = Path(__file__).resolve()
pathToPublicProjectsPython = _myPyFunc.getPathUpFolderTree(
pathToThisPythonFile, 'python')
pathToBatchScriptsFolder = Path(pathToPublicProjectsPython,
'customCommandLineApps', 'batchScripts',
'scripts')
def arrayOfSubFolders(pathToFolder):
arrayOfSubFolders = []
for fileObj in pathToFolder.iterdir():
if isNotFile(fileObj):
arrayOfSubFolders.append(fileObj)
return arrayOfSubFolders
for batchFileToTrash in os.listdir(pathToBatchScriptsFolder):
shutil.move(
Path(pathToBatchScriptsFolder, batchFileToTrash),
Path(pathToBatchScriptsFolder.parents[0], 'scriptsTrashed',
batchFileToTrash))
folderArray = [pathToPublicProjectsPython]
while folderArray:
currentFolder = folderArray.pop(0)
folderArray.extend(arrayOfSubFolders(currentFolder))
if currentFolder != 'scriptsForCustom':
for fileObj in currentFolder.iterdir():
if fileObj.is_file(
) and fileObj.suffix == '.py' and fileObj.stem[:1] != '_':
pathOfPythonFileToRun = ''
# p(fileObj.parts)
for partOfPathToFileObj in fileObj.parts[
len(pathToPublicProjectsPython.parts):]:
pathOfPythonFileToRun = pathOfPythonFileToRun + '/' + partOfPathToFileObj
# p(pathOfPythonFileToRun)
pathOfBatchFileToWriteTo = Path(pathToBatchScriptsFolder,
fileObj.stem + '.bat')
objOfBatchFileToWriteTo = open(pathOfBatchFileToWriteTo,
'w+')
objOfBatchFileToWriteTo.write(
'@echo off \npython %~dp0/../../..' +
pathOfPythonFileToRun + ' %*')
objOfBatchFileToWriteTo.close()
def reconcileArraysFunction(runningOnProductionServer, oAuthMode,
googleSheetTitle):
def decryptIntoSameFolder(pathToFolder, fileName, encryptionKey):
pathToDecryptedFile = Path(pathToFolder, 'decrypted' + fileName)
_myPyFunc.decryptFile(Path(pathToFolder, 'encrypted' + fileName),
encryptionKey,
pathToSaveDecryptedFile=pathToDecryptedFile)
return pathToDecryptedFile
def clearDecryptedFiles(decryptedFilesToClear):
for decryptedFileToClear in decryptedFilesToClear:
with open(decryptedFileToClear, "w") as fileObj:
fileObj.write('')
pathToThisPythonFile = Path(__file__).resolve()
pathToConfigData = Path(pathToThisPythonFile.parents[2], 'configData')
if runningOnProductionServer:
from ..myPythonLibrary import _myPyFunc
from ..googleSheets.myGoogleSheetsLibrary import _myGspreadFunc
loadedEncryptionKey = os.environ.get('savedEncryptionKeyStr', None)
if not runningOnProductionServer:
sys.path.append(str(pathToThisPythonFile.parents[1]))
from myPythonLibrary import _myPyFunc
from googleSheets.myGoogleSheetsLibrary import _myGspreadFunc
pathToRepos = _myPyFunc.getPathUpFolderTree(pathToThisPythonFile,
'repos')
pathToGoogleCredentials = Path(pathToRepos, 'privateData', 'python',
'googleCredentials')
if oAuthMode:
scopesArray = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
if runningOnProductionServer:
pathToDecryptedJSONCredentialsFile = decryptIntoSameFolder(
pathToConfigData, 'JSONCredentialsFile.json',
loadedEncryptionKey)
pathToDecryptedAuthorizedUserFile = decryptIntoSameFolder(
pathToConfigData, 'AuthorizedUserFile.json',
loadedEncryptionKey)
decryptedFilesToClear = [
pathToDecryptedJSONCredentialsFile,
pathToDecryptedAuthorizedUserFile
]
if not runningOnProductionServer:
pathToDecryptedJSONCredentialsFile = Path(
pathToGoogleCredentials, 'usingOAuthGspread',
'jsonCredentialsFile.json')
pathToDecryptedAuthorizedUserFile = Path(
pathToGoogleCredentials, 'usingOAuthGspread',
'authorizedUserFile.json')
credentialsObj = gspread.auth.load_credentials(
filename=pathToDecryptedAuthorizedUserFile)
if not credentialsObj:
flowObj = InstalledAppFlow.from_client_secrets_file(
pathToDecryptedJSONCredentialsFile, scopesArray)
credentialsObj = flowObj.run_local_server(port=0)
gspread.auth.store_credentials(
credentialsObj, filename=pathToDecryptedAuthorizedUserFile)
gspObj = gspread.client.Client(auth=credentialsObj)
if not oAuthMode:
if runningOnProductionServer:
pathToDecryptedAPIKey = decryptIntoSameFolder(
pathToConfigData, 'APIKey.json', loadedEncryptionKey)
decryptedFilesToClear = [pathToDecryptedAPIKey]
if not runningOnProductionServer:
pathToDecryptedAPIKey = Path(pathToGoogleCredentials,
'usingServiceAccount',
'jsonWithAPIKey.json')
gspObj = gspread.service_account(filename=pathToDecryptedAPIKey)
if runningOnProductionServer: clearDecryptedFiles(decryptedFilesToClear)
gspSpreadsheet = gspObj.open(googleSheetTitle)
gspFirstTableSheet = gspSpreadsheet.worksheet('firstTable')
gspSecondTableSheet = gspSpreadsheet.worksheet('secondTable')
gspComparisonTableSheet = gspSpreadsheet.worksheet('comparisonTable')
gspEndingSecondTableSheet = gspSpreadsheet.worksheet('endingSecondTable')
firstArray = gspFirstTableSheet.get_all_values()
secondArray = gspSecondTableSheet.get_all_values()
firstArrayFirstRow = firstArray.pop(0)
secondArrayFirstRow = secondArray.pop(0)
matchingColumnTitle = ''
for indexOfColumnIndexFirstArray, columnTitleFirstArray in enumerate(
firstArrayFirstRow):
for indexOfColumnIndexSecondArray, columnTitleSecondArray in enumerate(
secondArrayFirstRow):
if columnTitleFirstArray == columnTitleSecondArray:
firstArrayColumnIndexToCompare = indexOfColumnIndexFirstArray
secondArrayColumnIndexToCompare = indexOfColumnIndexSecondArray
comparisonArray = [['firstTable'] + [''] * (len(firstArray[0])) +
['secondTable'] + [''] * (len(secondArray[0]) - 1)]
comparisonArray.append(firstArrayFirstRow + [''] + secondArrayFirstRow)
# p(comparisonArray)
while firstArray:
firstArrayCurrentRow = firstArray.pop(0)
# p(firstArrayCurrentRow)
rowToAppend = firstArrayCurrentRow + ['']
for secondArrayRowIndexCount, secondArrayCurrentRow in enumerate(
secondArray):
# p(secondArrayCurrentRow)
if firstArrayCurrentRow[
firstArrayColumnIndexToCompare] == secondArrayCurrentRow[
secondArrayColumnIndexToCompare]:
secondArrayRowToAppend = secondArray.pop(
secondArrayRowIndexCount)
rowToAppend = rowToAppend + secondArrayRowToAppend
comparisonArray.append(rowToAppend)
# p(comparisonArray)
clearAndResizeParameters = [{
'sheetObj': gspComparisonTableSheet,
'resizeRows': 3,
'startingRowIndexToClear': 0
}, {
'sheetObj': gspEndingSecondTableSheet,
'resizeRows': 2,
'startingRowIndexToClear': 0
}]
_myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
_myGspreadFunc.updateCells(gspComparisonTableSheet, comparisonArray)
secondArray.insert(0, secondArrayFirstRow)
_myGspreadFunc.updateCells(gspEndingSecondTableSheet, secondArray)
strToReturn = os.environ.get('urlOfKingGorillaGoogleSheetPublicStr')
if not strToReturn:
pathToConfigDataJSON = Path(pathToRepos, 'privateData',
'herokuGorilla', 'configData.json')
jsonFileObj = open(pathToConfigDataJSON)
strToReturn = json.load(
jsonFileObj)['urlOfKingGorillaGoogleSheetPublicStr']
strToReturn = strToReturn[:-1] + '871892682'
return strToReturn
# for oauth
# Advanced Usage
# Custom Authentication
# Google Colaboratory
# If you familiar with the Jupyter Notebook, Google Colaboratory is probably the easiest way to get started using gspread:
# from google.colab import auth
# auth.authenticate_user()
# import gspread
# from oauth2client.client import GoogleCredentials
# gc = gspread.authorize(GoogleCredentials.get_application_default())
# def authorize(credentials, client_class=Client):
# """Login to Google API using OAuth2 credentials.
# This is a shortcut function which
# instantiates `client_class`.
# By default :class:`gspread.Client` is used.
# :returns: `client_class` instance.
# """
# client = client_class(auth=credentials)
# return client
#################################################################################################
# importing
# this works
# import reconcileArrays.hiPackage.hiModule
# reconcileArrays.hiPackage.hiModule.hiFunction()
# this works
# from reconcileArrays.hiPackage import hiModule
# hiModule.hiFunction()
# this works
# from .hiPackage import hiModule
# hiModule.hiFunction()
# this works
# from ..hiPackage import hiModule
# hiModule.hiFunction()
###################################################################################################
# # from __future__ import print_function
# import pickle
# import os.path
# from googleapiclient.discovery import build
# from google_auth_oauthlib.flow import InstalledAppFlow
# from google.auth.transport.requests import Request
# # If modifying these scopes, delete the file token.pickle.
# SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
# # The ID and range of a sample spreadsheet.
# SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
# SAMPLE_RANGE_NAME = 'Class Data!A2:E'
# """Shows basic usage of the Sheets API.
# Prints values from a sample spreadsheet.
# """
# creds = None
# # The file token.pickle stores the user's access and refresh tokens, and is
# # created automatically when the authorization flow completes for the first
# # time.
# if os.path.exists(pathToPickleCredentialsFile):
# with open(pathToPickleCredentialsFile, 'rb') as token:
# creds = pickle.load(token)
# # If there are no (valid) credentials available, let the user log in.
# if not creds or not creds.valid:
# if creds and creds.expired and creds.refresh_token:
# creds.refresh(Request())
# else:
# flow = InstalledAppFlow.from_client_secrets_file(
# pathToDecryptedJSONCredentialsFile, SCOPES)
# creds = flow.run_local_server(port=0)
# # Save the credentials for the next run
# with open(pathToPickleCredentialsFile, 'wb') as token:
# pickle.dump(creds, token)
# service = build('sheets', 'v4', credentials=creds)
# # Call the Sheets API
# sheet = service.spreadsheets()
# result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
# range=SAMPLE_RANGE_NAME).execute()
# values = result.get('values', [])
# if not values:
# print('No data found.')
# else:
# print('Name, Major:')
# for row in values:
# # Print columns A and E, which correspond to indices 0 and 4.
# print('%s, %s' % (row[0], row[4]))
###############################################################################################################
# pathToPickleCredentialsFile = Path(pathToOAuthCredentialsFolder, 'pickleFileWithCredentials.pickle')
# import pickle
# import os.path
# from googleapiclient.discovery import build
# from google_auth_oauthlib.flow import InstalledAppFlow
# from google.auth.transport.requests import Request
# # If modifying these scopes, delete the file token.pickle.
# SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
# # The ID and range of a sample spreadsheet.
# SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
# SAMPLE_RANGE_NAME = 'Class Data!A2:E'
# creds = None
# usePickleFile = False
# if usePickleFile:
# # The file token.pickle stores the user's access and refresh tokens, and is
# # created automatically when the authorization flow completes for the first
# # time.
# if os.path.exists(pathToPickleCredentialsFile):
# with open(pathToPickleCredentialsFile, 'rb') as token:
# creds = pickle.load(token)
# # If there are no (valid) credentials available, let the user log in.
# if not creds or not creds.valid:
# if usePickleFile and creds and creds.expired and creds.refresh_token:
# creds.refresh(Request())
# else:
# flowObj = InstalledAppFlow.from_client_secrets_file(pathToDecryptedJSONCredentialsFile, SCOPES)
# creds = flowObj.run_local_server(port=0)
# p(flowObj)
# if usePickleFile:
# # Save the credentials for the next run
# with open(pathToPickleCredentialsFile, 'wb') as token:
# pickle.dump(creds, token)
# service = build('sheets', 'v4', credentials=creds)
# sheet = service.spreadsheets()
# result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME).execute()
# values = result.get('values', [])
# if not values:
# print('No data found.')
# else:
# print(values)
def looperFunction(oAuthMode, googleSheetTitle):
pathToRepos = _myPyFunc.getPathUpFolderTree(pathToThisPythonFile, 'repos')
pathToThisProjectRoot = pathToThisPythonFile.parents[3]
gspObj = _myGspreadFunc.getSpreadsheetLevelObj(oAuthMode,
pathToThisProjectRoot)
gspSpreadsheet = gspObj.open(googleSheetTitle)
gspLoopTable = gspSpreadsheet.worksheet('loopTable')
gspCalculationTable = gspSpreadsheet.worksheet('calculationTable')
gspResultTable = gspSpreadsheet.worksheet('resultTable')
clearAndResizeParameters = [{
'sheetObj': gspResultTable,
'startingRowIndexToClear': 0,
'resizeColumns': 1
}]
_myGspreadFunc.clearAndResizeSheets(clearAndResizeParameters)
loopTableArray = gspLoopTable.get_all_values()
if oAuthMode:
pass
else:
for rowIndex, rowData in enumerate(loopTableArray):
if rowIndex > 0:
_myGspreadFunc.updateCell(gspLoopTable, rowIndex, 1,
random.randint(1, 101))
_myGspreadFunc.updateCell(gspLoopTable, rowIndex, 2,
random.randint(1, 101))
loopTableArray = gspLoopTable.get_all_values()
calculationTableArray = gspCalculationTable.get_all_values()
resultTableArray = [[
loopTableArray[0][0], calculationTableArray[0][2]
]]
for rowIndex, rowData in enumerate(loopTableArray):
if rowIndex > 0:
_myGspreadFunc.updateCell(gspCalculationTable, 1, 0,
rowData[1])
_myGspreadFunc.updateCell(gspCalculationTable, 1, 1,
rowData[2])
calculationTableArray = gspCalculationTable.get_all_values()
resultTableArray.append(
[loopTableArray[rowIndex][0], calculationTableArray[1][2]])
_myGspreadFunc.displayArray(gspResultTable, resultTableArray)
strToReturn = os.environ.get('urlOfKingGorillaGoogleSheetPublicStr')
if not strToReturn:
pathToConfigDataJSON = Path(pathToRepos, 'privateData',
'herokuGorilla', 'configData.json')
jsonFileObj = open(pathToConfigDataJSON)
strToReturn = json.load(
jsonFileObj)['urlOfKingGorillaGoogleSheetPublicStr']
strToReturn = strToReturn[:-1] + '871892682'
return strToReturn
def getGoogleSheetsAPIObj(arrayOfPathParts=None,
pathToCredentialsDirectory=None,
pathToCredentialsFileServiceAccount=None):
from pathlib import Path
pathToThisPythonFile = Path(__file__).resolve()
import sys
sys.path.append(
str(Path(pathToThisPythonFile.parents[2], 'myPythonLibrary')))
import _myPyFunc
import pickle, googleapiclient.discovery, google_auth_oauthlib.flow, google.auth.transport.requests
from pprint import pprint as pp
googleSheetsAPIScopes = ["https://www.googleapis.com/auth/spreadsheets"]
credentialsObj = None
if pathToCredentialsFileServiceAccount:
import google.oauth2
credentialsObj = google.oauth2.service_account.Credentials.from_service_account_file(
pathToCredentialsFileServiceAccount)
return googleapiclient.discovery.build(
"sheets", "v4", credentials=credentialsObj).spreadsheets()
else:
if pathToCredentialsDirectory:
pathToJSONForCredentialsRetrieval = Path(
pathToCredentialsDirectory, 'jsonForCredentialsRetrieval.json')
pathToPickleFileWithCredentials = Path(
pathToCredentialsDirectory, 'pickleFileWithCredentials.pickle')
else:
pathToRepos = _myPyFunc.getPathUpFolderTree(
pathToThisPythonFile, 'repos')
pathToJSONForCredentialsRetrieval = _myPyFunc.addToPath(
pathToRepos,
arrayOfPathParts + ['jsonForCredentialsRetrieval.json'])
pathToPickleFileWithCredentials = _myPyFunc.addToPath(
pathToRepos,
arrayOfPathParts + ['pickleFileWithCredentials.pickle'])
#if the pickle is file is available from persistent memory, then get the credentials object from it
#otherwise, check if the credentials object can be refreshed and do that
#if the credentials object can't be refreshed, then get them
if Path.exists(pathToPickleFileWithCredentials):
with open(pathToPickleFileWithCredentials, "rb") as pickleFileObj:
credentialsObj = pickle.load(pickleFileObj)
else:
if not credentialsObj or not credentialsObj.valid:
if credentialsObj and credentialsObj.expired and credentialsObj.refresh_token:
credentialsObj.refresh(
google.auth.transport.requests.Request())
else:
flowObj = google_auth_oauthlib.flow.InstalledAppFlow.from_client_secrets_file(
pathToJSONForCredentialsRetrieval,
googleSheetsAPIScopes)
credentialsObj = flowObj.run_local_server(port=0)
#save the credentials in persistent memeory
with open(pathToPickleFileWithCredentials,
"wb") as pickleFileObj:
pickle.dump(credentialsObj, pickleFileObj)
return googleapiclient.discovery.build(
"sheets", "v4", credentials=credentialsObj).spreadsheets()