API Calls Made Simple- Curtis Rose

[awilliams]

With a simple-to-use Google Sheet, with only one plugin, anyone can make basic API calls without the need of assistance from a programmer or IT department. This easy-to-use form allows for customized reports that can be updated live as needed.

[padmaja_mandali]

Is the link tiny.cc/InstConAPI  still available. I get to the google sheet with the link but get viewer access only. I requested access. Is access to the google sheet on request basis only?Am I missing something ?

[brian_t_anders]

Padmaja,

You have to save a copy of the spreadsheet. The code you need will be in the copy of the script editor.

[Alexandre_Sch]

Wow! That looked nice! 

I saved the sheet and tried to populate the informations, but i'm receiving: 

"returned code 401. Truncated server response"  Not authenticated

=ImportJSON("https://XXXX.beta.instructure.com/api/v1/users/39?per_page=1000&acess_token=(this is a User access token or a account token, if it is a account, what is the URI that need to be set? ", "/sis_user_id", "noInherit, noHeaders")

Any help on how to make this work? 

Regards,
Alexandre Schneider.

[brian_t_anders]

 @Alexandre_Sch ‌

Can you try the request without the ...,"/sis_user_id", "noInherit, noHeaders"... part?

[Alexandre_Sch]

Hi Brian, yeah i tried that and still not working!

[audra_agnelly]

 @Alexandre_Sch ‌

I used the following value in the first cell and the sheet populated as expected:

=ImportJSON("https://XXXX.beta.instructure.com/api/v1/users/213220?per_page=10000&access_token=XXXXXXXXXXXXXX", "/sis_user_id", "noInherit, noHeaders"

The access token is your account token, in this case you'd also need to make sure you're using a token valid in Beta. Anyone can obtain a token, but your ability to run an API call is based on your user permissions. Are you a Canvas Admin at your institution with the ability to look up users? If not, you wouldn't be able to run this API call. Try pasting https://XXXXX.beta.instructure.com/api/v1/users/39 into your browser after logging into Canvas, does that return JSON data?

[Alexandre_Sch]

Hi Audra, how are you? Still did not tried to do it, will return as soon as i try what you mentioned. Regards!

[gsaito]

I'm a Canvas admin and I tried the following, but I'm getting an error (see image below).  Any suggestions?

=ImportJSON(https://paloaltou.beta.instructure.com/api/v1/users/1176?per_page=100&access_token=xxxx, "/sis_login_id,/email,/name", "noInherit, noHeaders")

[brian_t_anders]

 @gsaito ‌,

You need quotes between the URL request. E.g.:

=ImportJSON("https://paloaltou.beta.instructure.com/api/v1/users/1176?per_page=100&access_token=xxxx","/sis_login_id,/email,/name", "noInherit, noHeaders")

If that doesn't work, try it without the additional arguments, then narrow it down:

=ImportJSON("https://paloaltou.beta.instructure.com/api/v1/users/1176?per_page=100&access_token=xxxx")

[gsaito]

Thank you, Brian Anders, I added the quotes and it's working!

[robmoores]

Hi. Is tiny.cc/InstConAPI still available or available elsewhere ? When I type that address into my browser (Chrome, latest updates) I get a page that starts with .."Sorry, we weren't able to locate that URL"

Thanks .

[robmoores]

I found it on GitHub and managed to attach it to a Google Sheet - works a treat, many thanks !

[ryan_kerrison]

Hi Robert, 

Would you mind sharing, I cant seem to find it on GitHub or anywhere else for that matter!
Much appreciated!

Ryan

[robmoores]

Hi Ryan

Sure - here you go … GitHub - bradjasper/ImportJSON: Import JSON into Google Sheets, this library adds various ImportJSON... 

In my Google sheet I've been simplifying the construction of the parameters, as it's so easy to make a typo.

I'll be happy to share it with you and the community if it's of any use ….

[ryan_kerrison]

Oh, amazing! Thank you so much - I'd love to see something like that if you're willing to share!

Ryan

[robmoores]

Happy to share - I'll prettify and comment it over the next few days and share by early next week.

[robmoores]

You should be able to download the sheet from here: ImportJSONSheet - ambiently fork for Canvas Community - Google Sheets 

IMMEDIATELY TAKE A COPY OF THE SHEET AND USE THAT COPY OR YOU MAY LEAVE IMPORTANT DATA IN IT FOR EVERYONE TO SEE AND USE - SUCH AS YOUR ACCESS TOKEN - AND THAT WOULD BE BAD FOR YOUR STUDENTS, YOUR INSTITUTION ... AND YOUR CAREER !

Let me know if that doesn't work or you have any issues using it.

... and don't forget to take that copy - and use that copy not the original !

[ryan_kerrison]

Hugely helpful!

Thanks a bunch - I'll ensure I take a copy

Kindest,

Ryan

[MikeBrinkman]

When I call "https://myinstitution.instructure.com:443/api/v1/courses?per_page=10000&access_token=...", I get 4 courses returned. Clearly I am doing something wrong. Any ideas?

[wdewysockie]

Hi all,

We got this working. The formula is populating the spreadsheet with data from Canvas. However, the data is limited to just 100 rows. We've searched for a solution but haven't found one. Anyone experience this issue and find a fix?

We're using the "per_page=10000" argument.

Many thanks,

Bill

[Alexandre_Sch]

Hi William, 

You will need to implement pagination. Basically, you will store the value from each next x results and run the api again to get the next X and so on untill there is no "next" page to go. After that, you can merge all the objects in a single one and converts the json to any desired variable.

I tried to use Per_page with a higher value and did not worked untill i implemented the pagination on my lti (not these googlesheet one). 

[wdewysockie]

Hi Alexandre,

Many thanks for your quick response. After seeing your post, I did some querying and it looks like pagination will require some programming skills. I'll pass this project off to our developers with the information you provided. I was hoping to relieve them of some busy work by handling some of the less complex reporting tasks. Oh, well. 🙂

Thanks again,

Bill

[wdewysockie]

Hi Alexandre,

Well, this was a major breakthrough for me. I opened the URL part of the formula in Firefox and was able to find the link headers under the Headers tab. This gave me the argument I needed to cycle through the pages of data in Google Sheets. Thanks again for pointing me to pagination.

Bill

[Alexandre_Sch]

Glad it worked o/

[penny_christens]

Hi @All. While late to this content, I would value the sheet Curti Rose makes reference to in his InstructureCon 2017 video.
Thanks in advance, community!