Handling Test Grades in Google Forms

In the context of distance learning, teachers are faced with the problem of distance monitoring of students' learning. "Distance" is over, but the work done continues to be useful in the future.

One of the simplest ways to organize testing over the Internet is to use the Google Forms service. To turn a simple list of questions into a test with answers and scores, you need to go to the Form settings and turn on the "Quiz" mode.

«Responses». , Google Sheet. :

Score. . , , "/ 2" Custom Number Format. IMPORTRANGE(), , . QUERY() - . .

, : Test 1, Test 2 Test 3. "Full Name". . : Test 1 (Responses), Test 2 (Responses), Test 3 (Responses). : Timestamp, Score, Full Name. , .

Googe Sheet. , , Grade Book. (T1, T2, T3), Source, , Grade Book .

T1, T2, T3...

T1, T2, T3 . A1 :

=IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1dee7GYwj1NgZfDNZJgLMVOcWRmPnvSAvg3KJ0ahqkmI",
"Form Responses 1!A2:C"
)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

"https://..." - URL "Test X (Responses)", , "Form Responses 1" - , "A2:C" - , ( ).

"Score" : . D1 :

=ArrayFormula(split(B1:B, "/"))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

D , E - .

Source

Source - , - . Full Name.

A1 "Source" :

=QUERY({
QUERY('T1'!A1:E, "SELECT 'T1', A, B, C, D, E WHERE A IS NOT NULL LABEL 'T1' ''");
QUERY('T2'!A1:E, "SELECT 'T2', A, B, C, D, E WHERE A IS NOT NULL LABEL 'T2' ''");
QUERY('T3'!A1:E, "SELECT 'T3', A, B, C, D, E WHERE A IS NOT NULL LABEL 'T3' ''")
}, "SELECT * ")
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

T1, T2, T3 - , , SELECT 'T1'...



- , .

G1 "Source" :

=ArrayFormula(E1:E/F1:F*5)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

H1 , Full Name . , , , . .

=ArrayFormula(INDEX(SPLIT(D1:D, " "), 0, 1))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

"Source" :

Grade Book

A1 "Grade Book" :

=QUERY(
Source!A1:H,
"SELECT H, MAX(G) WHERE C IS NOT NULL GROUP BY H PIVOT A"
)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Source - , , MAX(G)



- , PIVOT(A)



, - .

:

.

PS: In the Russian version of Google Docs, it is necessary to use a semicolon as the argument separator in functions, since the comma is a decimal separator.