►
Description
For source code and input files, check out this link:
https://github.com/sumurthy/officescripts-projects/tree/main/Copy%20Tables%20to%20Master%20Table
A
Hello
in
this
video
I'm
going
to
showcase
how
to
use
office
scripts
to
combine
tables
that
are
located
in
this
excel
file
into
a
single
merge
table.
Now.
This
assumes
that
the
the
tables
that
we're
dealing
with
all
have
the
same
structure
and
they
may
be
located
anywhere
in
the
spreadsheet,
but
as
long
as
they
have
the
same
structure,
which
means
the
same
the
same
columns,
then
we
can
combine
all
of
them
into
a
single
table.
A
A
There
is
none
here,
and
this
sheet
has
the
same
set
of
columns
but
different
set
of
rows
and
another
one
here,
so
knowing
that
they're
all
located
in
different
places,
so
I
can
just
get
hold
of
all
the
tables
in
this
given
file.
Now,
if
you
want
to
exclude
some
files-
or
if
you
happen
to
have
other
tables
that
don't
match
the
same
criteria,
like
number
of
columns
may
be
different
and
if
you
want
to
exclude
them,
that
is
a
little
bit
more.
A
A
A
So
now
that
I
have
the
new
sheet,
I
can
begin
to
update
the
range.
So
range
is
nothing
but
sort
of
collection
of
cells.
I
can
begin
to
update
the
the
range
with
the
with
the
header
row
and
I
can
pick
up
the
header
row
from
any
of
the
tables
knowing
what
they
are.
I
can
even
hard
code
it,
but
let's
be
more
dynamic.
A
A
And
new
sheet
offers
add
table
api,
so
add
table
api
takes
in
two
parameters.
One
is
the
the
starting
address,
or
rather
the
the
address
where
you
want
to
create
the
table
and
a
boolean
property
that
has
that
indicates
whether
the
data
that
you're
creating
table
on
top
of
whether
it
has
the
header
or
not.
A
So
I'm
not
ready
yet
because
I
don't
have
the
the
new
sheet
that
I've
created,
I
don't
have
any
data
to
begin
overlaying
the
table
on
top
of.
So
I'm
going
to
comment
this
for
now
and
think
about
creating
you
know
when
I
create
a
new
sheet.
My
goal
is
to
let's
say
you
know,
take
the
header
and
then
and
then
update
the
new
sheet
with
that
right.
A
And
you
should
see
those
five
different
columns
in
a
two-dimensional
array
right,
so
those
are
all
the
table.
Column
values
I
expect
so
now.
What
I
want
to
do
is
take
this
and
update
on
top
of
let's
say,
beginning
at
a1
on
a
new
sheet,
then
I'm
going
to
create
so
for
that
I
will
write,
say
a
function
that
takes
in
two
arguments.
A
Well,
if
you
know
that
this
has
five
columns,
you
know
I
can
say
a1
through
e1,
but
let's
assume
you
know,
the
structure
of
the
table
could
be.
You
know
dynamic
in
nature.
You
know
tomorrow,
I
might
want
to
run
against
another
excel
file
that
has
different
number
of
columns,
so
I
want
to
make
it
a
little
bit
more
dynamic
so
for
that
I'll
compute,
the
target
range
so
I'll,
say,
target
range.
A
A
A
A
Now
I'm
gonna
subtract
one
from
each
given
that
I'm
already
you
know,
I
need
the
relative
size,
so
I'm
going
to
get
the
so
I'm
going
to
subtract
by
1..
So
now
I
just
simply
display
the
new
range
address
just
to
make
sure
I'm
on
the
right
path
here
right.
So
all
this
is
doing
is
extracting
the
header
row
of
the
first
table
and
then
it's
calling
this.
Let
me
call
this.
A
A
I
can
rerun
this
again
and
again
without
having
to
run
into
duplication
issues.
So
for
that
I'll
get
worksheet
with
the
same
name.
I
think
I
called
it
combined
and
then
delete
it
if
it
exists
all
right.
So
if
I
run
this,
I
should
see
well.
I
should
see
the
address
of
where
the
all
right,
so
it's
gonna
compute,
so
it's
computing
it
correctly
so
which
is
on
this
worksheet
a1
through
e1,
is
where
I
want
to
update.
A
A
A
A
A
A
A
A
A
A
A
A
Sheet
names
from
where
I
want
to
pick
up
so
I'll
call
it
sheet1
and
sheet2
and
then
sheet
3.
So
it
will
not
pick
up
the
new
table
that
I
created
here.
So
it'll
only
pick
up
from
those
three
different
sheets,
so
sheets
themselves
have
get
tables
api.
So,
for
instance,
here
there
is
the
workbook
get
tables,
but
she
will
also
contain
the
workbook.
You
know
get
tables.
A
A
A
A
A
So
for
each
of
the
sheet
names
I'm
going
to
get
the
actual
sheet
and
then
I'll
call
the
git
tables
on
it.
So
from
then
on
it
should
work
pretty
much
the
same,
except
that
I
don't
want
to
create
the
the
target
table
more
than
once
right.
So
for
that
purpose,
I
will
extract
this
piece
out
or
maybe
do
that
just
once.
A
A
A
A
It'll
pick
up
the
first
table
of
the
target
sheet
and
it
could
be
any
sheet
doesn't
matter
because
they're
all
the
same
and
then
it'll
you
know
create
you
know,
get
the
target
range.
It'll
call
the
same.
Update
range
function,
receive
the
target
range
and
create
the
the
table,
and
that
will
mark
the
table
created
as
true.
So
that
way
it
won't
pick
up.
A
It
won't
go
into
this
logic
again,
so
it's
going
to
just
create
the
table
once
and
then
for
each
of
the
sheet
it's
going
to
get
in
all
the
sheet,
all
the
tables.
Well,
in
this
case,
I
only
happen
to
have
one,
but
if
you
had
multiple
tables
in
the
same
sheet,
it'll
work
the
same
and
then
for
each
of
the
table.
It
is
going
to,
you
know,
append
the
values
in
terms
of
address.