►
Description
In this 15-minute all user-focused demo, Damien Bird steps through an automated invoice generation scenario using Excel scripts, Power Automate and no tables in Excel. Solution elements include 2 lists containing data and an Excel invoice template with scripts in SharePoint + a cloud flow in Power Automate. Flow actions pull data from lists, run script in template to populate an Excel file for each customer, creates pdf invoices. Excel invoices stored in document library, PDF files in OneDrive. This PnP Community demo is taken from the Office Add-ins community call – May 2021, recorded on May 12, 2021.
Demo Presenters:
• Damien Bird (DamoBird365) | @DamoBird365
• Nancy Wang (Microsoft) | @nancyatMSFT
Supporting documents:
• Website - Microsoft 365 Dev Center | https://developer.microsoft.com/en-us/office-scripts
• Documentation - Use Office Scripts and Power Automate to email images of a chart and table | https://docs.microsoft.com/office/dev/scripts/resources/samples/email-images-chart-table
• Documentation - Create a cloud flow in Power Automate | https://docs.microsoft.com/en-us/power-automate/get-started-logic-flow
• Blog - Populate an Excel Template (Invoice) dynamically using Office Scripts and Power Automate | https://www.damobird365.com/perfectly-complete-an-excel-invoice-template/
• YouTube Channel - DamoBird365 | https://www.youtube.com/channel/UC-NCKrEw6CM8fidaIk-yrUQ
Stay connected:
• Twitter https://twitter.com/microsoft365dev
• YouTube https://aka.ms/M365DevYouTube
• Blog https://aka.ms/m365pnp/community/blog
A
Yeah,
so
I'm
really
excited
to
be
here
with
damian
today,
talking
about
office
scripts
with
power,
automate
damien
has
a
really
cool
demo
of
how
you
can
use
power,
automate
and
javascripts
together
to
populate
an
excel
template,
but
that's
all
I'll
say
because
I
think
he's
done
a
really
great
job
with
it
and
I
don't
want
to
spoil
all
the
surprises.
Just
as
a
quick
recap
before
I
go
on
to
the
next
slide.
Office.
A
A
A
B
Perfect
thanks
nancy
for
the
wii
intro.
If
I
just
jump
into
my
my
opening
slide
so
today,
I
just
want
to
say
first
of
all
thanks
very
much
david
and
nancy
for
inviting
me
along
and
thanks
for
the
opportunity
to
present
my
name
is
damian
bird
as
you've
heard
already
I'm
available
on
all
the
social
media
platforms
as
demo
bird
365,
I'm
very
much.
A
community
developer
active
on
the
power
platform
community
site
and
in
march,
was
the
top
non
super
user
so
feel
free
to
reach
out
and
connect.
B
B
So
the
purpose
of
my
use
case
is
really
to
demonstrate
how
you
can
populate
excel
files
using
both
power,
automate
and
excel
scripts,
without
the
need
for
tables
in
excel,
which
is
the
the
current
limitation
of
the
the
cloud
flow
actions
at
the
moment.
So
I'm
about
to
demonstrate
invoicing,
but
equally
you
could
use
it
for
issue
tracking
or
time
cards
or
money
management.
B
So
I've
seen
lots
of
good
examples
of
excel
scripts
to
perform,
perform
functions
that
are
not
native
to
power
automate.
But
what
I'll
do
now
is
I'll
jump
onto
my
demonstration
and
one
of
the
key
things
for
you
to
maybe
do
on
your
own
pcs.
Right
now
is
you'll
see
a
url
on
that
slide.
I've
got
to
excel
demo.
B
B
So
here
I
am
on
the
the
page
that
I've
asked
you
to
look
at
and
there's
a
series
of
links
here,
but
we'll
move
on
to
that
later
on,
so
I've
got
here
a
sharepoint
site
and
to
to
make
things
more.
Visual.
I've
got
a
couple
of
lists,
and
so
I've
stored
the
data
for
the
the
customers
into
a
list.
Now,
in
a
real
life
scenario,
you
might
be
using
a
sql
database
or
or
the
dataverse
to
store
this
data,
but
I
think
for
this
demo,
it
just
makes
it
easier
to
to
visualize.
B
B
We're
going
to
populate
the
unique
fields
here
for
for
the
company,
there's
going
to
be
an
invoice
number
and
invoice
date,
various
other
cells
and
then
the
sort
of
more
interesting
bit
are
these
individual
lines
that
relate
to
the
the
data
that
is
in
those
tables
and,
like
I
say
that
the
data
that's
in
those
those
lists.
I
I've
used
it
as
a
visual
in
a
real
life
scenario:
you're
going
to
have
a
front
end
system
somewhere
capturing
this
transactional
information,
but
it's
the
the
flow
that
does
the
the
clever
work
with
excel
scripts.
B
I
will
I'll
jump
onto
the
the
automate
tab
as
well
and
just
open
up
the
the
script
before
I
jump
onto
my
flow,
so
I
was
lucky
enough
to
have
the
excel
team.
Look
at
my
script.
I'm
not
a
javascript
programmer
or
a
typescript
programmer,
but
I
was
able
to
build
this
solution
entirely
by
myself.
Just
by
reading
the
content
that's
online
and
relying
on
some
of
the
material
that
microsoft
have
already
put
together
on
the
the
developer
site
so
jumping
into
the
script
you
can
see
here.
B
I've
defined
a
series
of
variables
that
are
used
in
the
call
to
the
script
in
the
cloudflow.
So
you'll
see
these
things
later
on
things
like
the
invoice
invoice
number
the
date,
the
payment
method
and
check
number.
There
are
also
references
to
the
customer
which
makes
up
the
cells
here
and
there's
also
the
sales
array
which
makes
up
all
the
individual
lines
in
the
the
invoice
and
so
the
excel
script.
B
We
then
got
a
similar
example
here:
we've
defined
a
range
we've
got
c2
to
c6,
which
is
this
particular
range
here
of
the
customer
and
we're
applying
applying
things
like
the
name,
the
customer
name
and
such
like,
and
then
a
slightly
different
example.
We've
got
a
loop
here
and
it
starts
at
row
12,
which
is
where
the
first
line
of
our
invoice
begins
and
then
we're
looping
through
the
array
and
again
applying
those
values
into
the
the
row
in
the
excel
sheet
at
the
bottom.
This
was
a
bit
that
I
hadn't
really
graphed.
B
B
So
I'm
gonna
close
this
file
down
now
and
jump
into
my
flow
and
I'll
go
into
edit
that
flow,
and
so
at
the
moment
I've
got
it
set
up
as
a
manual
flown
and
a
real
life
scenario.
Again,
you
might
have
a
recurrence
trigger
so
that
it's
running
it
at
the
end
of
every
month
or
every
week,
depending
on
often
you
want
to
build,
and
then
the
first
action
I
do
here
is
to
get
the
invoice
template.
B
B
The
customer
accounts
list
and
the
items
the
work
complete
list
and
again
in
a
real
life
scenario,
you
might
have
a
no
data
filter
in
place
there
to
just
retrieve
the
data,
that's
relevant
for
the
month
that
it's
running
for
instance,
or
the
week
that's
running
at
the
moment,
it's
running
for
everything
and
then
I
jump
into
and
apply
to
each
so
this
applies
to
each
is
running
off
the
the
value,
that's
retrieved
from
the
customers,
and
so
that
will
obviously
traverse
through
every
single
customer.
That's
in
that
initial
list.
B
So
in
this
scenario,
there
were
four
customers
that
will
run
through
those
those
four
as
a
loop,
and
the
first
thing
I
do
is
to
create
a
copy
of
that
file,
so
using
the
company
name
and
just
to
create
a
unique
file.
Name,
I'm
using
the
format
date
time
expression
to
pick
up
the
the
month
and
the
year
and
append
that
to
the
file
name.
And
then
I
pick
up
the
file
content
from
the
original
invoice
that
I've
picked
up
at
the
top
here
and
that's
just
so.
B
B
But
for
the
customer
it's
actually
split
up
into
individual
fields
and
that's
based
on
the
end
of
the
the
script
where
you
define,
or
in
fact
it's
not
the
the
end.
But
you
saw
at
the
end
of
the
script
whether
the
fields
were
defined
at
the
top.
There
was
the
definition
for
an
array
versus
a
non-array
for
the
the
customer,
and
this
is
where
it's
getting
a
bit
technical
for
me.
So
the
programmers
will
know
what
I
mean,
but
that's
that's
sort
of
covered.
B
The
basics
of
the
cloud
flow
at
the
bottom
here
I've
got
a
part
two
optional
bit,
which
is
the
the
clever
bit.
I'm
not
going
to
go
into
detail
on
this
one.
If
it
runs
it's
a
it's
an
added
bonus,
but
the
aim
is
here
using
some
of
the
out
of
the
box
or
the
the
the
the
connectors
that
are
included
as
part
of
office
365.
B
B
Now,
there's
also
that
other
extra
bit
as
well,
which
will
add
to
the
overhead,
but
if
I
jump
back
to
my
sharepoint
site
and
into
my
documents
folder.
This
is
where
I
store
the
template
invoice,
but
I've
also
got
an
invoices
folder
here,
and
this
is
where
my
invoices
are
being
created
created
as
we
speak
as
as
the
cloud
flow
is
running,
so
I'm
pleased
to
say
that
we've
at
least
got
two
out
of
the
four.
So
far,
I'm
just
gonna
wait
until
the
process
is
completed,
because
I
don't
want
to
disturb
the
files.
B
Oh
there
we
go
so
your
flow
has
run
successfully
perfect.
So
so
back
on
to
my
sharepoint
site,
if
I
open
up
marketing
for
you,
for
instance,
and
look
at
the
the
invoice
here,
we
can
see.
We've
got
tracy
manning
who's
works
for
marketing,
for
you,
and
here
are
the
the
various
lines
of
the
invoice
that
relates
to
the
work
that
we're
billing
for
that
month
and
one
of
the
other
sort
of
curious
things,
it's
pretty
basic,
but
it's
it's
it's
a
great
feature
to
have,
because
this
is
an
excel
file.
B
You've
obviously
got
all
the
formulas.
So
it's
calculated
things
like
the
the
line
totals.
It's
worked
out.
The
discounts
that
we've
passed
on
dynamically
through
the
cloud
flow.
It's
worked
out,
the
subtotal
and
one
of
the
variables
in
the
in
the
cloudflow,
if
you
were
watching,
was
the
the
vat
rate
so
20
and
and
all
that's
worked
out
nice
and
neat.
B
So
the
added
bonus,
if
this
has
worked,
if
I
jump
back
onto
the
sharepoint
site
here,
you'll
see
where
I
had
my
my
links
to
the
various
social
media
platforms.
If
I
go
ahead
and
just
refresh
that
page
we'll
see
now,
we've
got
four
links
to
the
various
invoices.
So
if
I
click
on
marketing
for
you,
it
will
actually
open
up
the
invoice
and
pdf
format.
So
that
was
the
the
second
part
of
the
demonstration
which
I'm
pleased
to
say
it
has
worked,
and
that
is
basically
the
end
of
the
demonstration.