►
Description
Checkout this page for script source:
https://github.com/sumurthy/officescripts-projects/tree/main/Performance
Contents
0:00 Intro
2:32 main() function
4:44 Update range in chunks function
11:57 Core logic - compute range address and update
17:00 Smaller range update (in one go)
18:05 See it in action
A
I
had
done
a
video
on
a
similar
topic,
which
is
to
effectively
read
range
data
in
a
performant
manner,
but
the
scenario
was
fairly
simple
and
the
data
that
the
user
was
dealing
with
was
quite
small,
you
know,
say
small
to
medium,
but
if
you
have
some
large
data
requirements
where
you
need
to
write
back
a
large
amount
of
data,
then
you
need
some
more
advanced
concepts
to
you
know,
write
scripts
in
a
manner.
That's
that's
more
optimal!
A
So,
for
instance,
here
I
have
you
know:
100
000
rows
of
data,
and
if
you
have
to
write
back
such
a
large
amount
of
data,
then
you
really
need
to
be
paying
closer
attention
to
how
you're
writing
back.
So
the
concept
that
I'm
going
to
showcase
is
to,
rather
than
write
back
all
at
once
break
up
this
data
into
smaller
chunks,
a
smaller
batches
and
you
know,
write
them
in
smaller
chunks
so
that
they
have
a
higher
rate
of
success.
A
A
What
I'm
doing
is
generating
the
data
in
a
random
fashion.
In
your
in
your
case,
maybe
you
have
actual
data
that
you
that
you
get
from
somewhere
or
read
from
somewhere
else
and
then
you'll
have
to
write
that
back
for
the
purpose
of
the
demo.
I'm
just
generating
this
data
randomly
and
the
data
that
I'm
going
to
generate
is
going
to
look
exactly
like
what
I
have
here.
It
has
multiple
rows.
A
A
Let's
make
it
hundred
thousand
rows.
So
once
I
have
the
data
that
I'm
ready
to
update
with
update
the
range
with,
I
call
this
function
called
update,
range
in
chunks
and
and
then
I
pass
two
parameters.
One
is
the
the
beginning
cell,
where
I
want
to
update
the
data
with
in
this
case
it
is
b2,
and
then
I
pass
supplied
the
actual
data
that
I
just
created
as
a
parameter.
A
So
so
it
accepts
two
two
arguments
and
then
it
returns
a
boolean
flag,
saying
you
know
if
it
went
through
successfully
or
not.
If
it
didn't
run
successfully,
I
just
display
an
error
message
and
ask
people
to
check
again
and
rerun
and
that's
it.
So
that's
pretty
much
what
the
the
main
part
of
the
script
is.
A
A
Second
is
the
values-
and
here
I
have
the
type
of
the
argument
defined
here,
which
is
it
could
be
a
string
or
boolean
or
number
it's
a
two
dimensional
array
right
and
each
of
the
each
of
the
array
element
represents
one
single
cell,
so
it
has
because
it's
a
two-dimensional
data,
it's
it
has
to
be
rows
and
columns,
and
then
the
third
parameter
is
interesting.
So
this
is
the
cells
that
I
want
to
update
in.
A
So
the
number
of
cells
that
I
want
to
update
in
one
go
and
for
this
purpose,
I've
for
this
for
the
demo
purpose,
I've
defined
10,
000
cells
and,
in
your
case,
depending
on
the
type
of
data
you
may
want
to
play
around
it
could
be.
You
know,
10
000.
It
could
be
a
thousand.
It
could
be
100
000,
I
don't
know
so.
A
You'll
have
to
play
around
with
the
data
a
little
bit,
but
I
think,
based
on
limited
amount
of
tests
that
I've
done,
10
000
seems
to
work
fine,
because
it's
not
too
large,
it's
not
too
little.
It
seems
to
kind
of
get
through
the
the
data
quite
quickly.
So
so,
if
you
do
want
to
overwrite,
you
know
you
can
pass
that
in
as
part
of
this
argument
list.
So
let's
say
you
wanna
do
only
five
thousand
cells,
so
you
could
do
that.
A
But
if
you
don't
supply
this,
it's
gonna
take
the
the
10ks
default
size.
The
default
cells
that
it's
gonna
update
with
now
the
thing
another
thing
to
notice.
When
we
update
the
range
it
has
to
have
the
full
row.
For
instance,
I
cannot
update
a
partial
row,
that's
because
the
api
that
accepts
the
the
input
data
requires
for
it
to
have
a
complete
row
right.
A
A
So
this
is
a
simple
scenario
which
is
like
your
data
is
small,
so
you
call
this
function
and
I'm
going
to
show
that
at
the
very
end,
but
this
this
is
not
really
the
primary
focus
and
then
it
returns
that
okay,
I
did.
I
did
that
work
successfully
and
that's
it,
but
if
the
data
is
larger,
which
is
sort
of
the
point
of
this
demo,
so
you
have
a
large
amount
of
data.
A
Now
you
want
to
calculate
the
rows
per
check.
Like
I
mentioned,
you
cannot
do
a
partial
row
update,
so
you
have
to
calculate
the
number
of
the
cells
that
I
want
to
update
in
one
go
in
one
chunk,
divided
by
the
the
row,
the
number
of
rows
so
value,
0,
sorry
number
of
columns,
so
value
0
is
the
first
row,
so
you
can
pretty
much
take
any
row
length
gives
me
the
the
number
of
cells
in
a
number
of
columns
in
in
the
input
data.
A
So
then
I
get
I
get
the
rows
per
chunk
that
I
can
include.
This
is
important
because
I
need
to
now
figure
out
based
on
this.
How
many
rows
I
can
update
in
a
single
go
because
I
don't
want
the
the
I
don't
want
to
send
the
partial
row,
so
I
have
to
send
the
whole
row
so
for
that
I
have
to
calculate
the
minimum,
or
rather
the
maximum
row
that
I
can
send.
A
It
may
be
that
there
may
be
some
additional
columns
that
will
be
left
over,
but
at
the
end,
we'll
accumulate
and
and
and
just
do
another
last
update
and
I'll
show
how
that
works
all
right.
So
once
I
figure
out
how
many
rows
per
batch
that
I
can
use,
then
I
initialize
few
variables
like
row:
count
total
rows,
updated
and
then
the
chunk
count.
A
The
row
count
is
the
important
one.
Then
I
begin
a
loop
in
the
loop.
I
travel
from
the
first
row
all
the
way
till
the
end,
so
one
row
at
a
time.
So
in
this
case
you
know
one
row
at
a
time.
It's
going
to
drive
us
through
that
and
then,
if
it
checks
hey
once
I
reach
the
number
of
rows
that
I've
previously
determined
as
the
the
maximum
count.
A
I
can
use
per
chunk
per
per
batch
and
then,
if
that
is
the
case,
then
I
update,
I
call
another
function
called
update
next
chunk,
so
here
I
pass
in
the
starting
cell
again,
which
is
the
top
cell,
and
then
I
pass
in
the
same
values
and
then
I
pass
in
two
other
arguments.
One
is
a
rows
per
chunk,
which
is
you
know
how
many
rows
I
can
update
in
in
the
chunk
and
then
the
total
number
of
rows
already
updated.
A
There
are
in
the
input,
data
and
then
multiplying,
but
by
100,
to
get
the
percentage
value.
So
the
the
next
thing
to
look
at
is
the
update
next
chunk
function.
So
this
is
where
pretty
much
most
of
the
computation
is
happening
right.
So
the
update
chunk
next
chunk
is
its
role
is
to
figure
out
one
to
update.
A
You
know,
let's
say
n
number
of
rows
in
a
go
in
a
batch,
and
but
it
cannot
do
that
all
the
way
you
know
every
time
from
the
start
it
has
to
compute
where
I
ended
next.
So,
let's
assume
that
we
are
updating.
You
know
three
rows
and
ago.
So
in
the
first
rank
it
has
to
update
this
and
then
the
next
chunk
it
has
to
update
the
next
I'll
use
a
different
color
here
and
then
the
third
time
it
has
to
update.
A
A
It's
going
to
use
that
to
compute
the
offset
range
so
that
it
will
know
where
to
start
from.
So
in
this
case,
let's
say
it's
the
very
first
time
the
total
rows
updated
is
going
to
be
zero,
so
the
the
starting
cell
get
offset
range
will
always
return
the
same
cell.
Let's
say
the
total
rose,
updated
is
three.
A
The
the
get
offset
range
is
going
to
give
you
the
starting
cell
at
b5
and
so
on.
So
that's
why
the
total
rows
updated
argument
is
important.
So
once
I
have
the
starting
cell,
I
on
the
the
cell
that
I
obtained
I'm
going
to
call
another
api
called
get
resize
range
and
its
purpose
is
to
compute
the
target
range
for
the
current
chunk
so
that
it
can
use
the
set
values
api
to
update
it.
To
do
that,
it
accepts
the
rows
percent
because
it's
zero
index
I
have
to
subtract
by
one.
A
A
It
accepts
two
parameters
the
beginning
and
where
to
end,
and
then
I
use
the
total
rows
updated
and
the
total
rows
updated,
plus
the
rows
chunk,
so
that
I
know
in
the
from
the
input
data.
I
can
just
extract
just
that
amount
of
data
that
I've
highlighted
here.
But
this
we're
talking
about
the
input
input
range
and
then
I
I
call
the
set
values
on
the
target
range
that
I've
just
computed
the
whole
range.
So
this
api
call,
so
the
set
values
is
the
really
the
api,
that's
doing
the
actual
work.
A
So
this
is
the
api,
that's
actually
updating
your
range
and
when
it
updates
it,
we
want
to
make
sure
if
it
went
through
successfully
or
not.
So
I
surround
that
by
a
try,
catch
statement
and
if,
if
for
some
reason
it
didn't
go
through
successfully
it'll
throw
an
error
and
your
script
will
end
right
there.
Otherwise
it
will
simply
return
it
all
right.
A
So
what
it
did
was
like,
depending
on
the
argument
you
sent,
it
will
compute
the
next
range
and
it'll
update
it
and
it'll
return
back
to
the
the
the
place
where
we
called,
which
is
in
a
loop,
so
once
it
exhausts
that
there
may
be
some
additional
rows
left
because,
let's
say
at
the
end,
let's
say
I'm
left
with
one
or
two
rows,
because
the
the
rows
per
chunk
is
three.
These
won't
be
picked
up
within
the
loop,
so
I
check
hey.
A
A
In
that
case,
I
just
call
another
function
here,
which
is
update
target
range.
So
this
is
fairly
simple
and
I
have
that
here.
What
this
does
is
it.
It
calculates
the
the
target
cell
by
picking
up
the
sorry
computes
the
target
range
by
the
target
cell,
which
is
the
top
one,
and
then
it
gets
the
resize
range
for
the
using
the
the
values
that
we
sent
number
of
rows
number
of
columns
again
minus
one
because
it's
zero
index
and
then
it
uses
a
set
values
to
update
it.
A
So
this
this
is
a
kind
of
the
plain
and
simple
scenario,
but
if
you
have
large
data,
all
the
work
is
going
to
be
done
in
the
update
next
chunk,
so
that
that
is
the
I
guess,
a
long
walkthrough
of
what
the
script
does
now,
let's
go
back
here.
I'm
going
to
copy
paste
this
code,
I'm
going
to
expand
the
office
scripts
here,
I'm
going
to
open
the
office
scripts
here
I
believe
it's
this.
A
Okay,
so
here
it
uses
10
000
rows,
so
maybe
I'll
use
that
so,
let's
I'll
start
a
new
sheet
so
that
you
can
see,
see
the
progress
and
I
should
expect
pretty
much
what
I
was
looking
earlier,
but
this
time
on
a
new
sheet,
so
it
starts
to
you
know,
update
as
you
can
see
it's
kind
of
giving
me
the
progress
along
the
way.
These
are
the
console
log
statements
that
I
did
and
then
at
the
end
it
said:
okay,
I'm
done
so
it
took
had
you
know:
60
000
cells.
A
But
you
know
it's:
you
can
take
the
script
and
try
this
on
your
own
and
and
try
it
out
with
different
chunk
size
and
see
how
it
affects
from
my
very
limited
testing,
the
the
10
000
seems
to
work
for
medium
to
large
size
data
all
right,
so
it
finished
updating,
600,
000
cells.
It
took
42
seconds
to
complete
and
yeah,
so
that's
sort
of
the
the
walkthrough
that
I
intended
to
do
so
I'll
supply.