pip install oauth2client
pip install PyOpenSSL
pip install gspread
pip install pydrive

import pandas as pd
import gspread
import io

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from oauth2client.service_account import ServiceAccountCredentials
from google.colab import files #comment out if you aren't using Google Colab

# Init Google (with Auth)

file_path = r"<YOUR KEY FILE PATH>.json"

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name(file_path, scope)

# Used to auth Google Sheets
gc = gspread.authorize(credentials)

# Used to get all files in Google Drive folder
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)

# Open Spreadsheet
spreadsheet = gc.open('Class List')

# Get Student Class List
student_list_sheet = spreadsheet.get_worksheet(0)
student_list = student_list_sheet.get_all_values()
student_headers = student_list.pop(0)

# Read Student Class List into DataFrame
df_students = pd.DataFrame(student_list, columns=student_headers)

df_students

template_spreadsheet = gc.open('Template_Rubric')

detailed_rubric = template_spreadsheet.get_worksheet(0)
rubric_summary = template_spreadsheet.get_worksheet(1)

for index, element in df_students.iterrows():
  
    print(element['Student Number'])

    # Create Workbook
    workbook = gc.create('{}_Rubric'.format(element['Student Number']), folder_id='<YOUR FOLDER ID>')

    # Update Rubric Path (in Student Class List)
    sheet_id = workbook.id
    sheet_url = 'https://docs.google.com/spreadsheets/d/{}/'.format(sheet_id)

    # Update Student Rubric Path
    student_list_sheet.update('D{}'.format(index + 2), sheet_url)

    # Duplicate Spreadsheets
    detailed_rubric_worksheet = detailed_rubric.copy_to(spreadsheet_id=workbook.id)
    rubric_summary_worksheet = rubric_summary.copy_to(spreadsheet_id=workbook.id)

    # Delete Sheet1
    worksheet = workbook.sheet1
    workbook.del_worksheet(worksheet)

    # Get Duplicated Spreadsheets
    student_spreadsheet = gc.open('{}_Rubric'.format(element['Student Number']))
    detailed_rubric_worksheet = student_spreadsheet.get_worksheet(0)
    rubric_summary_worksheet = student_spreadsheet.get_worksheet(1)

    # Update Sheetnames
    detailed_rubric_worksheet.update_title('Detailed Rubric')
    rubric_summary_worksheet.update_title('Rubric Summary')

    # Update Student Information
    detailed_rubric_worksheet.update('B1', str(element['Initials']))
    detailed_rubric_worksheet.update('B2', element['Surname'])
    detailed_rubric_worksheet.update('B3', element['Student Number'])

    # Update #REFs in Student Rubric - Student Information
    for row in range(1, 5, 1):

        # Get Formula  
        cell = rubric_summary_worksheet.acell(f'B{row}', value_render_option='FORMULA').value

        # Set Formula
        rubric_summary_worksheet.update(f"B{row}", cell, raw=False)

    # Update #REFs in Student Rubric - Student Summary Marks
    for row in range(7, 19, 1):

        # Get Formula    
        cell = rubric_summary_worksheet.acell(f'D{row}', value_render_option='FORMULA').value

        # Set Formula
        rubric_summary_worksheet.update(f"D{row}", cell, raw=False)

def GetData(workbook):
    
    # Open Spreadsheet
    spreadsheet = gc.open(workbook)

    # Retrieve Student Number From Spreadsheet
    student_number = spreadsheet.get_worksheet(1).get('B3') # saves as list of list
    student_number = student_number[0][0]

    # Get Detailed Rubric
    detailed_rubric = spreadsheet.get_worksheet(0).get('A6:H42')
    rubric_headers = detailed_rubric.pop(0)

    # Get Category Rubric
    category_rubric = spreadsheet.get_worksheet(1).get('G6:J9')
    category_rubric_headers = category_rubric.pop(0)

    # Get Sub-Category Rubric
    sub_category_rubric = spreadsheet.get_worksheet(1).get('A6:E17')
    sub_category_rubric_headers = sub_category_rubric.pop(0)

    # Get Total
    total = spreadsheet.get_worksheet(1).get('I10') # saves as list of list
    total = total[0][0]

    # Read Rubrics into DataFrame
    df_category_rubric = pd.DataFrame(category_rubric, columns=category_rubric_headers)
    df_sub_category_rubric = pd.DataFrame(sub_category_rubric, columns=sub_category_rubric_headers)
    df_detailed_rubric = pd.DataFrame(detailed_rubric, columns=rubric_headers)
    
    # Return all of the dataframes, the student_number and the total
    return df_category_rubric, df_sub_category_rubric, df_detailed_rubric, student_number, total

def ProcessResults(df_entry_raw, sheet_index, student_number, transpose=True, has_student_number=False):
    
    try:
    
        if transpose:
            
            # Transpose dataframe
            df_entry_transpose = df_entry_raw.transpose()

            # Make the Result Row the Headers
            df_entry = df_entry_transpose.rename(columns=df_entry_transpose.iloc[0])
            
            # Remove the Result Row
            df_entry = df_entry.iloc[1: , :]

            # Remove 'Mark' as the Index
            df_entry = df_entry.reset_index(drop=True)


        else:
            
            df_entry = df_entry_raw
            
        # If the student number is not provided in the dataset, assign it
        if has_student_number == False:
        
            # Add Student Number Column
            df_entry['Student Number'] = student_number

            # Move Student Number Column to the Front of the DataFrame
            cols = list(df_entry.columns)
            cols = [cols[-1]] + cols[:-1]
            df_entry = df_entry[cols]
        
        # Write to Excel if something changes, get the new columns and add it to the Results sheet
        #df_entry.to_excel('{} Results.xlsx'.format(sheet_index), index=False)
        
        # Open Results Spreadsheet
        spreadsheet = gc.open("Results")

        # Records
        results_worksheet = spreadsheet.get_worksheet(sheet_index)
        results_records = results_worksheet.get_all_values()

        results_headers = results_records.pop(0)

        # Read Results Records into DataFrame
        df_results = pd.DataFrame(results_records, columns=results_headers)

        # Check if Student Results Already Recorded
        if (df_results['Student Number'] == student_number).any():

            # Get Index of Current Student Number
            indexes = df_results.index[df_results['Student Number'] == student_number].tolist()

            for index in indexes:

              # Drop old Record
              df_results = df_results.drop(index)

        # Add Student Record
        df_results = df_results.append(df_entry)

        results_worksheet.update(
            [df_results.columns.values.tolist()] + [[vv if pd.notnull(vv) else '' for vv in ll] for ll in df_results.values.tolist()]
        )
        
        return True
        
    except Exception as e:
        
        print(e)
        
        return False

def ProcessStudentResults(workbook):

    status = False

    results = GetData(workbook)

    df_category_rubric = results[0]
    df_sub_category_rubric = results[1]
    df_detailed_rubric = results[2]
    student_number = results[3]
    total = results[4]

    # Filter columns: Detailed Results 
    df_entry_raw = df_detailed_rubric[['Criteria','Mark']]
    # criteria table: first tab (0) in workbook
    status = ProcessResults(df_entry_raw, 0, student_number=student_number)

    # Detailed Results Processed Successfully -> Filter columns: Sub-Category Results
    if status:

        print('Detailed Results Updated')

        df_entry_raw = df_sub_category_rubric[['Sub-Category','Mark']]
        # second tab (1) in workbook
        status = ProcessResults(df_entry_raw, 1, student_number=student_number)

    else:

        print('Detailed Results NOT Updated')
      
    # Sub-Category Results Processed Successfully -> Filter columns: Category Results 
    if status:

        print('Sub-Category Results Updated')

        df_entry_raw = df_category_rubric[['Category','Mark']]
        # third tab (2) in workbook
        status = ProcessResults(df_entry_raw, 2, student_number=student_number)

    else:

        print('Sub-Category Results NOT Updated')

    # Category Results Processed Successfully -> Add Total and Display
    if status:
      
        print('Category Results Updated')

        df_entry_raw = pd.DataFrame(data=
                                  {
                                      'Student Number': [student_number], 
                                      'Total': [total]
                                  }, 
                                  columns=['Student Number','Total'])

        # Results tab (tab 3) in the file
        status = ProcessResults(df_entry_raw, 3, transpose=False, has_student_number=True, student_number=student_number)

        if status:

            print('Total Updated')

        else:

            print('Total NOT Updated')

    else:

        print('Category Results NOT Updated')def ProcessStudentResults(workbook):

    status = False

    results = GetData(workbook)

    df_category_rubric = results[0]
    df_sub_category_rubric = results[1]
    df_detailed_rubric = results[2]
    student_number = results[3]
    total = results[4]

    # Filter columns: Detailed Results 
    df_entry_raw = df_detailed_rubric[['Criteria','Mark']]
    # criteria table: first tab (0) in workbook
    status = ProcessResults(df_entry_raw, 0, student_number=student_number)

    # Detailed Results Processed Successfully -> Filter columns: Sub-Category Results
    if status:

        print('Detailed Results Updated')

        df_entry_raw = df_sub_category_rubric[['Sub-Category','Mark']]
        # second tab (1) in workbook
        status = ProcessResults(df_entry_raw, 1, student_number=student_number)

    else:

        print('Detailed Results NOT Updated')
      
    # Sub-Category Results Processed Successfully -> Filter columns: Category Results 
    if status:

        print('Sub-Category Results Updated')

        df_entry_raw = df_category_rubric[['Category','Mark']]
        # third tab (2) in workbook
        status = ProcessResults(df_entry_raw, 2, student_number=student_number)

    else:

        print('Sub-Category Results NOT Updated')

    # Category Results Processed Successfully -> Add Total and Display
    if status:
      
        print('Category Results Updated')

        df_entry_raw = pd.DataFrame(data=
                                  {
                                      'Student Number': [student_number], 
                                      'Total': [total]
                                  }, 
                                  columns=['Student Number','Total'])

        # Results tab (tab 3) in the file
        status = ProcessResults(df_entry_raw, 3, transpose=False, has_student_number=True, student_number=student_number)

        if status:

            print('Total Updated')

        else:

            print('Total NOT Updated')

    else:

        print('Category Results NOT Updated')

# View all folders and file in your Google Drive
file_list = drive.ListFile({'q': "'<YOUR FOLDER ID>' in parents and trashed=false"}).GetList()

for file in file_list:
    
    if 'rubric' in file['title'].lower() and 'template' not in file['title'].lower(): 

        print(file['title'])

        ProcessStudentResults(file['title'])

        print('\n')