Analyze Data

Introduction
Once the template has been created, and users have entered the appropriate student data, the student data needs to be analyzed to deliver several reports/visualizations:

1) Graded Assignment - this will have color coded highlights for correct and incorrect answers as well as provide a final score for each student.

2) Grades Summary - this will list each student in order and list their score.

3) Most Missed Questions - This gives a breakdown of each question and the number of students who missed it. This is sorted in descending order from most missed to least missed. If there are multiple questions who were missed an equal number of times, they are sorted in alphanumeric order

4) Grades Summary by Score - This is the same information as in the Grades Summary report but it is automatically sorted in order by score and then by student name in alphanumeric order

5) Answer Choice Analysis - This shows what percentage of students gave each answer choice for a given question.

All of this is generated from the Student Data worksheet. If Student Data is ever updated, then the user should go back to the Dashboard and click Analyze again to ensure all data is up to date.

Before continuing, ensure that you repeat the steps you took before beginning the Create Template section. This is because many of those same variables will need to be set here as well. Additionally, you will need a separate callback URI from Google from that used for Create Template. Information about this is provided in greater detail in the Create Template section of this documentation.

analyzeData()
Please note this code is based on that of Matyas Danter. Direct links to his code will be provided in the References section.

This acts as the constructor for the analyzeData class. It begins the process of creating an OAuth login screen for the user by use of the GoogleAuthorizationCodeFlow.Builder() function. It requires an HTTP Transport, JSON Factory, Google OAuth Client ID and secret, as well as a defined access scope.

Additionally, the type of access can be set to determine whether or not access should be permitted when the user is not present at the browser. Additional details can be found in Reference 1 in the Additional Reading Section.

The HTTP_TRANSPORT, JSON_FACTORY, and SCOPES variables are all part of a class definition (as well as the ClientID and clientSecret mentioned in the Introduction section). The only one of these that should need any editing is the SCOPES variable.

SCOPES allows you to choose what type of data access the service requires. In the case of Nebula, the ability to read and write from our user's file repository (Google Drive via Google Sheets) so this is set to SheetsScopes.SPREADSHEETS per Google's documentation (this is Reference 2 in the Additional Reading Section).

buildLoginURL()
This function simply creates a new Google OAuth URL such that users can securely login to their Google accounts to access Nebula. This is done using a randomly generated state token.

wrapper()
This function is responsible for calling all of the functions required for analysis as well as taking in the authorization code (authCode) from Google and trading it in for a token (stored as credential).

First, all worksheets other than Student Data and the Dashboard are removed to allow a clean slate. If this is the first time the worksheet has been analyzed then nothing will change as a result of the removePreviousAnalyses() function being run.

Next, duplicateSpreadsheet() is called to make a copy of Student Data to make the foundation for the Graded Assignment worksheet.

Graded Assignment's title (i.e. what shows on the tab at the bottom of the screen as you switch between worksheets) is updated from Copy of Student Data to Graded Assignment. Additionally, the scoring column is added and formatted as a percentage and each of the student answer choices are updated to show whether the answer they chose was correct (highlighted in green) or wrong (highlighted in red). Finally, the scores are calculated using the gradeAssignment() function.

Next, another worksheet is created called Grades Summary. This will have its worksheet ID stored and will be formatted using the formatGradesSummary().

After this, the same steps will be repeated for the Most Missed worksheet (and formatMostMissed() function).

Next, the Grades Summary worksheet will be duplicated and the copy will be renamed to Grades Summary by Score and then the scores will be sorted in order from highest to lowest score and then if there are multiple students with the same score, the students' names will be displayed in alphanumeric order.

Finally, a new worksheet will be created in the same style as Grades Summary and Most Missed which is the Answer Choice Analysis.

Control is then returned to the calling function.

newWorksheet()
This function will create a new worksheet given the spreadsheet ID for the spreadsheet to add the worksheet to and the title of the new worksheet. It will return the worksheet ID for use in other functions.

addScoringColumn()
This function will add a column to the Graded Assignment worksheet to store each student's grades provided only the spreadsheet and worksheet IDs.

getWorksheetID()
This function will request the ID of a given worksheet given the position the worksheet is in. For example, if there are three worksheets in a given spreadsheeet, to get the worksheet ID of the middle worksheet, sheetPosition would be set to 1 (sheet positions start at 0).

removePreviousAnalyses()
This function gets all of the names of all of the worksheets in the spreadsheet and compares those to the list of worksheets generated by Nebula and will delete all except Dashboard and Graded Summary by iterating through the names of each sheet and seeing if it matches the name of a worksheet that is meant to be deleted. If not, the worksheet is ignored. If it does match, then deleteSheet() is called.

deleteSheet()
This function creates a request to delete a given worksheet based on the spreadsheet and worksheet IDs. The request is then executed.

duplicateSpreadsheet()
This function is written to allow a worksheet to make a copy of one worksheet and paste it into the same spreadsheet as a new worksheet. The numSheets variable is used to store the number of sheets in the entire spreadsheet. Because counting sheets starts at 0 for the Google API, if there are 8 sheets before, then the 9th sheet would have an index of 8 thus retrieving the newly created worksheet.

coloredHighlight()
This function applies conditional formatting to each of the student answer choice cells. If the answer is the same as the one from the answer key for that question, the cell is made green. If not, it is made red. Conditional formatting formulas for each scenario are created in this function and made into a series of requests, two for each cell (one for correct and one for incorrect). These are all put into an ArrayList and executed all at once.

formatAsPercentage()
This function sets the cell format of a given set of cells. In this case, there should be no values after the decimal point so the number pattern is set to #,0% which is just show whole numbers (i.e. 100%).

gradeAssignment()
This function calculates the grades of the assignment by seeing if each of the answers is equal to the corresponding value from the answer key. A formula is inserted into each cell to do this through use of a request. The requests are all stored in an ArrayList until the end when they are executed all at once. This will be used to populate both the Graded Assignment and Grades Summary worksheets.

formatGradesSummary()
This function calls three functions. This first is the formatAsPercentage() function which makes sure that when scores are inserted, the cells will know they need to be formatted as percentages. Secondly, the copyandPaste() function copies our student names. Finally, the heading is created by calling the headingCells() function.

More information about the copyandPaste() function can be found in its section on this page. The headingCells() function is discussed in greater detail in the Create Template section of this documentation.

copyandPaste()
This function takes in two ranges - an origin and a destination. Each range is made up of a starting and ending row and column. Please note these both start at zero and the ending row/column is not included in the range. If you want to copy the first through third rows of column D, startRow would equal 0, endRow would equal 3, starting column would equal 3 and end with 5.

formatMostMissed()
This function begins by calling headingCells() to setup the heading for the worksheet. Then, the question names (typically Question 1, Question 2, etc) are copied from the answer key and pasted into the worksheet. Then, the frequency missed is calculated using the missedFrequency() function. Finally, the report is sorted in descending order by most missed question. If multiple questions were missed the same number of times, all questions with the same frequency are then placed in alphanumeric order.

missedFrequency()
This function works by getting the total number of students and subtracting one each time a question is answered correctly. The results are then sorted in descending order by number missed. If multiple questions were missed the same number of times, all questions with the same frequency are then placed in alphanumeric order.

sortRange()
This function takes a range of values and sorts them in either ascending or descending order (this is controlled by the sortOrder variable) by the sort dimension. The request is then executed.

choiceAnalysis()
This function begins by creating a hidden worksheet which will contain the data needed by Answer Choice Analysis. This worksheet is called Number of Answers Per Question and it will contain each of the questions and how many distinct answers there were (i.e. A, B, C, D).

After setting up the new worksheet, writeNumAnswersPerQuestion() is called to determine how many distinct answers there were to each question. After this, the text labels for each question are pasted into this worksheet and the heading cells are completed for both worksheets.

Then, the Answer Choice Analysis worksheet can be filled out. This is done by formatting the columns that will be used for percentages as percentages by calling formatAsPercentage(). Then, the columns where each answer choice will go are bolded.

Just before this, the values from Numbers of Answers Per Question are read in for easy access later.

After this point, the for loop begins to add requests for the values, the headings, and the borders for each question. Because they are different types of requests, they cannot be combined into the same ArrayList.

Once all ArrayLists have been generated, their requests are executed in a batch (one for each ArrayList) and the final report is completed.

writeNumAnswersPerQuestion()
This function counts the number of unique answer choices provided for each question. This is done using a =COUNTUNIQUE formula in Google Sheets and making a request for each question. These requests are stored in an ArrayList until the end where it is then run.

readNumAnswersPerQuestion()
This function gathers the values from the Number of Answer Per Question sheet and stores them in an integer array which is passed back to the calling function.

boldCells()
This function takes in a range of cells and makes them bold by setting the text format. Once the request has been created it is then executed. Please note that any data previously in the cells will be wiped out after doing this. You must bold the cells before inserting data.

deleteSheet()
This function takes in the spreadsheet and worksheet ID and then deletes it per the standard methodology provided by Google's API. Additional information is available by looking at the 6th link in the Additional Reading section of this documentation.

numberstoCoordinates()
Please note this code is based on that of Techie Delight. Direct links to their code will be provided in the References section.

This function converts cell coordinates in the A1 notation to coordinates (i.e. row 0, column 0) by grabbing the row number and then dividing by 26 and matching up the remainder with a letter of the alphabet. This allows support for columns beyond the Z column (i.e. cell AC4)