►
Description
If the range/data you are dealing with is quite large (say 100K cells), you may need to use advanced techniques like breaking-up your read/writes into multiple chunks. This video is really for small-mid size data setup.
For large dataset, refer to https://github.com/sumurthy/officescripts-projects/tree/main/Performance
A
Hello
in
this
video,
I'm
going
to
walk
through
a
couple
of
office,
cuts
performance,
related
tips,
that's
going
to
help
your
script
run
much
faster,
and
this
is
in
relation
to
a
scenario,
that's
quite
common,
which
is
you're
required
to
read
some
data
out
of
excel
and
perform
some
data,
manipulation
and
write
back
an
example.
Data
that
I
have
is
list
of
products
and
what
I
want
to
do
is
append
some
data
to
the
customer
name.
A
Yes,
you
can
use
formulas
for
this,
but
for
the
sake
of
demonstration,
let's
say
I
have
to
read,
and
then
I
have
to
perform
this
in
the
script
itself
and
write
back
so
that
is
sort
of
what
the
right
way
of
doing
this
and
then
there's
a
wrong
way
of
doing
this.
So
I'm
going
to
show
both
so
beginning
with
this
a
very
simple
script
and
what
it
does
is
on
line.
A
The
get
address
gets
the
range,
and
I
just
want
to
run
this
and
make
sure
it
runs
correctly.
So
yes,
so
you'll
see
that
on
the
console,
it's
displaying
the
right
range
and
then
I'm
going
to
paste
a
code
snippet
here
which
shows
sort
of
the
wrong
way
of
doing
this.
A
A
So,
for
instance-
and
it
starts
with-
I
equals
one,
which
is
the
second
row.
So
the
get
row
is
going
to
be
just
that
part
of
the
part
of
the
range
and
for
each
of
the
row.
It
then
gets
the
the
second
cell,
which
is
using
the
get
cell
and
then
gets
its
value.
So
in
this
case
it's
gonna
get
that
value
and
then
for
the
same
cell,
it'll
use
a
set
value
api
and
pass
in
the
the
data
that
it
just
read,
plus
some
constant
concatenated
string.
A
So
that's
pretty
much
it.
So
I'm
going
to
run
this
against
this
sort
of
fairly
smallish
data.
I
have
just
27
rows
in
here,
so
you'll
notice
that
when
I
run
this
the
you
know
it
was,
you
could
see
each
of
the
row
getting
updated
right
and
that's
not
what
you
want
to
see
because
for
small
data
you
can
you
can
manage
with
that.
But
let's
imagine
I
have
something
large
like
in
this
case.
I
have,
I
think,
similar
data,
but
around
900
some
rows.
A
So
what
I'm
going
to
do
now
is
comment
this
out
and
show,
what's
the
ideal
way
of
doing
this,
to
do
that
I'll
first
read:
the
the
rows
of
data
are
the
actual
data
associated
with
it,
so
I'll
use
the
range
get
values
api.
So
this
returns
a
two
dimensional
array
object
that
represents
just
the
data
within
and
now
once
I
have
that
locally,
which
means
now
I
was
able
to
bring
the
data
back
from
excel
and
I'll
use.
A
A
very
similar
loop
like
before
again
start
with
the
row
one
and
go
through
the
entire
length
of
the
rows
and
then
for
each
row.
What
I'll
do
is
update
the
rows,
the
second
cell,
so
so
this
is
I
and
then
one
so
the
first
index
is
the
row
and
the
second
is
a
column.
So
that's
for
each
I,
the
first
second
columns
are
index.
One
I'll
set
that
to
you
know
the
same
plus
the
concatenated
value
that
I
need
so
I'll.
A
A
So
you'll
see
that.
Let
me
just
kind
of
go
to
the
end
of
this
into
this
data
here.
So
I'm
going
to
run
this
script,
and
this
uses
just
a
single
read,
which
is
read
everything
you
need
ahead
of
time,
get
this
into
local
variables
and
process
the
local
variables
and
then
just
update
back
using
the
set
values.
A
So
so
I
ran
this
as
you
can
see
quickly
updated
all
these
900
some
rows
and
it
took
far
fewer
seconds
than
the
time
that
it
took
to
go
through
each
of
the
rows.
So
this
is
the
ideal
way
of
doing
it.
So
make
sure
that
you
read
all
your
data
ahead
of
time
and
then
just
use
the
set
values
all
at
once.