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
