· 4 years ago · Jul 05, 2021, 10:26 PM
1import smartsheet
2import os
3from datetime import datetime
4import logging
5
6# Look up API Token for smart sheets
7access_token = os.getenv("token")
8
9options = {
10 "MLS": {
11 "Folder": 3953194737919876, # folder id
12 "Whitelist": 3347722745997188, # report id
13 "Kickout": 7814503766222724 # sheet id
14 },
15 "SUM": {
16 "Folder": 3725832591042436,
17 "Whitelist" : 3735163155834756,
18 "Kickout": 7127107135399812
19 },
20 "DIG": {
21 "Folder": 1945666894227332,
22 "Whitelist":6866091235403652,
23 "Kickout": 1861198057629572
24 },
25 "Sandbox":{
26 "Folder": 4707492463699844,
27 "Whitelist": 6837232276400004,
28 "Kickout": 4840530971518852
29 },
30 "NEXT": {
31 "Folder": 5809683479979908,
32 "Whitelist": 6620931549357956,
33 "Kickout": 380344068269956
34 }
35}
36
37def get_transactions(sheetid):
38 tracker = smartsheet_client.Sheets.get_sheet(sheetid)
39 response = smartsheet_client.Workspaces.list_folders(tracker.workspace.id, include_all=True)
40
41 for item in response.data:
42 if item.name == "Archives":
43 folder_id = item.id
44 break
45
46 try:
47 archive_folders = smartsheet_client.Folders.get_folder(folder_id)
48 for logs in archive_folders.sheets:
49 if logs.name == "2021 Transactions":
50 return logs.id
51 except:
52 return"Log not found."
53
54# Initialize client
55smartsheet_client = smartsheet.Smartsheet(access_token)
56# Make sure we don't miss any errors
57smartsheet_client.errors_as_exceptions(True)
58# Log all calls
59logging.basicConfig(filename='rwsheet.log', level=logging.INFO)
60
61# Prompt user for imput
62company = input("ORG: ")
63month = input("Two-digit month: ")
64
65# Creates error log
66now = datetime.now()
67date_time = now.strftime("%m%d%Y")
68file_name = company + " Errors " + date_time + ".txt"
69f = open(file_name,"a")
70
71#folder id based on user input of MLS, SUM, DIG, or NEXT
72folder_id = options[company]["Folder"]
73#looks through the company folder
74folder = smartsheet_client.Folders.get_folder(folder_id)
75#gets the sheet id of the upload month
76sheet_name = company + " " + month
77
78#finds the upload sheet for the organization
79for sheet in folder.sheets:
80 if sheet.name == sheet_name:
81 upload_sheet_id = sheet.id
82 upload_sheet = smartsheet_client.Sheets.get_sheet(upload_sheet_id)
83 break
84
85#opens whitelist report
86whitelist_report = smartsheet_client.Reports.get_report(options[company]["Whitelist"], page_size=5000, include="sourceSheets")
87
88whitelist_values = {}
89upload_columns_to_ids = {} # The pretty column titles to their virtual row ids
90upload_rows = []
91kickout_rows = []
92match_rows = []
93
94# Identify gl column id in the whitelist_report
95for column in whitelist_report.columns:
96 if column.primary:
97 gl_column_id = column.virtual_id
98 break
99
100# Populate the whitelist_values dictionary
101for row in whitelist_report.rows:
102 gl_code = None
103 for cell in row.cells:
104 if cell.virtual_column_id == gl_column_id:
105 gl_code = cell.value
106 break
107
108 whitelist_values[gl_code] = {"Source": row.sheet_id, "Rows": [None]}
109
110# Create a quick lookup of upload column title to column id
111for column in upload_sheet.columns:
112 upload_columns_to_ids[column.title] = column.id
113
114for row in upload_sheet.rows:
115 upload_rows.append(row.id)
116 # Since we're iterating over the rows anyway, add them to the
117 # whitelist map
118 for cell in row.cells:
119 # If the current cell is a gl code and it's whitelisted
120 if cell.id == upload_columns_to_ids["GL Codes"] and whitelist_values.has_key(cell.value):
121 whilist_rows = whitelist_values[cell.value]["Rows"]
122 whilist_rows.append(row.id)
123 match_rows.append(row.id)
124 break
125
126print("Matched Row Count: " + str(len(match_rows)))
127
128difference = set(upload_rows).symmetric_difference(set(match_rows))
129kickout_rows = list(difference)
130
131print("Kickout Row Count: " + str(len(kickout_rows)))
132
133print("Total Row Count: " + str(len(upload_rows)))
134
135print(whitelist_values)
136
137# only runs the script if the total row count matches
138if len(match_rows)+len(kickout_rows)==len(upload_rows):
139 for key, whitelist_value in whitelist_values.items():
140 source = whitelist_value["Source"]
141 logs = get_transactions(whitelist_value["Source"])
142
143 if source == options[company]["Kickout"]:
144 smartsheet_client.Sheets.copy_rows(upload_sheet_id,
145 smartsheet.models.CopyOrMoveRowDirective({
146 'row_ids': whitelist_value["Rows"],
147 'to': smartsheet.models.CopyOrMoveRowDestination({
148 'sheet_id': options[company]["Kickout"]
149 })
150 })
151 )
152 elif type(logs) == str:
153 if whitelist_value["Rows"]:
154 smartsheet_client.Sheets.copy_rows(upload_sheet_id,
155 smartsheet.models.CopyOrMoveRowDirective({
156 'row_ids': whitelist_value["Rows"],
157 'to': smartsheet.models.CopyOrMoveRowDestination({
158 'sheet_id': options[company]["Kickout"]
159 })
160 })
161 )
162 elif whitelist_value["Rows"]:
163 smartsheet_client.Sheets.copy_rows(upload_sheet_id,
164 smartsheet.models.CopyOrMoveRowDirective({
165 'row_ids': whitelist_value["Rows"],
166 'to': smartsheet.models.CopyOrMoveRowDestination({
167 'sheet_id': get_transactions(whitelist_value["Source"])
168 })
169 })
170 )
171 else:
172 f.write("\nLog not found for " + str(key))
173
174
175 smartsheet_client.Sheets.copy_rows(upload_sheet_id,
176 smartsheet.models.CopyOrMoveRowDirective({
177 'row_ids': kickout_rows,
178 'to': smartsheet.models.CopyOrMoveRowDestination({
179 'sheet_id': options[company]["Kickout"]
180 })
181 })
182 )
183
184 f.close()
185 print("Finished.")
186
187else:
188 f.close()
189 print("Row Mismatched")