"""
parse from course stats urls for all students files
"""
import json
import pandas as pd
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials


# Файл, полученный в Google Developer Console
CREDENTIALS_FILE = 'sheets/creds1.json'
# ID Google Sheets документа (можно взять из его URL)

# МИО-4
#spreadsheet_id = '1XLlJLyQP69i155tzMlMLoHZM_B83XgvTxQ6eazuFuA0'

#Сценарии-3
spreadsheet_id = '11ErqwwqrVdJLNHKgqEuc8rsdnb3jYY1nzCnDAJb38Ug'

# Авторизуемся и получаем service — экземпляр доступа к API
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    CREDENTIALS_FILE,
    ['https://www.googleapis.com/auth/spreadsheets',
     'https://www.googleapis.com/auth/drive'])

httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http = httpAuth)

# Пример чтения файла
spreadsheet = service.spreadsheets().get(
    spreadsheetId=spreadsheet_id,
    ).execute()

titles = []

for sheet in spreadsheet['sheets']:
    titles.append(sheet['properties']['title'])

values = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id,
    range=titles[0]+"!A1:Y75",
    #range=titles[1] + "!A1:YH75",
    majorDimension='ROWS'
    ).execute()

#for summary sheet
print(values['values'][4])
print(values['values'][5:][0])
columns = values['values'][4]
print(columns)

def extract_id(filename):
    start = len('https://docs.google.com/spreadsheets/d/')
    end = filename.find('/edit?usp=sharing')
    file_id = filename[start:end]
    return file_id

df = pd.DataFrame.from_records(values['values'][5:], columns=columns)
#df = pd.DataFrame.from_records(values['values'])
students = []
for index, row in df.iterrows():
    if row['1 урок']:
        student = {}
        student['name'] = row['Имя']
        student['family'] = row['Фамилия']
        student['email'] = row['Email']
        student['lesson_1'] = extract_id(row['1 урок'])
        student['lesson_2'] = extract_id(row['2 урок'])
        student['lesson_3'] = extract_id(row['3 урок'])
        student['lesson_4'] = extract_id(row['4 урок'])
        student['lesson_5'] = extract_id(row['5 урок'])
        student['lesson_6'] = extract_id(row['6 урок'])
        student['lesson_7'] = extract_id(row['7 урок'])
        student['lesson_8'] = extract_id(row['8 урок'])
        student['lesson_9'] = extract_id(row['9 урок'])
        student['lesson_0'] = extract_id(row['0'])
        students.append(student)

with open('sce3.json', 'w') as f:
    json.dump(students, f)

df = df.to_html()

4899ea1796a0ac0a46c6969b5510ef73e7ade09b
<mask token>


def extract_id(filename):
    start = len('https://docs.google.com/spreadsheets/d/')
    end = filename.find('/edit?usp=sharing')
    file_id = filename[start:end]
    return file_id


<mask token>

<mask token>
for sheet in spreadsheet['sheets']:
    titles.append(sheet['properties']['title'])
<mask token>
print(values['values'][4])
print(values['values'][5:][0])
<mask token>
print(columns)


def extract_id(filename):
    start = len('https://docs.google.com/spreadsheets/d/')
    end = filename.find('/edit?usp=sharing')
    file_id = filename[start:end]
    return file_id


<mask token>
for index, row in df.iterrows():
    if row['1 урок']:
        student = {}
        student['name'] = row['Имя']
        student['family'] = row['Фамилия']
        student['email'] = row['Email']
        student['lesson_1'] = extract_id(row['1 урок'])
        student['lesson_2'] = extract_id(row['2 урок'])
        student['lesson_3'] = extract_id(row['3 урок'])
        student['lesson_4'] = extract_id(row['4 урок'])
        student['lesson_5'] = extract_id(row['5 урок'])
        student['lesson_6'] = extract_id(row['6 урок'])
        student['lesson_7'] = extract_id(row['7 урок'])
        student['lesson_8'] = extract_id(row['8 урок'])
        student['lesson_9'] = extract_id(row['9 урок'])
        student['lesson_0'] = extract_id(row['0'])
        students.append(student)
with open('sce3.json', 'w') as f:
    json.dump(students, f)
<mask token>

<mask token>
CREDENTIALS_FILE = 'sheets/creds1.json'
spreadsheet_id = '11ErqwwqrVdJLNHKgqEuc8rsdnb3jYY1nzCnDAJb38Ug'
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE
    , ['https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'])
httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http=httpAuth)
spreadsheet = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute(
    )
titles = []
for sheet in spreadsheet['sheets']:
    titles.append(sheet['properties']['title'])
values = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id,
    range=titles[0] + '!A1:Y75', majorDimension='ROWS').execute()
print(values['values'][4])
print(values['values'][5:][0])
columns = values['values'][4]
print(columns)


def extract_id(filename):
    start = len('https://docs.google.com/spreadsheets/d/')
    end = filename.find('/edit?usp=sharing')
    file_id = filename[start:end]
    return file_id


df = pd.DataFrame.from_records(values['values'][5:], columns=columns)
students = []
for index, row in df.iterrows():
    if row['1 урок']:
        student = {}
        student['name'] = row['Имя']
        student['family'] = row['Фамилия']
        student['email'] = row['Email']
        student['lesson_1'] = extract_id(row['1 урок'])
        student['lesson_2'] = extract_id(row['2 урок'])
        student['lesson_3'] = extract_id(row['3 урок'])
        student['lesson_4'] = extract_id(row['4 урок'])
        student['lesson_5'] = extract_id(row['5 урок'])
        student['lesson_6'] = extract_id(row['6 урок'])
        student['lesson_7'] = extract_id(row['7 урок'])
        student['lesson_8'] = extract_id(row['8 урок'])
        student['lesson_9'] = extract_id(row['9 урок'])
        student['lesson_0'] = extract_id(row['0'])
        students.append(student)
with open('sce3.json', 'w') as f:
    json.dump(students, f)
df = df.to_html()

<mask token>
import json
import pandas as pd
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials
CREDENTIALS_FILE = 'sheets/creds1.json'
spreadsheet_id = '11ErqwwqrVdJLNHKgqEuc8rsdnb3jYY1nzCnDAJb38Ug'
credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE
    , ['https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'])
httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http=httpAuth)
spreadsheet = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute(
    )
titles = []
for sheet in spreadsheet['sheets']:
    titles.append(sheet['properties']['title'])
values = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id,
    range=titles[0] + '!A1:Y75', majorDimension='ROWS').execute()
print(values['values'][4])
print(values['values'][5:][0])
columns = values['values'][4]
print(columns)


def extract_id(filename):
    start = len('https://docs.google.com/spreadsheets/d/')
    end = filename.find('/edit?usp=sharing')
    file_id = filename[start:end]
    return file_id


df = pd.DataFrame.from_records(values['values'][5:], columns=columns)
students = []
for index, row in df.iterrows():
    if row['1 урок']:
        student = {}
        student['name'] = row['Имя']
        student['family'] = row['Фамилия']
        student['email'] = row['Email']
        student['lesson_1'] = extract_id(row['1 урок'])
        student['lesson_2'] = extract_id(row['2 урок'])
        student['lesson_3'] = extract_id(row['3 урок'])
        student['lesson_4'] = extract_id(row['4 урок'])
        student['lesson_5'] = extract_id(row['5 урок'])
        student['lesson_6'] = extract_id(row['6 урок'])
        student['lesson_7'] = extract_id(row['7 урок'])
        student['lesson_8'] = extract_id(row['8 урок'])
        student['lesson_9'] = extract_id(row['9 урок'])
        student['lesson_0'] = extract_id(row['0'])
        students.append(student)
with open('sce3.json', 'w') as f:
    json.dump(students, f)
df = df.to_html()

"""
parse from course stats urls for all students files
"""
import json
import pandas as pd
import httplib2
import apiclient.discovery
from oauth2client.service_account import ServiceAccountCredentials


# Файл, полученный в Google Developer Console
CREDENTIALS_FILE = 'sheets/creds1.json'
# ID Google Sheets документа (можно взять из его URL)

# МИО-4
#spreadsheet_id = '1XLlJLyQP69i155tzMlMLoHZM_B83XgvTxQ6eazuFuA0'

#Сценарии-3
spreadsheet_id = '11ErqwwqrVdJLNHKgqEuc8rsdnb3jYY1nzCnDAJb38Ug'

# Авторизуемся и получаем service — экземпляр доступа к API
credentials = ServiceAccountCredentials.from_json_keyfile_name(
    CREDENTIALS_FILE,
    ['https://www.googleapis.com/auth/spreadsheets',
     'https://www.googleapis.com/auth/drive'])

httpAuth = credentials.authorize(httplib2.Http())
service = apiclient.discovery.build('sheets', 'v4', http = httpAuth)

# Пример чтения файла
spreadsheet = service.spreadsheets().get(
    spreadsheetId=spreadsheet_id,
    ).execute()

titles = []

for sheet in spreadsheet['sheets']:
    titles.append(sheet['properties']['title'])

values = service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id,
    range=titles[0]+"!A1:Y75",
    #range=titles[1] + "!A1:YH75",
    majorDimension='ROWS'
    ).execute()

#for summary sheet
print(values['values'][4])
print(values['values'][5:][0])
columns = values['values'][4]
print(columns)

def extract_id(filename):
    start = len('https://docs.google.com/spreadsheets/d/')
    end = filename.find('/edit?usp=sharing')
    file_id = filename[start:end]
    return file_id

df = pd.DataFrame.from_records(values['values'][5:], columns=columns)
#df = pd.DataFrame.from_records(values['values'])
students = []
for index, row in df.iterrows():
    if row['1 урок']:
        student = {}
        student['name'] = row['Имя']
        student['family'] = row['Фамилия']
        student['email'] = row['Email']
        student['lesson_1'] = extract_id(row['1 урок'])
        student['lesson_2'] = extract_id(row['2 урок'])
        student['lesson_3'] = extract_id(row['3 урок'])
        student['lesson_4'] = extract_id(row['4 урок'])
        student['lesson_5'] = extract_id(row['5 урок'])
        student['lesson_6'] = extract_id(row['6 урок'])
        student['lesson_7'] = extract_id(row['7 урок'])
        student['lesson_8'] = extract_id(row['8 урок'])
        student['lesson_9'] = extract_id(row['9 урок'])
        student['lesson_0'] = extract_id(row['0'])
        students.append(student)

with open('sce3.json', 'w') as f:
    json.dump(students, f)

df = df.to_html()
