How to create your own custom data types in Excel using ScriptLab and the Javascript API.
Important Links:
Try out the workbook with my custom data types: TestTheseCustomDataTypes.xlsx
Github for the example using a Custom Function:
gist.github.com
Github for the example using an add-in:
gist.github.com
Table of Contents
(0:00) The quest for a perfect custom data type
(1:50) Download a workbook with working custom data types
(3:54) The back-end API
(5:17) ScriptLab add-in
(8:33) ScriptLab Function
(10:49) Visual Studio version
(12:00) Nerdvana check
Important Links:
Try out the workbook with my custom data types: TestTheseCustomDataTypes.xlsx
Github for the example using a Custom Function:
How to create your own data type and return as an entity through a custom function. ScriptLab demo.
How to create your own data type and return as an entity through a custom function. ScriptLab demo. - Excel Custom Data Type Custom Function Demo.EXCEL.yaml
gist.github.com
Github for the example using an add-in:
How to create your own data type and set the cell values by using Range.valuesAsJson property. ScriptLab demo.
How to create your own data type and set the cell values by using Range.valuesAsJson property. ScriptLab demo. - Excel Custom Data Type Add-in Demo.EXCEL.yaml
gist.github.com
Table of Contents
(0:00) The quest for a perfect custom data type
(1:50) Download a workbook with working custom data types
(3:54) The back-end API
(5:17) ScriptLab add-in
(8:33) ScriptLab Function
(10:49) Visual Studio version
(12:00) Nerdvana check
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2451.
Excel custom data types from the JavaScript API.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
This is our third episode on the quest for a perfect custom data type.
We're talking about these data types that were introduced in Excel a few years ago.
They're awesome, but it's not our data.
It's Wolfram Data or Wikipedia data.
What we really need is to get our data into these. And so in the first two videos I showed how to do this with Power Query and then in the last video with Power BI.
But today I'm going to show you how to do this with the JavaScript API .
My goal for this is I want to be able to type plain text into a cell and convert it to a data type.
Ideally from the same gallery as the other data types.
From the card for that data type, I want to be able to return an array of entities.
That's this example that I showed you yesterday with Bob Marley.
If I come in and go to Members, so that is a table, and not just the table of text, but a table of entities.
So right here =A13.Member returns a list of names and each one is a person.
I can then click get the image: =B13#.Image That's my goal.
I want that to happen with my company data.
The data type should be able return an image from an image URL which power BI could do, but power query cannot.
The data type should be able to return a clickable clickable hyperlink and so far none of them can do that.
And it would be nice if I could customize the little icon so it was a person it was a person instead of just the built in icons.
This is Nirvana.
This is what I'm looking for.
They announced it Ignite, which was what November of 2021.
And we've been waiting ever since then for one extra piece to get released.
And finally on this past Friday it was released.
Check this out, I'm going to let you download this workbook right here.
There's nothing special.
There's no code.
These data types are here and they'll work everywhere.
I can send it to you and they will work.
They will even work on your iPad.
My test of “Can an entity return an array of entities?” So here we will go into MrExcel 2021 and I'll go to Author.
And then I want a list of books.
There are 61 items there.
I have not found a way to do this with Power BI.
If I come in and say, add that to the grid, I get the 61 books that I've written.
Here, let's try another one.
Ken & Miguel’s new book.
So we'll go in and I want to see the authors add the authors to the grid.
And then for each one.
Get the image.
So it's returning an image.
And I should be able to change that to B2 Hash.
Look at that.
So from the entity in A2 I'm returning an array of entities and then I can pull the image or other information out.
This is everything that I've been dreaming for.
If your IT department delivers an API that has your, let's say, company product list or something like that.
Just imagine how powerful that will be to give someone a spreadsheet and they can go in and investigate and so on.
It's simply amazing.
I'm going to show you the code that we use to create this.
And this is code that you can run, you yourself can run right now in the beta version of Excel.
The real way that you're going to want to do this is to create an Office Add in.
That's beyond the scope of this podcast.
But just using ScriptLab you can get something like this and send it on to other people in your company.
So I know some of you are not going to be interested in the technical details.
In fact, you should send the rest of this video to your IT department so they can start working on it now.
But for those of you who want to dive in.
Let's dive in.
Now look as you test the spreadsheet today and you want to try to build your own.
You're more than welcome to use our data.
But the only thing less interesting than Wikipedia data is my product data.
You're going to need your IT department, your web person, to build out a back end to get your data.
In the meantime, feel free to use ours, but show them this.
The first step is getting the back end built.
And for us, we already have an application on our website that has a database that drives these books.
And a second data table by author.
So it was up to our web team to take this data and convert it to an API.
The API sends into here, looking for a book, title of VBA or title of Pivot, let's say.
And it returns a huge JSON string.
I can take this data into a JSON online validator.
And, we purposely built this to not look exactly like it needs to look in Excel.
That way, we would have to do some work in ScriptLab or or in Visual Studio to reformat things.
But step one is to get your company web team to build out this API.
Alright lets head back to Excel where we can install ScriptLab.
Insert tab, Get Add-Ins.
Search for a ScriptLab.
Right there that.
Click Continue.
First, click on ScriptLab in the ribbon.
We want to create new Code.
This pane will appear over here on the right hand side with a template.
But we're not going to use their template, so select everything and delete it.
On the libraries tab.
Right now, you have this Apps for office microsoft.com library 1.
You are going to delete the 1 and type the word beta.
Alright, and then come back to Script like that.
OK now this video that you're watching right now.
Come down here to the description and click Show More.
There's the link to try out the Excel file.
There is the link to try the custom function.
Here's the one for an add-in.
Click that.
Here's the GitHub.
We don't need everything here.
Start at Row 9, very carefully, Row 9 and select down to row 158.
9 to 158 - so it ends with this Async function.
Control C to copy.
Switch back to Excel.
Paste.
Alright, we should be good to go now.
Come over into the grid anywhere in the grid.
And type what you're looking for.
Let's search for Power Query.
Back here in the Pane, we're going to choose, Run, Run in this pane.
And we get a button here that says Run.
Now look, ideally this Run button would be called Convert to Book or something like that and would be up here in the gallery.
Right now in ScriptLab, it's going to have to be a button over here.
I'll show you in a couple of minutes the add-in that we wrote that at least gets the button up onto its own tab.
All right, look at that.
Two data types were found, and they're both returned.
With the Add-in, when it's ambiguous, it will appear over here on the right hand side.
But right here with this ScriptLab, this is what we're going to get.
I can come here to Show Card.
You see that we're returning an array of authors.
I can return those to the grid.
I get both authors and they are entities.
Would it be nice to have that icon be a person icon?
It would, but Oh well.
I can even add the image.
Let's make the height of these taller.
And then here C3 I'm going to add a hash there to get the entire array.
And I'll get images of both authors.
This is almost everything that I wanted.
Ever since Ignite up until they gave us the ability to write the data entity to the grid, we've been doing this with a custom function.
And it's actually interesting - the custom function gives us a few things that the Add In doesn't.
Let me show you this.
I'm going to choose code and again we'll get rid of everything here.
The library can actually stay the way it is, we don't need the beta library.
In the YouTube description below, here's the GitHub example for using a custom function - Click.
This time very carefully, select from row 9 to row 137.
Control C. And paste.
We're going to Register this function.
So we have a function called GetBooks.
Check this out.
I'm going to put in what I'm searching for: pivot I can either look in title or body so I have two input cells there.
And then here a custom function =GetBooks.
It wants to know the keyword and where to search.
Press Enter.
Alright, so those are all the books that have pivot in the title.
If I then add the book image.
With a hash.
Make all of these taller.
And top align the text.
So now with the custom function version, this is actually pretty wild because I can come here, for example, type VBA.
Now go out to the API and return all the VBA books.
I can type VSTO.
I can type web.
Power query.
You know, in some ways this is better than the data types that we have in the gallery.
OK, now how about our goal of being able to have the search from the gallery like regular data types?
We don't quite have that, but we have something similar using a Visual Studio add-in.
This is 1000 lines of code and certainly more than I can explain here today, but it gives us something that feels like the task pane from regular data types.
We have a new tab now called Bookstore.
Alright, if I just come here and I want to search for VBA.
Stay in that cell.
Go to Bookstore and search by books.
And just like a regular data type, there's too many - it's ambiguous.
They ask me to choose which one I want.
I choose.
Select.
And there we are, that's perfect.
Or I can come here and type VSTO.
Now we only have one hit on so when I click search books.
It will automatically convert because there was one hit.
And we have the added benefit of coming over here.
And let's say search for pivot.
And click Search.
So I can keep adding more items just from the list.
Getting our own data in Data Types.
This gets us really close.
Closer than Power Query did.
Closer than Power BI did.
We havethree things we talked about today: the ScriptLab add-in, the function from ScriptLab, and the Visual Studio Add-In.
They are really checking a lot of boxes.
The From a Button is only going to be up in the Ribbon with the Visual Studio add-in.
And it is not going to be in the gallery, it is going to be on its own tab.
But being able to return an array, and get fields from the sub-entities, and returning an image from those, those are all really huge.
I can probably live without 5 and 6.
So kudos to the Excel team for this new JavaScript API for custom data types.
This, I think, is going to be hugely popular.
So many applications.
Imagine giving all of your sales reps an iPad, where they can just touch and pull up the pictures.
It is amazing.
Hey, December 20th 2021, I have some new courses on the Retrieve platform.
I love this platform.
They have artificial intelligence.
You type what you're looking for and will take you right to the spot in the video and you can read the transcript instead of watching the video.
So it's an amazing way to get content and we launch on December 20th, 2021.
If you like these videos please, down below Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank Suat for figuring all this out.
Thanks to Chris Gross and the team at Microsoft for offering it to us.
And I thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Let's hear you, Nancy.
Excel custom data types from the JavaScript API.
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
This is our third episode on the quest for a perfect custom data type.
We're talking about these data types that were introduced in Excel a few years ago.
They're awesome, but it's not our data.
It's Wolfram Data or Wikipedia data.
What we really need is to get our data into these. And so in the first two videos I showed how to do this with Power Query and then in the last video with Power BI.
But today I'm going to show you how to do this with the JavaScript API .
My goal for this is I want to be able to type plain text into a cell and convert it to a data type.
Ideally from the same gallery as the other data types.
From the card for that data type, I want to be able to return an array of entities.
That's this example that I showed you yesterday with Bob Marley.
If I come in and go to Members, so that is a table, and not just the table of text, but a table of entities.
So right here =A13.Member returns a list of names and each one is a person.
I can then click get the image: =B13#.Image That's my goal.
I want that to happen with my company data.
The data type should be able return an image from an image URL which power BI could do, but power query cannot.
The data type should be able to return a clickable clickable hyperlink and so far none of them can do that.
And it would be nice if I could customize the little icon so it was a person it was a person instead of just the built in icons.
This is Nirvana.
This is what I'm looking for.
They announced it Ignite, which was what November of 2021.
And we've been waiting ever since then for one extra piece to get released.
And finally on this past Friday it was released.
Check this out, I'm going to let you download this workbook right here.
There's nothing special.
There's no code.
These data types are here and they'll work everywhere.
I can send it to you and they will work.
They will even work on your iPad.
My test of “Can an entity return an array of entities?” So here we will go into MrExcel 2021 and I'll go to Author.
And then I want a list of books.
There are 61 items there.
I have not found a way to do this with Power BI.
If I come in and say, add that to the grid, I get the 61 books that I've written.
Here, let's try another one.
Ken & Miguel’s new book.
So we'll go in and I want to see the authors add the authors to the grid.
And then for each one.
Get the image.
So it's returning an image.
And I should be able to change that to B2 Hash.
Look at that.
So from the entity in A2 I'm returning an array of entities and then I can pull the image or other information out.
This is everything that I've been dreaming for.
If your IT department delivers an API that has your, let's say, company product list or something like that.
Just imagine how powerful that will be to give someone a spreadsheet and they can go in and investigate and so on.
It's simply amazing.
I'm going to show you the code that we use to create this.
And this is code that you can run, you yourself can run right now in the beta version of Excel.
The real way that you're going to want to do this is to create an Office Add in.
That's beyond the scope of this podcast.
But just using ScriptLab you can get something like this and send it on to other people in your company.
So I know some of you are not going to be interested in the technical details.
In fact, you should send the rest of this video to your IT department so they can start working on it now.
But for those of you who want to dive in.
Let's dive in.
Now look as you test the spreadsheet today and you want to try to build your own.
You're more than welcome to use our data.
But the only thing less interesting than Wikipedia data is my product data.
You're going to need your IT department, your web person, to build out a back end to get your data.
In the meantime, feel free to use ours, but show them this.
The first step is getting the back end built.
And for us, we already have an application on our website that has a database that drives these books.
And a second data table by author.
So it was up to our web team to take this data and convert it to an API.
The API sends into here, looking for a book, title of VBA or title of Pivot, let's say.
And it returns a huge JSON string.
I can take this data into a JSON online validator.
And, we purposely built this to not look exactly like it needs to look in Excel.
That way, we would have to do some work in ScriptLab or or in Visual Studio to reformat things.
But step one is to get your company web team to build out this API.
Alright lets head back to Excel where we can install ScriptLab.
Insert tab, Get Add-Ins.
Search for a ScriptLab.
Right there that.
Click Continue.
First, click on ScriptLab in the ribbon.
We want to create new Code.
This pane will appear over here on the right hand side with a template.
But we're not going to use their template, so select everything and delete it.
On the libraries tab.
Right now, you have this Apps for office microsoft.com library 1.
You are going to delete the 1 and type the word beta.
Alright, and then come back to Script like that.
OK now this video that you're watching right now.
Come down here to the description and click Show More.
There's the link to try out the Excel file.
There is the link to try the custom function.
Here's the one for an add-in.
Click that.
Here's the GitHub.
We don't need everything here.
Start at Row 9, very carefully, Row 9 and select down to row 158.
9 to 158 - so it ends with this Async function.
Control C to copy.
Switch back to Excel.
Paste.
Alright, we should be good to go now.
Come over into the grid anywhere in the grid.
And type what you're looking for.
Let's search for Power Query.
Back here in the Pane, we're going to choose, Run, Run in this pane.
And we get a button here that says Run.
Now look, ideally this Run button would be called Convert to Book or something like that and would be up here in the gallery.
Right now in ScriptLab, it's going to have to be a button over here.
I'll show you in a couple of minutes the add-in that we wrote that at least gets the button up onto its own tab.
All right, look at that.
Two data types were found, and they're both returned.
With the Add-in, when it's ambiguous, it will appear over here on the right hand side.
But right here with this ScriptLab, this is what we're going to get.
I can come here to Show Card.
You see that we're returning an array of authors.
I can return those to the grid.
I get both authors and they are entities.
Would it be nice to have that icon be a person icon?
It would, but Oh well.
I can even add the image.
Let's make the height of these taller.
And then here C3 I'm going to add a hash there to get the entire array.
And I'll get images of both authors.
This is almost everything that I wanted.
Ever since Ignite up until they gave us the ability to write the data entity to the grid, we've been doing this with a custom function.
And it's actually interesting - the custom function gives us a few things that the Add In doesn't.
Let me show you this.
I'm going to choose code and again we'll get rid of everything here.
The library can actually stay the way it is, we don't need the beta library.
In the YouTube description below, here's the GitHub example for using a custom function - Click.
This time very carefully, select from row 9 to row 137.
Control C. And paste.
We're going to Register this function.
So we have a function called GetBooks.
Check this out.
I'm going to put in what I'm searching for: pivot I can either look in title or body so I have two input cells there.
And then here a custom function =GetBooks.
It wants to know the keyword and where to search.
Press Enter.
Alright, so those are all the books that have pivot in the title.
If I then add the book image.
With a hash.
Make all of these taller.
And top align the text.
So now with the custom function version, this is actually pretty wild because I can come here, for example, type VBA.
Now go out to the API and return all the VBA books.
I can type VSTO.
I can type web.
Power query.
You know, in some ways this is better than the data types that we have in the gallery.
OK, now how about our goal of being able to have the search from the gallery like regular data types?
We don't quite have that, but we have something similar using a Visual Studio add-in.
This is 1000 lines of code and certainly more than I can explain here today, but it gives us something that feels like the task pane from regular data types.
We have a new tab now called Bookstore.
Alright, if I just come here and I want to search for VBA.
Stay in that cell.
Go to Bookstore and search by books.
And just like a regular data type, there's too many - it's ambiguous.
They ask me to choose which one I want.
I choose.
Select.
And there we are, that's perfect.
Or I can come here and type VSTO.
Now we only have one hit on so when I click search books.
It will automatically convert because there was one hit.
And we have the added benefit of coming over here.
And let's say search for pivot.
And click Search.
So I can keep adding more items just from the list.
Getting our own data in Data Types.
This gets us really close.
Closer than Power Query did.
Closer than Power BI did.
We havethree things we talked about today: the ScriptLab add-in, the function from ScriptLab, and the Visual Studio Add-In.
They are really checking a lot of boxes.
The From a Button is only going to be up in the Ribbon with the Visual Studio add-in.
And it is not going to be in the gallery, it is going to be on its own tab.
But being able to return an array, and get fields from the sub-entities, and returning an image from those, those are all really huge.
I can probably live without 5 and 6.
So kudos to the Excel team for this new JavaScript API for custom data types.
This, I think, is going to be hugely popular.
So many applications.
Imagine giving all of your sales reps an iPad, where they can just touch and pull up the pictures.
It is amazing.
Hey, December 20th 2021, I have some new courses on the Retrieve platform.
I love this platform.
They have artificial intelligence.
You type what you're looking for and will take you right to the spot in the video and you can read the transcript instead of watching the video.
So it's an amazing way to get content and we launch on December 20th, 2021.
If you like these videos please, down below Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank Suat for figuring all this out.
Thanks to Chris Gross and the team at Microsoft for offering it to us.
And I thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Let's hear you, Nancy.
