store = file.Storage('credentials.json') creds = store.get() if not creds or creds.invalid: flow = client.flow_from_clientsecrets('client_secret_3.json', SCOPES) creds = tools.run_flow(flow, store) service = build('sheets', 'v4', http=creds.authorize(Http())) # Call the Sheets API SPREADSHEET_ID = '1vMFRfLKJV2hJv1KW2KgP52Ltv6-g8MzCL0sNyt9pApM' RANGE_NAME = 'TEST!A2:E2' result = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute() tasks_file = "tasks.csv" task_manager = TaskManager(tasks_file) tasks = task_manager.generate_task_fields() print(tasks) # dropdown_menu = DropdownMenu("names.csv") # dropdown_request = dropdown_menu.generate_request(0, 3, 4, 1, 5) # editors = ["*****@*****.**"] # lock_cells = LockCells(editors) # lock_cells_request = lock_cells.generate_request(0, 5, 6, 0, 5) # body = { # "requests": [dropdown_request, lock_cells_request] # } # response = service.spreadsheets() \ # .batchUpdate(spreadsheetId=SPREADSHEET_ID, body=body).execute() # print('{0} cells updated.'.format(len(response.get('replies'))))
class GSheetsRequest(object): """docstring for GSheetsRequest""" def __init__(self, editors, root_location, spreadsheetId): super(GSheetsRequest, self).__init__() self.editors = editors self.root_location = root_location self.service = None self.tasks = TaskManager(tasks) self.dropdown = DropdownMenu(names) self.lockcells = LockCells(self.editors) self.spreadsheetId = spreadsheetId self.write_body = {} self.request_body = {} def start(self): """ Initializes credentials with Google Sheets API. """ SCOPES = 'https://www.googleapis.com/auth/spreadsheets' store = file.Storage('credentials.json') creds = store.get() if not creds or creds.invalid: flow = client.flow_from_clientsecrets('client_secret.json', SCOPES) creds = tools.run_flow(flow, store) service = build('sheets', 'v4', http=creds.authorize(Http())) self.service = service return 1 # def new_page_test(self): # request = {"addSheet": { # "properties": { # "title": "New Week Test 4", # "gridProperties": { # "rowCount": len(self.tasks) + 4, # TODO Get rid of magic number # "columnCount": 10 # }, # "tabColor": { # "red": 1.0, # "green": 0.3, # "blue": 0.4 # } # } # }} # self.push(request) def new_week_template(self, week_number): # 1. Read in current month sheet print("getting current sheet") sheet_metadata = self.service.spreadsheets().get(spreadsheetId=self.spreadsheetId).execute() sheets = sheet_metadata.get('sheets', '') current_sheet = sheets[-1] current_sheet_name = current_sheet.get("properties", {}).get("title", -1) current_sheet_id = current_sheet.get("properties", {}).get("sheetId", -1) # 2. Find location for next write using week_number TASK_OFFSET = 4 new_location = (week_number - 1) * (len(self.tasks) + TASK_OFFSET) # print("clearing the sheet") # if week_number == 1: # # A. Clear the sheet. # clear_request = { # "updateCells": # { # "range": # { # "sheetId": current_sheet_id # }, # "fields": "userEnteredValue" # } # } # self.push(clear_request) # B. Lock the sheet. # lock_sheet_request = self.lockcells.generate_lock_sheet_request(current_sheet_id) # self.push(lock_sheet_request) print("generating new template") # 3. Generate new week body and write it there values, schedule_dict = self.tasks.generate_task_fields() write_request, lockdown_request = self.generate_write_request(values, current_sheet_name, current_sheet_id, new_location, schedule_dict) self.push_write(write_request) self.push(lockdown_request) print("pushed, waiting for result") def generate_write_request(self, values, sheet_name, sheet_id, location, schedule_dict): """ Generates write request and blacks out the correct cells according to the schedule dictionary. """ # 1. Generate requests for tasks and dates write_tasks_request = self.generate_task_write_request(values, sheet_name, location) write_dates_request = self.generate_dates_write_request(sheet_name, location, schedule_dict) blackout_request, lockdown_request = self.generate_blackout_request(sheet_id, location) request = [write_tasks_request, write_dates_request] return request, blackout_request def generate_task_write_request(self, values, sheet_name, location): # 1. Generate write request for tasks root = location + 2 write_tasks_range = "{0}!A{2}:A{1}".format(sheet_name, len(values) + root, root) major_tasks_dimension = "ROWS" write_tasks_request = { "range": write_tasks_range, "majorDimension": major_tasks_dimension, "values": values } return write_tasks_request # def generate_dates_write_request(self, sheet_name, location, schedule_dict): # """ Creates line of dates, assumed to be executed on a Sunday. """ # days_of_week = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"] # values = [] # today = datetime.datetime.today() # for i, day in enumerate(days_of_week): # new_date = today + datetime.timedelta(days=i + 1) # new_date = new_date.strftime("%m/%d") # values.append(["{0} ({1})".format(day, new_date)]) # write_dates_range = "{0}!B{1}:H{1}".format(sheet_name, location + 1) # major_dates_dimension = "COLUMNS" # write_dates_request = { # "range": write_dates_range, # "majorDimension": major_dates_dimension, # "values": values # } # return write_dates_request # def generate_blackout_request(self, sheet_id, location): # background_black = { # "userEnteredFormat": # { # "backgroundColor": # { # "red": 0, # "green": 0, # "blue": 0 # } # } # } # dropdown_menu = { # "dataValidation": self.dropdown.get_validation_rule() # } # DAYS_IN_WEEK = 7 # data = [] # ALPHABET = "abcdefghijklmnopqrstuvwxyz" # translate = {i: char for i, char in enumerate(ALPHABET)} # lockdown_requests = [] # for j, task in enumerate(self.tasks): # schedule = task.schedule # task_data = [] # for i in range(DAYS_IN_WEEK): # if i not in schedule: # # Make black and lock. # task_data.append(background_black) # a1_input_lock = "{0}{1}:{0}{1}".format(translate[i + 2], location + 1 + j) # grid_range_lock = self.convert_A1_to_GridRange(sheet_id, a1_input_lock) # lockdown_request = { # "addProtectedRange": # { # "protectedRange": # { # "range": grid_range_lock, # "warningOnly": False, # "editors": # { # "users": self.editors # } # } # } # } # lockdown_requests.append(lockdown_request) # else: # # Add dropdown menu. # task_data.append(dropdown_menu) # data.append(task_data) # requests = [] # for i, row in enumerate(data): # a1_input_color = "B{0}:H{0}".format(location + 1 + i) # grid_range_color = self.convert_A1_to_GridRange(sheet_id, a1_input_color) # request = { # "updateCells": # { # "rows": # { # "values": row # }, # "range": grid_range_color, # "fields": "userEnteredFormat.backgroundColor, dataValidation" # } # } # requests.append(request) # for lockdown_request in lockdown_requests: # requests.append(lockdown_request) # return requests, None # def convert_A1_to_GridRange(self, sheet_id, a1_input): # start, end = a1_input.lower().split(":") # ALPHABET = "abcdefghijklmnopqrstuvwxyz" # translate = {char: i for i, char in enumerate(ALPHABET)} # start_col = translate[start[0]] # start_row = int(start[1:]) # end_col = translate[end[0]] + 1 # end_row = int(end[1:]) + 1 # GridRange = { # "sheetId" : sheet_id, # "startRowIndex": start_row, # "endRowIndex": end_row, # "startColumnIndex": start_col, # "endColumnIndex": end_col # } # return GridRange def change_font_request(self, font): pass def push_write(self, request): if "valueInputOption" not in self.write_body: self.write_body["valueInputOption"] = "USER_ENTERED" self.write_body["data"] = [request] else: self.write_body["data"].append(request) return def push(self, request): if "requests" not in self.request_body: self.request_body["requests"] = [request] else: self.request_body["requests"].append(request) return # def test_dropdown_menu_debug(self): # sample_request = self.dropdown.generate_request(0, 3, 4, 1, 5) # # TODO: create Constants file with directed filenames for easy import of task names and contact information # self.push(sample_request) # def test_lock_debug(self): # sample_request = self.lockcells.generate_request(0, 0, 1, 0, 1) # self.push(sample_request) def full_send(self, request_only=False): response = self.service.spreadsheets() \ .batchUpdate(spreadsheetId=self.spreadsheetId, body=self.request_body).execute() if not request_only: response_w = self.service.spreadsheets() \ .values().batchUpdate(spreadsheetId=self.spreadsheetId, body=self.write_body).execute() # print('{0} cells updated.'.format(len(response.get('replies')))) # print('{0} cells updated.'.format(len(response.get('replies')))) self.request_body = {} self.write_body = {} return def full_send_writes(self): response = self.service.spreadsheets() \ .values().batchUpdate(spreadsheetId=self.spreadsheetId, body=self.write_body).execute() print('{0} cells updated.'.format(len(response.get('replies')))) self.write_body = {} return def view_last_task(self): if 'requests' not in self.body: return {} return self.body['requests'][-1] def view_last_write(self): if 'data' not in self.write_body: return {} return self.write_body