►
Description
In this 17-minute developer-focused demo, Sudhi Ramamurthy opens with key enhancements to APIs, Recorder and Admin-Control for Office Scripts. Customer Leslie Black then shows how to use Office Scripts to build complex functionality within an Excel Online workbook- a TICTACUFO application he created. He uses Power Automate Flow to capture trigger events. Because Excel Office Scripts is integrated with Power Automate, it opens a world of Azure Cloud Services and data sources available for your solutions. 20 functions are used in this single script file. This PnP Community demo is taken from the Office Add-ins community call – April 2021, recorded on April 14, 2021
Demo Presenters:
• Leslie Black (Analysis Cloud Ltd.) | Microsoft Applications Specialist | les@analysis.ie
• Sudhi Ramamurthy (Microsoft) | Sr Program Manager
Supporting documents:
• Repo - TICTACUFOGame | https://github.com/MrAnalyticals/OfficeScripts/tree/main/TICTACUFOGame
• 2 min Video - UFO TicTacToe Office Scripts Game | https://youtu.be/FMOCPm1aV_Q
• 11 min Video - TicTacToe Code Review | https://youtu.be/F49hwCzpygQ
• LinkedIn Group - Office Scripts LinkedIn Group | https://www.linkedin.com/groups/9016822/
• Documentation - Add comments in Excel | https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/add-excel-comments
• Documentation - Office Scripts in Excel on the web (preview) | https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel
Stay connected:
• Twitter https://twitter.com/microsoft365dev
• YouTube https://aka.ms/M365DevYouTube
• Blogs https://aka.ms/M365DevBlog
A
My
name
is
sudhi
ramamurthy,
I'm
one
of
the
program
managers
on
the
office
platform
team.
I
mainly
I'm
focusing
on
office
scripts.
Now
so
anytime.
We
get
an
opportunity
to
come
to
talk
to
our
add-in
friends
about
office
scripts.
So
we're
really
glad
so
thanks
for
this
opportunity,
so
my
part
of
today's
talk
is
gonna,
be
fairly
short.
I
know
in
this
forum,
I
think,
maybe
about
a
couple
of
months
ago.
I'd
done
a
short
presentation
about
what
office
scripts
is
and
how
we
can
use
it
today.
A
So
just
to
recap
what
office
scripts
is.
So
it
is
a
new
application
within
excel
for
web
and
it
basically
allows
end
users
to
automate
their
tasks.
They
create
tasks
within
excel
for
web,
so
you
can
access
it
today.
So
if
you
have
office,
365
account
can
go
to
any
file,
that's
stored
on
either
sharepoint
a
onedrive
and
you'll
be
able
to
access
this.
You
do
need
e3
arabao.
A
B
B
A
So
awesome,
so
the
key
thing
I
want
to
call
out
is
it's
easy
access,
no
configuration,
no
setup
and
recorder
is
really
sort
of
your
first
key
step
towards
not
only
converting
your
actions
to
script,
but
also
to
learn
and
discover
apis
and
how
to
use
it.
Okay.
So
the
second
part
I
wanted
to
highlight
is
the
the
usage
of
power
automation.
A
A
Now
the
cool
thing
is,
you
can
now
connect
excel
with
the
rest
of
the
business
using
a
run
script
action
under
excel
online
and
it
will
run
the
same
script
that
you
may
develop
in
the
the
excel
for
web
and
you
can
run
the
same
script
and
you
can
send
data.
You
can
return
data
from
the
script
and
perform
excel
calculations,
formatting
data
extraction,
chart
or
image
extraction,
and
things
like
that.
A
So
the
third
thing
I
want
to
talk
about
is
the
enhanced
admin
control,
so
admins
need
control
over
what's
happening
with
their
scripting
framework,
who's
accessing
what
sort
of
scripts
are
being
run
and
what
are
the
final
level
controls
that
they
may
wish
to
offer
today
within
office
scripts?
We
offer
three
final
level
controls.
A
One
is
you
know
whether
you
can
have
office
skips
ability
to
use
office
scripts
or
not,
and
you
can
do
that
at
everybody
level
or
like
a
specific
group
level
as
well,
and
then
whether
a
user
gets
to
share
the
script
with
their
colleagues
again
within
within
the
organization.
Scripts
cannot
be
shared
outside
and
the
the
new
one
that
we've
introduced
is
the
ability
to
control
who
gets
to
run
the
office
scripts
in
the
power
automation.
A
So
that's
something
new
that
we've
introduced
lately
and
this
is
sort
of
a
journey
that
we're
on.
We
will
continue
to
offer
more
fine
grain
help
as
and
when
we
introduce
new
features,
and
the
fourth
thing
is
the
office
scripts
api.
So
we've
created
a
new
simplified
interface
for
office,
skips
users
and
you
can
easily
automate
using
a
very
simplified
interface
and
the
key
thing
is:
it
can
be
invoked
synchronously,
so
you
don't
have
to
deal
with
promises
or
callbacks.
A
In
addition
to
those
simplified
interfaces,
we've
also
worked
on
improving
range
and
table
related
interactions,
especially
around
not
having
to
use
the
two-dimensional
arrays
when
you're
dealing
with
you
know
rows
and
columns
of
a
table
or
a
cell,
because
those
tends
to
be
somewhat
harder
pattern
for
of
a
scripts
users
to
use.
So
we
made
it
really
easy
easy
for
apis
to
be
consumed
so
in
terms
of
the
key
features.
So
first
is
the
api
and
we've
continually
been
adding
new
apis
and
we'll
continue
to
do
so.
A
Recent
additions
are
like
range
edge
which
able
to
detect
your
keyboard
moment.
Control
key
movement
range
spilling
curtain,
pace
that
you
just
saw
on
table
resize,
so
this
is
again
a
journey
that
we
are
on
then,
on
the
recorder
and
the
right
pane.
We
are
making
more
enhancements
to
show
contextual
messages
and
concise
messages,
rather
than
sequential
messages.
So
this
is
just
to
help
relate
your
recording
to
the
script
and
to
the
action
message
that
you
see
on
the
right
side
and,
lastly,
the
admin
control.
A
I
already
spoke
to
that
about
adding
power,
automation
controls,
so
that
is
just
a
quick
overview
and
I
wanted
to
call
out
that
recently
our
content
team
has
published,
I
think,
over
a
dozen
advanced
samples,
and
that
has
complete
code
as
well
as
the
input
files
that
you
can
use,
and
many
of
them
have
a
full
length.
Video,
so
we'll
continue
continue
to
add
more,
but
these
set
of
samples
will
give
you
a
lot
of
business
scenarios
that
you
can
use
today
and
apply
it
in
your
work,
environment
and
save
time.
A
So,
with
that
brief
introduction,
I
will
pass
the
control
over
to
leslie
and
leslie
has
been
doing
an
amazing
work,
not
only
in
creating
business.
Critical
applications
using
office
scripts,
but
he's
also
been
super
active
in
the
community
as
well,
and
the
most
interesting
part
I
find
about
him
is
he
develops
and
and
creates
new
ways
to
learn
a
platform
in
a
very
interactive
and
a
fun
fashion.
So,
hopefully
you'll
get
to
see
some
of
that
work
today.
So
leslie.
If
you're
able
to
take
control.
C
I've
recently
finished
working
with
the
european
commission
in
belgium,
building
a
large
scale,
100
cloud-based
covert,
reporting
application
to
enable
them
to
monitor
cooper
in
europe.
It
was
built
using
azure
power
platform
and
office.
365
office
scripts
was
used
as
a
central
controlling
component
of
that
application,
and
that
is
where
I
learned
to
use
of
a
scripts
as
well
as
many
of
the
other
tools
mentioned
there
as
well.
C
The
key
messages
for
this
demo
are
think
of
excel
online
as
a
calculation
engine
available
to
assist
your
built
online
apps.
So
I
appreciate
you
are
adding
developers,
but
excel
should
be
considered
as
a
calculation
engine
which
is
available
for
your
your
apps,
complex
automation,
tasks
can
be
written
using
offer
scripts.
C
The
power
automate
can
reduce
that
complexity
by
handling
the
logic.
Instead,
it
is
your
choice.
Excel
online
has
the
power
and
capability
to
be
used
as
a
front-end
user
interface.
For
your
web-based
applications,
though,
there
are
limitations
to
that
which
I
shall
come
on
to
shortly,
because
excel
of
a
scripts
is
integrated
with
power
automate.
It
opens
up
the
whole
world
of
azure
services
and
data
sources
available
for
your
solutions,
the
interface
for
the
game,
as
you
can
see
it's
in
excel
online.
C
C
You
can
specify
that
xl
plays
first
by
putting
a
one
in
cell
m6
or
you
can
clear
the
board
and
start
a
new
game
by
entering
a
one
in
cell
p8,
so
we
have
yellow
cells.
There
enter
signifying
where
you
can
enter
data
when
you
enter
marker
into
the
grid
excel
responds
by
putting
an
x
marker
into
an
available
space
in
the
grid.
C
C
So
here
we
have
the
power
automate
flow
for
this
application.
It
contains
only
three
connectors.
We
have
the
trigger
connector,
which
is
sharepoint
when
a
file
is
created
or
modified
in
a
folder,
and
we
have
two
action
steps,
a
condition
and
an
excel
run
script.
So
we're
testing.
If
the
file
has
the
name
tic-tac-toe
in
it,
because
excel
currently
has
only
one
trigger
connector
in
power
domain,
which
is
not
applicable
to
the
scenario,
it
leaves
only
the
sharepoint
file
trigger
to
us
as
the
application's
event
trigger
this
means
any
change.
C
C
So
when
a
marker
or
any
other
change
occurs
in
the
worksheet,
the
script
needs
to
ascertain
where
that
change
occurred
and
what
type
of
change
occurred.
This
is
done
by
testing
the
values
within
the
grid
and
cells,
m6
and
p8
time
for
power
automate
to
respond
to
the
entering
for
marker
is
in
addition
to
the
time
it
takes
for
the
script
to
run.
The
script
takes
on
average
around
70
seconds.
C
C
C
C
C
With
the
excel
macro
recorder.
Building
solutions
is
now
extremely
rapid.
The
animation
element
of
this
app
was
built
in
less
than
half
a
working
day,
that
is
1977
lines
of
typescript
code
was
written
in
less
than
four
hours
and
again
that
was
using
the
macro
recorder
which,
as
you're
probably
familiar
with,
is
very
similar
to
the
vba
macro
recorder.
C
So
here's
a
screenshot
of
the
game
being
built
and
we
can
see
some
script
here
that
was
generated
by
the
macro
recorder.
So
in
this
single
script
file,
there's
a
total
of
20
functions,
including
the
main
function.
The
main
function
is
369
lines,
long,
excluding
the
lines
of
code
that
control
the
animation.
The
total
number
of
lines
of
code
is
960..
C
C
Here
is
the
full
list
of
the
functions.
Those
functions
used
for
the
animation
have
been
marked
with
a
star.
The
get
range
coint
function
counts.
The
total
number
of
markers
entered
in
the
grid,
the
find
location
function
iterates
through
the
grid.
Finding
a
cell
that
is
not
listed
in
table
1
table
1
is
used
to
record
the
locations
of
the
entered
markers
and
the
order
in
which
they
are
added
to
the
grid.
It
is
hidden
from
user
view.
C
C
The
current
number
of
markers
added
to
the
grid
is
compared
to
the
number
added
to
table
1..
Thus
the
code
can
ascertain
whether
a
human
or
excel
moved
last
when
excel
enters
a
marker
table.
1
is
not
updated,
whereas
when
a
human
enters
a
marker,
the
code
does
update
table
1.,
so
I
am
in
effect
using
the
excel
worksheet
as
a
location
to
store
global
variables
between
script
runs.