def __init__(self, creds, docid, worksheet_name): self.docid = docid self.worksheet_name = worksheet_name print ("Logging into Google.") scope = ['https://spreadsheets.google.com/feeds'] credentials = ServiceAccountCredentials.from_json_keyfile_name(creds, scope) client = gspread.authorize(credentials) sh = client.open_by_key(self.docid) self.worksheet = sh.worksheet(self.worksheet_name)
def rowsearch(self, query, worksheet): #Searches for a cell and returns the row number client = self.credentials() sheet = client.open_by_key(worksheet).sheet1 try: cell = sheet.find(query) row = cell.row return row except gspread.exceptions.CellNotFound: return None
def colsearch(self, query, worksheet): #Searches for a cell and returns the col number client = self.credentials() sheet = client.open_by_key(worksheet).sheet1 try: cell = sheet.find(query) col = cell.col return col except gspread.exceptions.CellNotFound: return None
def main(): """Shows basic usage of the Sheets API. Creates a Sheets API service object and prints the names and majors of students in a sample spreadsheet: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit """ _creds = get_credentials() client = gspread.authorize(_creds) sheet_key = "1mP7X88b8xlxOPXfmOvACkpnMfnEYNZwfsAQ6Ki_0ftE" sheet = client.open_by_key(sheet_key).sheet1 sheet_range = sheet.range(2, 1, 140, 14) for cell in sheet_range: print(f"{cell.row}:{cell.col}")
def postGsp(ls): import os import json import gspread import promise from oauth2client.service_account import ServiceAccountCredentials scope = '' json_file = "" credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file, scope) doc_id = '' client = gspread.authorize(credentials) file = client.open_by_key(doc_id) wsheet = file.get_worksheet(0) wsheet.insert_row(ls)
def grab_vehs_salon(request): # Setup the Drive v2 API SCOPES = 'https://www.googleapis.com/auth/drive.metadata.readonly' store = file.Storage(credentials_path) creds = store.get() if not creds or creds.invalid: flow = client.flow_from_clientsecrets(client_secret_path, SCOPES) creds = tools.run_flow(flow, store) service = build('drive', 'v2', http=creds.authorize(Http())) # Подключаемся к google sheets scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( access_email, scope) client = gspread.authorize(creds) # Подключаемся к листу # try: sheet1 = client.open_by_key( '17V5kmDw1pTouSu_yiqW955OAQ9x968uH_NPwfjoj61A').worksheet('Main') # except: # return HttpResponse('Permission denied!') sheet_records = sheet1.get_all_records() for record in sheet_records: # Проверяем доступно ли авто # if str(record['Марка авто']) == '': # continue # if str(record['Статус']) != '' and str(record['Статус']) != 'Готова': # continue # if(str(record['Бронювання']) != '' and str(record['Бронювання']) != 'Акція'): # continue # Получаем ссылку на ресурс с фото # hplink = sheet1.find(str(record['VIN'])) # hplink = sheet1.acell('D%s'%str(hplink.row),'FORMULA').value # hplink_dir = hplink.replace('=HYPERLINK("','') # veh_folder = hplink_dir.replace('";"%s")'%str(record['VIN']),'') # veh_folder = str(record['Фото']) # Формируем запись в таблице new_vehicle = Vehicle( veh_title='{} {} {}'.format(str(record['Марка авто']), str(record['Комплектация']), str(record['Год'])), veh_comp=str(record['Комплектация']), veh_vin=str(record['VIN']), veh_year=str(record['Год']), veh_mileage=str(record['Пробег']).replace('\xa0', ''), veh_color_in=str(record['Салон']), veh_color=str(record['Кузов']), veh_price=str(record['Цена ($)']).replace('\xa0', ''), veh_folder=str(record['Фото']), veh_photo='', veh_battery=str(record['Батарея']), veh_info='', veh_type='salon', veh_status=0, ) try: new_vehicle.save() except: print('Could not save %s record. Check if there is one already' % str(record['VIN'])) continue print('Finished with %s' % str(record['VIN'])) del (new_vehicle) return HttpResponse("Машины загружены. Нужно выставить фото")
def grab_vehs_kiev(request): # Setup the Drive v2 API SCOPES = 'https://www.googleapis.com/auth/drive.metadata.readonly' store = file.Storage(credentials_path) creds = store.get() if not creds or creds.invalid: flow = client.flow_from_clientsecrets(client_secret_path, SCOPES) creds = tools.run_flow(flow, store) service = build('drive', 'v2', http=creds.authorize(Http())) # Подключаемся к google sheets scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( client_json_path, scope) client = gspread.authorize(creds) # Подключаемся к листу # try: sheet1 = client.open_by_key( '1nzwOMZIHoHFhU-VneJUWMPFjI03b-lXVPoUP9QzF1io').worksheet('Наявність') # except: # return HttpResponse('Permission denied!') sheet_records = sheet1.get_all_records() for record in sheet_records: # Проверяем доступно ли авто if str(record['Марка авто']) == '': continue if str(record['Статус']) != '' and str(record['Статус']) != 'Готова': continue if (str(record['Бронювання']) != '' and str(record['Бронювання']) != 'Акція'): continue # Получаем ссылку на ресурс с фото hplink = sheet1.find(str(record['VIN'])) hplink = sheet1.acell('D%s' % str(hplink.row), 'FORMULA').value hplink_dir = hplink.replace('=HYPERLINK("', '') veh_folder = hplink_dir.replace('";"%s")' % str(record['VIN']), '') # Формируем запись в таблице new_vehicle = Vehicle( veh_title='{} {} {}'.format(str(record['Марка авто']), str(record['Комплектація']), str(record['Мод рік'])), veh_comp=str(record['Комплектація']), veh_vin=str(record['VIN']), veh_year=str(record['Мод рік']), veh_mileage=str(record['пробіг(км)']).replace('\xa0', ''), veh_color_in=str(record['салон']), veh_color=str(record['салон']), veh_price=str(record['Ціна в салоні']).replace('\xa0', ''), veh_folder=veh_folder, veh_photo='', veh_battery=str(record['SOH']), veh_info='', veh_type='dealler', veh_status=0, ) try: new_vehicle.save() except: print('Could not save %s record. Check if there is one already' % str(record['VIN'])) continue print('Finished with %s' % str(record['VIN'])) del (new_vehicle) return HttpResponse("<h1>Vehicles has been grabbed successfully</h1>")
chrome_driver_directory = parent_directory + '/chromedriver' download_directory = os.getcwd() + '/Downloads/' chromeOptions = webdriver.ChromeOptions() prefs = {"download.default_directory" : download_directory} chromeOptions.add_experimental_option("prefs",prefs) driver = webdriver.Chrome(executable_path= chrome_driver_directory, options=chromeOptions) driver.get('https://accounts.google.com/servicelogin') ROOT = tk.Tk() ROOT.withdraw() user_input = tk.messagebox.askquestion("Completed Login", "Now log in, and indicate once you are done. \n Have you completed login?", icon='warning', type='yesnocancel') ROOT.destroy() output_sheet = client.open_by_key('1rKpEu60UiuIpHsPqx3Dq36b_uPzCVnzfOJ5BJ6CqgEA').worksheet('Additions') for cms_id in ['spjVqY2uSt1Ka9RqntE15g', 'gRKGrdXpuJU_NvauhuOD9A','j-kzZLfTxkpSeiLsPVbDMg','TbRYh8_8m5bKt22ytII2Kw','bGKc_BVk-gmsgBM8xcFzag']: asset_id_array = [] expiration_date_array = [] issue_type_array = [] next_button = 'Placeholder' issues_overview_url = "https://studio.youtube.com/owner/" + cms_id + "/issues?o=" + cms_id + "&filter=%5B%7B%22isPinned%22%3Atrue%2C%22name%22%3A%22STATUS%22%2C%22value%22%3A%5B%22ACTION_REQUIRED%22%5D%7D%5D&sort=%7B%22columnType%22%3A%22viewsLifetime%22%2C%22sortOrder%22%3A%22DESCENDING%22%7D" driver.get(issues_overview_url) while next_button != None: time.sleep(5) driver.implicitly_wait(10) firstLevelMenu = driver.find_elements_by_id("asset-link") for asset_link in firstLevelMenu: asset_id_array.append(asset_link.get_attribute('href')[-16:])
# Очищаем таблицы sql = "TRUNCATE vehicle_main" db_query_set(sql) sql = "TRUNCATE vehicle_available" db_query_set(sql) sql = "TRUNCATE vehicle_tesla" db_query_set(sql) sql = "TRUNCATE vehicle_comming" db_query_set(sql) sql = "TRUNCATE vehicle_photos" db_query_set(sql) # VEHICLE AVAILABLE # Получаем записи из листа Наличия sheet1 = client.open_by_key( '1nzwOMZIHoHFhU-VneJUWMPFjI03b-lXVPoUP9QzF1io').worksheet('Наявність') sheet_records = sheet1.get_all_records() print(type(sheet_records)) for record in sheet_records: # Словарь данных о машине vehicle = dict() # Проверяем доступна ли машина для продажи(Акция, и отстутствие брони) if (str(record['Марка авто'])) == '': break if (str(record['Бронювання']) != '' and str(record['Бронювання']) != 'Акція'): continue vehicle['veh_title'] = record['Марка авто'] vehicle['veh_comp'] = record['Комплектація'] # veh_type if ('*' in str(record['Комплектація'])):
def fnFindCellValue(self, strSheetID, strSheetName, range, strData): credentials = self.get_credentials() client = gspread.authorize(credentials) sht1 = client.open_by_key(strSheetID) worksheet = sht1.worksheet(strSheetName) worksheet.findall(strData)
def fnGetCellValue(self, strSheetID, strSheetName, range): credentials = self.get_credentials() client = gspread.authorize(credentials) sht1 = client.open_by_key(strSheetID) worksheet = sht1.worksheet(strSheetName) return worksheet.acell(range).value
def fnGetRowCount(self, strSheetID, strSheetName): credentials = self.get_credentials() client = gspread.authorize(credentials) sht1 = client.open_by_key(strSheetID) worksheet = sht1.worksheet(strSheetName) return worksheet.row_count