►
From YouTube: Excel data type APIs
Description
In this 15-minute Developer-focused demo, Chris Gross invites you to join the Excel teams’ journey of Data Types. Review data sources, data types (entity values), Card anatomy (Icon, Display text, Field, Field values, Dot Notation, Extract to grid), and Composable Building Blocks. Learn 3 steps to Increasing Developer Velocity and about New Values API (Range.valuesAsJson) preview that supports new data types with JSON schema. See demo of Data Types in Custom Functions (Preview). Feedback requested. This PnP Community demo is taken from the Office Add-ins community call – February 2022, recorded on February 9, 2022.
Demo Presenter: Chris Gross (Microsoft)
Stay connected:
• Learn more about the Microsoft 365 Patterns and Practices community at: http://aka.ms/m365pnp
• Visit the Microsoft 365 Patterns and Practices community YouTube channel: https://aka.ms/m365pnp/videos
A
Hello:
everyone,
my
name,
is
chris
gross,
an
excel
program
manager,
so
I'll
be
talking
to
you
guys
today
about
data
type
apis
as
promised.
So
I'm
going
to
share
my
screen
here
and
then
get
things
kicked
off,
got
about
15
minutes
and
I've
got
more
content
than
I
could
possibly
get
through.
So
I'll
try
to
do
this
in
an
expedited
version
and,
of
course,
always
happy
to
answer
questions
afterwards
in
the
chat
I'll
be
talking
to
you
all
about
data
types.
A
The
first
thing
to
kind
of
mention
is
just
the
journey
that
the
excel
team's
been
on
in
regard
to
data
types,
so,
historically
speaking,
excel
supported
what
we
now
call
a
number
of
basic
values
and
those
have
been
things
that
you're,
probably
familiar
with
like
text
text,
strings
numbers,
booleans
and
then
there's
a
formatting
layer.
On
top
of
that-
and
you
know,
the
entire
world
has
been
running
and
business
is
operating
off
of
all
of
these
formulae
that
make
use
of
these
basic
data
types.
A
We
started
on
the
journey
recently
of
adding
urals
data
sources
too,
and
so,
if
you
have
power
bi,
you
can
hook
that
in
through
what
we
call
organizational
data
and
then
there's
also
the
ability
for
power.
Query.
But
really
we've
been
missing
something
and
it's
been
something
that
we've
been
excited
to.
A
So
the
next
thing
I
want
to
cover
real
quick
is
just
a
bit
of
the
anatomy
about
data
types,
because
I'll
use
some
terms
today
just
loosely
and
that's
because
I
breathe
this
stuff
every
single
day
and
I'm
constantly
talking
about
it.
But
it
might
be
something
that
you're
not
familiar
with.
So
if
I
use
a
term
these
are
generally
what
they
mean.
So
the
first
thing
is
when
you
look
at
a
data
type-
and
specifically
these
are
called
entity
values
in
the
grid,
you'll
notice
that
they
have
an
icon
and
then
some
display
text.
A
These
are
things
that
you
can
extend
and
we'll
get
more
to
that
in
a
little
bit
later
and
then
the
other.
The
other
main
surface
area
that
you'll
users
are
interacting
with,
is
what
we
call
the
card,
probably
a
little
bit
self-explanatory
of
a
name,
but
it's
worth
at
least
calling
out
now
within
those
cards,
you
might
be
able
to
notice
that
there's
a
whole
bunch
of
information
contained
with
them
or
metadata.
A
So
we
call
these
things,
fields
and
field
values,
and
these
are
really
again
part
of
what's
important
to
excel,
because
it's
awesome
that
I
can
have
a
card,
but
when
you're
an
end
user,
you're-
probably
going
to
want
to
make
use
of
this
within
the
grid,
so
that
you
can
do
your
own
analysis.
So
we
have
a
number
of
ways
that
you
can
actually
extract
that
data
as
an
end
user
and
get
it
to
the
grid,
whether
that
be
through
the
on
object.
A
And
that's
not,
you
know
the
only
supported
things.
We
also
have
deep
supported
integration
with
many
of
the
feature
sets
so
right
here.
What
you're
seeing
is
kind
of
a
quick
example
of
our
ability
to
have
certain
filtering
functionality,
that's
just
built
in
baked
into
the
product
and
if
you
create
an
entity
value,
it's
just
generally
supported
within
those
infrastructures
too.
A
A
We
saw
a
number
of
patterns
over
the
years
of
things
that
folks
are
trying
to
do
either
through
the
spreadsheets
themselves
or
building
add-ins
to
do,
and
so
we
wanted
to
allow
the
ability
to
kind
of
get
the
job
done
a
little
bit
faster,
and
so,
if
you're
thinking
about
making
use
of
these
apis,
it's
rather
simple
to
do
all
you
need
to
do.
Is
you
need
to?
A
For
example,
if
you
have
a
rest
api,
you
can
return
a
json
structure
and
then
we
have
a
documented
schema
and
if
you
apply
that
schema
to
your
data,
then
all
you
have
to
do
is
set
those
values
and
pass
them
into
the
grid.
And
it's
really
as
simple
as
that
and
I'll
show
a
few
examples
today.
That
kind
of
show
a
little
bit
about
how
that
works.
A
Now
it's
also
important
to
talk
about
the
different
building
blocks
that
are
exposed,
because
you
know
I
certainly
talked
a
lot
about
that
entity.
Value
structure
because
that's
kind
of
the
main
one
that
comes
top
of
mind
when
you
think
about
data
types,
but
we've
also
added
support
for
a
number
of
different
other
composable
building
blocks
that
we've
built
along
the
way
that
support
all
these
experiences.
A
If
somebody
say
formats,
a
dollar
amount
as
a
date
when
they
didn't
mean
to
images
inside
the
grid
are
another
thing
that
we've
added
support
for,
so
we've
always
had
the
ability
to
have
stuff
floating
on
the
grid,
but
within
those
entity
values
you
might
have
noticed
that
there
were.
There
were
images
inside
of
there,
and
so
these
images
can
live
on
their
own
or
inside
of
these
entity
values,
and
that's
true
of
any
of
these
things
they
can
all
be
composable
within
each
other,
as
it
makes
sense.
A
A
A
This
is
very
much
like
range.values
if
you've
used
it
before,
except
the
only
difference
is
that
it
returns
augmented
information
about
the
historical
values,
but
it
also
has
support
for
these
new
data
types.
So
in
this
example
on
the
bottom
you
can
see
if
before
I
was,
writing
something
that
had
one
and
then
the
term
llama,
because
I
couldn't
actually
put
an
image
of
a
llama
in
the
cell.
A
Well
now
I
can
do
that
or
I
could
add
my
own
structure
to
if
I
wanted,
and
really
this
allows
you
forget
to
get
in
set
values
within
the
grid.
Just
like
range
top
values
always
has
a
little
bit
about
the
schema,
because
it
would
be
a
miss
to
not
at
least
touch
base
on
that.
To
give
you
a
kind
of
an
idea
of
how
it
works,
they
have
a
schema
in
json's
the
format,
probably
as
implied
from
the
api
name.
A
The
main
thing
that
drives
a
lot
of
these
definitions
is
what
we
call
the
type
field,
and
so
within
that
we've
added
we've
added
enumeration
where
you
can
actually
go
and
quickly
access
and
see
the
different
types
of
type
values
you
can
set.
So
here
you
can
see,
we've
got
those
objects.
I
talked
about
before
of
web
image,
formatted
number
values
and
then
entities,
and
then,
of
course,
there
are
special
properties
that
go
with
each
of
those.
A
A
It's
something
that
everyone
makes
use
of
every
single
day
when
they
want
to
do
their
analysis,
and
you
all
are
probably
familiar
with
custom
functions
and
their
ability
to
be
extended.
All
those
data
types
that
I
showed
you
are
generally
supported
within
custom
functions
too,
and
so
another
thing
I
kind
of
wanted
to
show
was
a
little
bit
about
how
your
journey
of
doing
analysis
in
excel
can
be
transformed
and
augmented,
if
you're
an
end
user
by
making
use
of
data
types.
A
So
this
was
for
a
demo
that
we
actually
built
at
ignite,
but
historically
speaking,
you'll
notice
that
generally
people
have
flat
tabular
data,
so
you've
got
a
whole
bunch
of
columns
with
rows
in
them
and
that's
how
you
represent
your
data
with
something
like
data
types.
You
can
actually
package
that
up
into
one
singular
entity
and
again
functions
are
another
good
way
of
possibly
creating
these,
as
well
as
maybe
building
your
ui,
but
in
this
case
you'll
actually
watch.
It
was
actually
phoebe
who
created
this
demo.
A
You'll
watch
her
kind
of
create
her
own
custom
function
and
then
pass
in
a
whole
bunch
of
information
and
then,
from
this
custom
function,
we're
actually
able
to
build
those
structures
that
you
can
kind
of
see
listed
here
and
again,
there's
a
picture
of
that
array
value
one
that
I
was
mentioning.
So
I
think
it's
pretty
slick
because
it
just
kind
of
shows
you
all
the
information
at
a
you
know
at
a
really
brief
moment's
notice,
and
if
you
want
to
extract
any
individual
value,
the
interactions
are
just
kind
of
built
in.
A
So
the
last
thing
I'm
going
to
leave
with
today
is
a
quick
demo.
We've
added
some
additional
metadata
to
the
data
types
in
particular,
and
so
I'm
going
to
show
you
how
we've
added
data
types
that
allow
you
to
set
some
card
specific
properties,
because
you
might
have
noticed
that
the
card
layouts
don't
look
like
some
of
the
other
ones
that
you
can
see,
for
example,
on
the
wolfram
side,
and
then
there's
also
the
ability
to
hide
individual
fields
from
the
client
and
I'll
show.
Some
of
that
too.
A
So
I'm
actually
going
to
flip
over
to
excel
where
I've
got
a
pre-curated
demo
and
I'm
going
to
be
making
use
of
a
tool
that
we've
built
today.
To
kind
of
show
you
a
little
bit
about
how
this
structure
works
really
quick
about
this
tool.
What
it
does
is,
it
allows
me
to
set
and
clear
data
types
and
kind
of
look
at
all
of
that
information-
that's
contained
within
there
and
modify
it.
So
that's
what
I'll
be
using
today.
So
first
thing
is
just
a
general
look
at
the
structure,
so
I've
created
a
fake
entity.
A
Here
it's
meant
to
be
of
myself
and
just
purely
illustrative,
but
you
can
see
one
of
the
first
things
that
might
jump
out
to.
You
is
the
additional
card
sections
that
we
have
here
so
before
you
didn't
have
those
card
sections
and
everything
was
just
kind
of
a
flat
list
and
laid
out,
and
you
also
couldn't
organize
the
individual
properties
and
I'll
talk
a
little
bit
about
that
too.
You
can
see.
I've
got
some
general
information
here
about
my
favorite
stuff
and
then
maybe
some
contact
information.
A
Now.
One
of
the
things
I
can
do
is
this
profile
picture
here.
This
is
actually
shown
in
line
and
really
I
want
that
to
be
at
the
top
and
that's
a
field
that
we've
introduced
under
a
new
card
layout
property.
That's
called
the
hero
image.
So
what
I'll
do
in
this?
This
case
is
I'm
just
going
to
click
this
button
to
make
that
profile.
The
actual
main
image
oh
and
the
other
thing
to
mention
that
I
forgot
to
sorry-
was
the
sub
label.
A
There,
you'll
notice,
it
says
profile
and
there's
this
dot
and
it
says
picture
every
single
field
also
has
the
addition
of
something
we
call
the
sub
label
that
you
can
set
to,
but
anyway,
I
want
to
make
this
the
main
image.
So
what
I'll
do
is
I'll
set
this
and
don't
mind
the
the
rendering
issue
I'm
having
today,
I've
got
my
own
special
builds
of
excel,
and
so
I've
got
bugs
that
come
with
that.
A
But
the
thing
to
notice
is
that
that
image
is
now
actually
located
at
the
top
of
the
card,
as
opposed
to
inline,
where
it
was
before,
which,
in
our
opinion,
gives
a
much
richer
experience
and
kind
of
looks
a
lot
better.
In
my
opinion
than
than
just
kind
of
inline
or
some
of
the
other
ways,
you
might
do
it.
A
One
of
the
other
things
that
we
changed
was
the
ability
to
order
the
fields
within
the
card.
So
if
you've
tried
this
api
before
you
might
have
noticed
that
we
always
sort
things
alphabetically,
we
have
another
field,
you
can
actually
change
the
structure
of
it.
So
in
this
case
let's
say
I
want
to
make
the
the
animal
my
favorite
animal
is
one
of
the
top
properties,
so
I
just
click
that
button
it'll
move
it
to
the
top.
Now,
if
I
set
that
data
again,
we
should
see
the
animals
listed.
A
The
last
thing
I'm
going
to
demo
is
some
of
the
interactions
I
said
about
setting
metadata
to
hide
things.
So,
for
example,
you
might
be
doing
something
like
pulling
a
whole
bunch
of
information
from
a
database
and
there's
information
that
you
might
want
available
to
you
as
you're
debugging,
but
you
don't
necessarily
want
to
make
available
to
the
end
user.
You
still
want
it
in
the
payload,
but
you
don't
want
it
to
be
readily
accessible.
A
So
that's
where
these
set
of
fields
are
going
to
come
in
the
first
one
is
one
called
card
view.
So
if
I
uncheck
this
and
hit
set
again
you'll
notice
that
I
can
actually
hide
that
field
from
card
view,
so
it
won't
display
in
the
card
view.
But
if
I
were
to
go
over
here
and
use
dot
notation
it's
still
available
and
accessible.
If
I
wanted
to
extract
it
into
the
grid,
but
let's
say
you
don't
want
that
to
to
be
extractable
to
the
grid
or
let's
say
you
don't
want
it
to
auto
complete.
A
A
Notice
that
I
don't
have
it
listed
here,
but
if
I
actually
call
this
it'll
still
be
extractable
and
then
of
course
the
final
thing
is,
you
can
also
remove
it
from
dot
notation
altogether.
So
this
action
once
I
click
that
we
should
actually
see
me
now
get
a
pound
field
error
because
excel
is
not
going
to
allow
that
to
be
extracted
to
the
grid.
It's
still
contained
in
there,
but
excel
is
not
it's
not
going
to
be
accessible
to
the
end
user
shown
in
the
ui
or
anything.
A
You
guys
think
are
interesting
or
useful
to
to
your
given
scenarios
and,
of
course,
we're
also
actively
looking
for
feedback.
So
when
you
make
use
of
these
apis
since
they
aren't
just
currently
in
preview
right
now,
we
want
to
make
sure
that
we're
building
the
right
thing
and
that
we're
building
out
new
apis
to
enable
any
scenarios
that
you
might
have
need
for.
So
that
kind
of
covers
everything
I
had
today
and
thanks
for
listening.
B
You
know
we're
moving
from
you
know
just
having
simple
data
types.
Now
you
can
work
with
really
complex
data
types
from
your
add-ins.
I
was
just
curious.
Do
you
want
folks
to
like
drop
an
email
or
something
if
they
want
to
connect
with
you
in
the
chat
or
because
you
mentioned
you
want
to
connect
emails.
A
Yeah,
the
email
email's,
probably
the
best
way.
You
know
my
email
is
ch
gross
at
microsoft.com.
So
if
you
want
to
send
me
an
email
go
for
it,
but
yeah,
I'm
looking
for
we're,
looking
for
any
sort
of
feedback
or
communications
and
happy
to
set
up
calls
and
talk
about
things
with
any
of
the
partners.
Today,.