Microsoft 365 Community Office Add-ins, 10 Jan 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: API Call from Office Scripts

Description

Web page: https://github.com/sumurthy/officescripts-projects/blob/main/API%20Calls/README.MD

Content

0:00 Intro
2:40 Scenario and API reference
5:16 Scripting
15:55 Results and conclusion

A

Hello in this video I'm going to talk through how to make external api calls from office scripts office scripts is a new feature in excel for web. That allows end users to automate their tasks and, as part of this task, you may need to pull data from an external source or push data out.

A

To do that, you need to understand a few aspects about making an external call from office scripts. I'm going to walk through some of the limitations on the steps required to help with the demo. I'm using this document. On the left hand side you will find the link to this in the description uh do check that out for for source code that I'm using plus additional instructions that I have now before you begin, you need to know a few limitations of using external calls from office scripts.

A

One is that the infrastructure doesn't really support sign-in capability, so that means you cannot make a user provide their name and password and sign into a service. So there is really no uh infrastructure for also managing your credentials or our keys required for the service call. So all that has to be managed within the script or within the workbook and also any skip that calls, and that makes external api call, doesn't really work within power automation.

A

So if you have such scenarios, you will have to manage that all within power automation and um thirdly, the power automation. Sorry, the scripting that uses uh an external api call requires asynchronous calls. So it makes your script slightly complicated.

A

It's not a big deal, but you just need to be familiar with some of the concepts that have listed here in the prior knowledge resources section so do check that out, if you're not familiar with making external call or if you not use the rest api or if you're not used, async await or fetch api calls and then lastly, do check with your local administrator check your policy guidelines to ensure that you can pull data from an external source so that you're not breaking any rules that your admin may have set up all right.

A

So with that prior knowledge, I'm going to walk through a simple scenario that I have in mind.

A

It's a very simple scenario, but it kind of, I think, will get you enough to get started so to the scenario I have in mind is to populate the spreadsheet that I have here with some external source um and the source that I'm looking for is my my github repository github offers a way to create repositories and and manage all the source code or documents that I have, and as part of that, I'm looking to extract all the names of the repositories along with the license information attached to each now I was looking around.

A

I was able to find the exact api that I'm looking for it's in the github reference documentation.

A

It's called list repositories for a user and it is a get api, which means I'm just getting the information, and this is the path to the api, and there is only one info piece of information that needs to be substituted, which is the username. So I'm going to use my username, but you can use your own username in the in the source code to fetch the data and, if I go further down, I can actually see when I make an ap.

A

I call the kind of data that I'm going to get another way to see is to actually just type this in any http interface, such as your browser for get api calls. Typically, this works, so the api endpoint is the api.github.com, and this is the resource path, users, my username and repos, and when I, when I pull this data, I can see that it's sending a collection which is an array of repositories.

A

Each instance of the array is a single repository, so um I can already see that it has name and then um and full information id. So those are all good, I'm also looking for license. So I'm just going to type in license here I'll see that I have a license, but in some cases it's not so not all repositories have license. If I go further down to see what it looks like when there is a license information, I can see that it's an object which has few other keys embedded.

A

So so that's good, so I want to maybe pull in the license key and the url so just for going further down. This is a good example of the name and then the url. So I think I'm I have enough information to get started, so I'm going to go to office. Scripts start a new script.

A

The first thing I'm going to look for is to pull that information down. So once I have the information I can process and update the spreadsheet.

A

To do that, I'm going to use an api called fetch and fetch is native to typescript and browsers support it and what it offers is a way to pull information from um from an api endpoint. So this is the uh the endpoint.

A

You can also get that from the reference documentation and because this is a get api call, it's it's a much simpler api to invoke, because I don't need to send any data to the the server like the data. That might be required to create a resource externally. So since I'm pulling it's much easier now fetch when I hover over, it's telling me that it's returning a promise, so all it means is that you don't get the data instantly in a synchronous fashion.

A

It's an asynchronous communication, which means that I have to wait for the information to come through again. If you are not used asynchronous api calls in javascript or typescript do check out the links when I wait for an api call to complete before going down further. What it means is that let's say I have a instruction.

A

You know some code here. This code will not execute until this line completes. The control goes back up to the whoever called the main function and it waits for this information. This api call to finish before going down further. So that's that's exactly the pattern I want to use, but if I use a weight I have to mark the api and the function that I'm using await as an async. So this tells uh typescript that hey, I'm, I'm making an asynchronous call and I'm using an away keyword.

A

So um so response comes back and the response has a few attributes, but the thing that I'm looking for here is the list of repos. So to do that, I know it's a json payload, so I can tell by looking at it. So um I can say: hey give me the list of repos, which is the collection um and say I'll, say response, and there is a json method, and this is also asynchronous so I'll do that so response also offers few other things like.

A

I think whether it's okay is a boolean, whether it's okay to continue or not. So if you want to do error handling, you can do that. I think it probably has few other things like the actual code.

A

um You know you can you can look at the the fetch api documentation to see what it what's needed um for doing complete error handling, so I'm not going to go into that because the resource here the link, offers all that information. So I'm going to assume that this is going to work fine and proceed further.

A

So once I have the repos I'm going to go through each of the reports and extract the information that I need to do that, I'm going to just use a simple for loop, I'll, say let repo of repos.

A

That means go one at a time and each instance here is going to be contained within the repo. So I'm going to create another constant collection, uh I'll call it this rows, and this is going to contain either string or boolean or number array.

A

uh It's a two-dimensional array, because I want to populate a grid of data here within excel.

A

So this is the typescript way of defining and I'm going to initialize it with an empty array at the beginning. So inside this loop, I'm going to push what I'm looking for in this case, repo dot so you'll see that I'm not getting any code completion. That's because I haven't defined.

A

What comes back now to do that, I have to use the interface definition the typescript offers. So I'm going to call it repository.

A

And you can only selectively define what you need. So in this case I'm using the name and maybe id so I'll say a name. It's a string.

A

Id is a string. The next thing I'm looking for is license now license. Is it's not a it's, not a basic type, it's an actual object, so I'm going to have to define another type car license.

A

So in here I'll come back and define another type called license, and I think I was looking at earlier. I'm looking for.

A

The name and then the url, so I'll, look for name, string and url is a string, and I notice that not all repositories have license. So I'm going to say this is an optional property. So that way, my interface doesn't expect that license be present at all times now. This is another thing to note, which is, if you're, not sure that any property is going to come through.

A

You can mark that, as as an optional property, but in my case I know that name and id are always going to be present and if there is license information, the name and url are going to be present, um and so you, just that just requires a little bit more subject matter. Knowledge of what information is present and not present, to know that um you know how to qualify this correctly and the api reference documentation should tell you about all the properties that comes through and what's required and what's not required to be present.

A

So yeah do check out the you know, whatever api, that you're going after the reference documentation to know how to define your interface, I don't have to define all the properties you know. Obviously, this individual instance of reaper is pretty large, I'm not interested in any of it. So in my interface, I don't need to define all of it. I'm only defining information that I'm going after once I define it. I can say that hey this repos that I'm getting is an act. It's actually an array of repository right.

A

So and now this tells typescript that the repos is a collection of repositories. So when I come back to an uh within the loop um and I do dot I'll get the code completion, so that's pretty cool so I'll.

A

So what I want to do is push an array each time for each of the repos I'm going to do repo id repo name and repo license dot name and I'm gonna also pull repo license dot url.

A

Right um so so there is one issue with this in that license may or may not be present for a given repo, um and I can kind of do checks beforehand here and only you know initialize it with something else, but a quick, easy way to do. This would be, to just add a question mark here um and types at the time of running this code.

A

um What happened is if there is no license information, it won't go any further and try to access name or url properties, it'll stop there and you'll just basically get blanks. So that's, okay!

A

So once I have this, the the rows array will contain um a single row for each of the the repositories with these attributes these properties. Now, once I do that, I want to now update the worksheet so I'll get a reference to the sheet.

A

Just use the active worksheet for now you can also get any worksheet using its name and then now so let's say I want to figure out the range in which I'm going to update to do that. It requires few api calls so I'll get the sheet I'll define the starting point. So let's say it's uh a2, let's say get range a2 and then now I want to know uh the target range. So if it starts here, you know how big the data should be.

A

How big the range should be so to do that I'll, use the get resized range and pass in.

A

I need to tell the the resize range how many rows to expand by and how many columns to expand by to do that. I'm going to use the the rows array and get its length, which is how many rows- and this is 000x so subtract by one and rows I'll pick any row and how many columns there are subtract by one. So this will tell me how many rows to expand by and how many columns to expand by. So once I have this, I'm going to use the range to set its values.

A

And I'm going to provide the rows.

A

And that's pretty much it so you'll notice that I'm not doing any error handling here. You may wish to add all that as part of your script.

A

Well, the the first place would be to make sure the response is okay, before proceeding and also making sure that you have some data uh before updating, but for now I'm just gonna assume that all that is gonna work, fine and I will just simply run this code.

A

All right, so this is the data that I was looking for. So let's see how it's come across.

A

Okay, I'll close this for now, all right so looks like I have the id name of the repos and anytime. There is um license information, it has the license name and then the the url that I'm looking for all right, so it also starting from a2. It goes all the way down and fetches all the repositories that I have all right. So um I think it's achieving what I wanted to do. You could do you know you could go further and enhance this further.

A

For instance, you may want to add the headers and then create a table on top, so all of that is pretty basic to do and- and you can check out some of the other videos that I have to find out how to do such type of range, formatting and adding table rows and all that okay.

A

So hopefully this has been useful, so um do check out the the link to the repo where I have this documentation and you'll find all the source codes uh source code that I've used and uh if you have any questions leave. Please leave a comment. If you find this channel useful, please do subscribe and and leave a comment, um and that will help me formulate my next next video. Alright, thanks for.

A