ABCDEFGHIJKLMNOPQRST
1
Canvas API From a Spreadsheet!?! (beta)
2
3
What is this?
4
This is a spreadsheet (with code behind it) that makes it easier to pull data from the Canvas API. Lots of people want to be able to pull data and analyze it, but lots of people aren't coders. And honestly, you shouldn't need to be. It's great that there's programmatic access to the data, but it'll be even greater now that you can start getting at it without being a programmer.
5
6
Setup Instructions
7
0. Make your own copy of this spreadsheet and attached code (if you're pulling as a Google Template this will already be done for you. If not, click File -> Make a Copy)
8
1. Go to your profile in Canvas
9
2. Generate an access token at the bottom of the page
10
3. Copy the token value -- you'll need it in a minute
11
4. In the Spreadsheet, click Canvas -> Check Settings
12
5. Enter your API host, i.e. "https://yourschool.instructure.com" and click OK
13
6. Enter the access token you generated before and click OK
14
7. In a new sheet, type in a cell, =canvasObject("/api/v1/users/self/profile") to make sure things are wired correctly (you should see profile information, not any error messages)
15
16
17
How to Use
18
If you haven't used custom functions like these in Google Spreadsheets before, it's a little bit of a mental adjustment. Most built-in spreadsheet functions return a single value. These Canvas API functions, since they have so much data to give back, return sets of data. In Google Spreadsheets you'll enter the function into a single cell, but the results will "spill out" into additional cells to the right of and below the entered cell. This is actually nice because it makes it easy to then perform calculations based on the whole data set, specific columns, etc. It does potentially cause problems, however, if one function's results overflow into another function's results area. For that reason it's typically best to only put a single function on each sheet.
19
20
Examples
21
There are a number of helper methods that make it easier to look up certain data. Check out the script editor view for a full list, but here are some examples.
22
=canvasCourseList()
23
=canvasCourseList(221)
24
=canvasPageViews("12345")
25
26
You can also make general calls to API endpoints that return lists or a single object.
27
=canvasList("/api/v1/users/9876/logins")
28
=canvasObject("/api/v1/courses/1234")
29
30
All API calls take an optional final parameter that lets you specify things like the number of results to retrieve, and the list of columns to show. NOTE: If you have results>50 then DON'T RUN THE QUERY OFTEN or panda will be sad, and we all may lose our fun toys :-).
31
=canvasCourseList(221, "results=100")
32
=canvasPageViews("12345", "results=30&keys=url,action,user_agent,user_id,render_time")
33
=canvasList("/api/v1/users/9876/logins", "keys=account_id,unique_id")
34
35
You can also use the API to look up account-level reports, which normally have to be first downloaded as CSV files
36
=canvasAccountReports("self") # list of reports for the current account
37
=canvasAccountReport(10, "grade_csv") # inserts the contents of the csv into the spreadsheet
38
(reports still need to be generated either in the Canvas UI or using Canvas -> Generate Report
39
40
Notes
41
Source: https://github.com/whitmer/gsheet_canvas_api
42
ToDo: Support APIs that require a background process (like the reports API), even more error handling, better messages when missing configuration values
43
Suggestions? If you think there are more helper functions we should add, please add an issue to the github repo with details
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100