►
Description
Checkout this page for full description and source & Excel sample file used in the video:
https://github.com/sumurthy/officescripts-projects/tree/main/Top%205%20Tips
A
A
Everything
that
I'm
going
to
be
talking
about
today
is
captured
in
this
page
here,
I'm
going
to
leave
the
link
to
this
in
the
description.
If
you
like
this
video,
do
subscribe
and
leave
a
comment
about
any
new
video
that
you
may
wish
to
see
all
right.
Let's
get
started
to
help
demonstrate
I'm
going
to
go
to
this
excel
spreadsheet
and
I'm
already
on
the
automate
tab
and
I've
opened
a
script
that
I'm
going
to
be
using
to
walk
through.
The
first
step
is
in
relation
to
the
the
intellisense
features.
A
So
intellisense
feature
is
nothing
but
a
tool
that
the
intellisense
other
that
the
editor
offers
using
the
typescript
service
to
help,
discover
apis
and
discover
any
warnings
or
potential
pitfalls
that
you
may
have
in
your
script
so
to
get
started.
You
can
start
with
any
object
that
you're
interested
in
in
this
case.
Workbook
is
the
object.
That's
passed
to
the
main
function.
A
It's
excel
range
value,
so
range
is
a
very
fundamental
object
within
excel
and
it
can
consist
of
either
one
cell
or
a
group
of
cells,
so
all
of
that
is
termed
as
a
range,
so
in
excel
the
value
is
what
excel
stores
behind
the
scene.
It
may
be
different
than
what
you
actually
see
in
the
cell,
so,
for
instance,
in
this
case
I
have
a
date
field
here,
a
date
cell,
and
if
I
go
change,
the
the
the
type
of
this
to
a
general
you'll
see
that
it
actually
is
stored
as
a
number.
A
So
what
you
see
may
not
be
what
you
actually
is:
what
excel
stores
behind
the
scene
when
you're
dealing
with
range
values,
you
have
to
be
careful
about
how
you're
interpreting
it
and
how
typescript
interprets
as
you're
coding
along
so
one
technique
is
to
tell
typescript
explicitly
what
type
of
value
you're
expecting
here
to
demonstrate
that
I
have
a
date
here
now,
I've
written
or
rather
reused.
This
function
called
excel
date
to
jstate
function.
A
It
takes
in
a
serial
date
as
an
argument
which
is
of
type
number,
and
it
does
some
computation
and
returns
a
javascript
date.
I
may
use
that
for
additional
processing
purpose,
but
the
point
here
is
to
demonstrate
that
I'm
calling
a
function
that
expects
a
date
and
now
to
begin
with,
I'm
going
to
read
the
the
range
a1
and
get
its
value.
A
So
when
you
get
the
value
you'll
see
that
it
it
could
be
f
type
string,
number
or
boolean,
and
since
the
script
doesn't
know
what
the
value
could
be,
it
knows
that
it's
one
of
those
three
types.
So
when
I
pass
that
date
that
I
just
read
or
a
value
that
I
just
read
into
this
excel
date
to
jstate
function,
I
have
to
tell
typescript
that
hey
this
is
of
type
number.
A
Otherwise
the
types
is
going
to
complain.
So
this
is
called
type
assertion.
So
type
assertion
is
nothing
but
overriding
the
type
at
the
compile
time
saying
hey.
I
know
what
I'm
doing.
I
expect
this
to
be
a
number
and-
and
hence
typescript,
looks
at
that
and
says:
okay,
I'm
gonna,
I'm
not
gonna!
Warn
you.
So
this
is
one
easy
technique
to
avoid.
Those
warning
messages
now
note
that
in
reality,
this
may
not
help
your
runtime.
A
So
what
I
mean
by
this
is
so
when
I
run
this,
let's
see
I
run
this
and
it's
going
to
show
me
the
javascript
date,
which
is
what
I
expect,
but
what,
if
the
cell
here
contains
something
else
that
you
don't
expect?
Let's
say
I
change
this
date
to
a
some
string
value.
When
I
run
the
same
date.
Sorry,
you
want
to
run
the
same
script.
It's
going
to
complain
that
this
is
an
invalid
date.
Now,
that's
because,
even
though
you
said
you
declared
that
hey,
this
is
a
number
in
reality.
A
When
you
read
at
runtime,
it
is
not
not
a
number
to
avoid
that
kind
of
runtime
issues.
You
can
do
a
simple
check
using
a
runtime
check
by
the
keyword,
type
off,
so
type
off
is
a
way
of
getting
it
runtime
the
value
or
the
rather,
the
type
of
the
variable
that
you're
dealing
with.
So
in
this
case,
it's
excel
date
excel
date
value
and
you
can
check
hey.
Is
this
number?
So?
Similarly,
you
can
do
this
for
string,
boolean
and
so
on
on
an
object
as
well.
A
So
once
I
wrap
my
logic
here,
underneath
the,
if
condition,
it'll
ensure
that
it'll
call
that
function
to
convert
only
if
the
data
type
is
of
type
number.
So
when
I
run
this,
nothing
happens
because,
obviously
it
is
not
a
number
right
and
now,
if
I
copy
back
the
same
value-
and
I
run
this
I'll
get
the
date
back
right.
So
this
is.
A
This
is
a
way
of
using
working
with
range
value
types
using
typo
session,
as
well
as
runtime
validation
of
the
types
to
make
sure
that
you're
calling
the
functions
only
when
at
runtime
it
satisfies
the
condition
that
you're
looking
for
right.
All
right,
so
tip
number
three
has
to
deal
with
the
understanding.
The
the
argument
type
so
you're
going
to
use
a
ton
of
methods
as
part
of
your
scripting,
and
you
you've
already
seen
like,
for
instance,
I'm
getting
the
range
and
I'm
getting
the
value,
and
so
on.
A
Some
of
these
methods
accept
arguments
and
to
effectively
use
these
methods.
You
need
to
understand
what
type
of
arguments
you're
dealing
with
so
in
this
case,
you
know
get
range
accepts
an
address
which
is
of
type
string,
but
in
some
cases
the
arguments
could
be,
you
know
more
than
one
it
could
be
required
could
be
optional.
In
some
cases
it
may
require
either
an
enumeration
or
an
object
type.
A
So
let's
look
at
these
two
types
a
little
bit
more
closely,
so
enumeration
types
are
where
excel
api
in
this
case
understands
one
of
many
valid
values
that
you
can
pass
as
arguments.
It's
not
an
arbitrary
value,
so
in
that
case
it
will
give
you
a
select
list
to
choose
from
so
let's
say
if
I
want
to
clear
range
a1,
so
I
can
use
the
clear
method
and
the
clear
method
takes
in
excel
script.
A
Clear
apply
to
so
when
you
see
sort
of
two
segments
here
you
know
that's,
either
an
object,
parameter
or
an
enum
parameter.
So
if
you
just
simply
type
excel
script
and
clear
apply
to
notice
that
the
intellisense
stays
at
the
top
here
to
help
you
along
and
then
when
I
click
dot,
I
get
all
the
options
that
I
that
I
want
to
use
that
I
can
use
so
here,
I'm
gonna
click,
all
I'm
gonna
clear,
the
rest
of
the
code
here
and
then
I'm
gonna
simply
click
run.
A
So
when
I
do
that
it'll
clear
the
a1
and
it
used
the
enumeration
to
determine
what
type
of
clear
that
I
wanted
to
do
now.
So
that's
the
enumeration.
The
second
one
is
the
object
parameter.
So
the
object
parameter
is
a
little
bit
tricky
to
understand.
So
let's
look
at
this
up
close
to
do
that,
I'm
going
to
use
the
the
find
all
method
that
the
worksheet
has
to
offer.
A
So
in
this
code
that
I'm
uncommenting
here
it
gets
the
worksheet
and
it
does
a
find
all
on
that
worksheet
for
the
word
breaks,
and
it's
saying
that
in
this
second
argument
that
the
match
case
to
be
true.
That
means
it's
only
going
to
find
when
the
the
case
also
matches
so
you'll
see
that
this
parameter
is
included
within
the
open
and
close
curly
curly
parenthesis.
A
A
And
take
your
cursor
here
inside
the
parenthesis
and
simply
press
ctrl
space,
and
you
will
see
all
the
options
that
it
has
to
offer.
You
can
use
the
up
arrow
key
down
our
iq
to
move
around
and
you
can
also
press
on
this
little.
I
icon
here
to
understand
that
hey
this
is
of
type
boolean
and
you
can
also
read
the
description.
So
in
this
case
let's
say
I
want
to.
A
I
want
to
ch,
I
don't
if
I
don't
care
about
the
match
case
I'll
say:
no,
I
don't
care
about
it
so
false.
So
when
I
run
this,
it's
going
to
display
some
information.
So
let's
look
at
what
it
actually
displays
in
line
number
seven,
I'm
simply
getting
the
results
which
is
of
range
areas
and
then
getting
the
address,
which
is
a
string.
A
So
wherever
that
word
is
found,
it's
going
to
give
me
the
the
cell
address
of
those
concatenated
by
a
string
like
a
comma
and
then
in
line
number
10
and
11,
I'm
going
to
take
the
same.
The
address,
string
and
split
that
in
into
an
array.
So
when
you
use
a
split
which
is
a
string
function,
it
splits
and
returns
an
array
back.
So
when
I
run
this,
you
should
see
that
this
is
the
get
address
value
and
this
is
sort
of
the
same
string
value
converted
into
an
array.
A
Now
what
if
I
change
the
match
case
to
true,
so
in
this
case
it
is
not
going
to
result
in
any
value.
So
it's
going
to
result
in
this
stack
of
error,
so
that
is
tip
number
four,
which
is
how
to
effectively
use
the
or
handle
the
undefined
and
null
values
all
right.
So
let's
look
at
how
to
handle
that
scenario
now.
A
The
reason
why
it
is
complaining
is
that
on
line
number
seven,
when
I
use
the
get
address,
it
is
complaining
that
hey
you're,
calling
get
address
on
an
undefined
result,
so
result
is
undefined
because
it
didn't
find
any
proper
match.
So
when
I
try
to
call
a
method
called
get
address
on
an
undefined
type,
it's
going
to
throw
an
error,
so
a
simple
way
to
avoid
that
is
to
just
check,
saying
hey.
If
result,
so
that
means
it
is
not
a
null.
A
It
is
not
an
undefined,
so
null
and
undefined
are
two
native
javascript
types
that
you
want
to
always
handle
carefully.
Otherwise
you'll
run
into
runtime
issues,
so
I'm
going
to
enclose
the
whole
logic
here
inside
this.
If
condition,
if
result
means
it
is
a
valid
object,
type
or
a
valid
type,
but
that
is
not
a
undefined
or
null.
So
when
I
run
this,
it's
not
going
to
do
anything
because
it
it
didn't
execute
anything
in
here.
A
A
A
So
that's
because
a
range
can
be
a
single
cell,
it
could
be
multiple
cells
so
since
excel
doesn't
know
how
what
kind
of
range
you're
reading
when
you
ask
for
get
get
me
values,
get
me
formulas,
the
plural
version
of
these
methods.
It
also
always
gives
a
two-dimensional
object
to
demonstrate
that
here
I
have
a
table
object
so
which
gets
the
first
table
in
this
worksheet
and
it
gets
the
texts
so
on
the
range
on
the
header
range.
It
gets
the
text
so
table
dot.
Get
header
range
header
row
range.
A
Is
this
so
line
number
four
a4
through
c4
and
get
texts
and
when
I
simply
display
the
header
value
you'll
see
that
it's
a
two
dimensional
array,
even
though
it's
a
single
row,
it
always
has
two.
It's
always
packaged
into
us,
the
second
dimension.
Now
what
if
I
want
to
just
display
this
as
a
single
dimension
as
a
one
1d
array,
so
that
I
don't
I
know,
maybe
I
can
I
want
to
use
that
I
want
to
map
through
or
do
some
processing.
A
You
can
just
simply
get
the
zeroth
index,
knowing
that
it
only
has
one
row
so
in
this
case
you'll
see
it
comes
across
slightly
differently
here,
because
it's
it's
just
us
a
single
dimensional
array.
So
that's
how
you
can
extract
a
single
row
out
of
multiple
rows
or
out
of
a
range
that
may
have
one
or
many
rows.
So
that's
the
row,
this,
the
actual
columns,
is
a
little
bit
tricky.
So
here
let's
say
I
want
to
add
hundred
dollars
to
each
of
the
sales
value
and
then
add
a
new
column.
A
To
do
that,
I
can
use
an
api
called,
add
column
so
add
column
here
if
I
uncomment
and
I
can
see
that
it
accepts
a
single
dimensional
array,
object
of
type
string,
number
or
boolean
right.
So
to
do
that,
if
I
want
to
read
this
whole
column
value,
I
can
use
the
method
here
that
I'm
going
to
uncomment
and
demonstrate
so
that
is
to
use
the
this,
the
the
get
column
by
name
sales
and
get
its
range
value,
which
is
the
whole
range
and
then
get
its
values
so
get
values.
A
You
can
see
returns
a
two
dimensional
area
object.
Now
the
goal
is
to
extract
the
sales.
This
whole
thing
as
a
single
dimensional
array
object.
So
to
do
that,
I
have
written
like
a
helper
function
here
extract
column,
so
it
accepts
a
two-dimensional
array,
object
of
type
string,
number
or
boolean,
and
then
it
accepts
an
index
and
then
it
basically
maps
through
the
data
and
collects
so
map
is
a
way
to
collect
various
aspects
of
each
item
of
an
array
into
another
array.
A
A
As
I'm
doing
in
line
number
13
here,
I
can
extract
column
of
sales
values,
which
is
a
two
dimensional
array,
object
and
says
zero.
So
zero
is
the
zeroth
index
in
this
case.
That
is
the
only
column
that
I'm
going
to
have.
So
when
I
display
what
I
get
back,
you
will
see
that
I
have
so.
This
is
the
original
sales
column.
A
Each
cell,
so
here
I'm
using
the
map
function
again
on
the
one
dimensional
array
object
that
I
got
out
of
the
function
and
I'm
simply
adding
100
so
you'll
see
that
I'm
overriding
this
as
a
number
and
including
100.
So
as
number
is
a
typo
session,
if
you
recall
from
tip
earlier
tip
and
typescript,
is
able
to
understand
that-
and
it's
not
warning
me
because
of
that
reason,
and
then
line
number
21,
I'm
just
simply
calling
add
column
minus
one
is
to
say
added
at
the
end
and
then
I'm
passing
this
revised
sales
data.
A
So
if
I
run
this
again,
you'll
see
a
new
column
getting
added
with
a
hundred
dollars
added
to
each
of
the
cells
right.
So
that's
converting
two-dimensional
array
into
either
a
one-dimensional
row
or
a
one-dimensional
column.
Now,
if
you
wish
to
convert
this
back
for
some
reason,
let's
say
I
want
to
take
the
one-dimensional
object
like
here
in
this
case
the
sales
back
into
two
men
two-dimensional.
You
can
do
that
by
writing.
A
similar
helper
function
that
I
had
here
it
converts
a
column
into
a
2d.
A
It
takes
in
this
case
a
single
dimensional
array
like
of
string
number
or
boolean,
and
then
it
returns
a
two-dimensional
array
object
by
returning
I'm
using
the
map.
Again,
it's
collecting
and
and
enclosing
that
inside
another
array.
So
it
basically
takes
each
item
packages
into
an
array
of
its
own
and
then
it
puts
an
array
over
the
entire
entire
array
and
then
it
converts
into
a
two-dimensional
array.
So
if
I
display
that
again,
I'm
gonna.
A
Use
the
convert
column
to
2d
pass
the
same
sales
as
one-dimensional
array
as
an
argument,
and
I'm
going
to
just
log
that
back
so
you'll
see
that
I'm
going
to
get
the
same
result
back
here
as
as
in
the
case
when
I
originally
read
the
range
for
the
sales
column,
it
it
added
a
new
column,
that's
okay,
so
yeah!
You
can
see
that
it's
basically
returning
the
same
thing
back
to
me
here.
I
essentially
converted
a
2d
to
1d
column
and
then
back
to
a
two-dimensional
array.
A
So
you'll
end
up
finding
yourselves
in
having
to
do
this
simply
because
of
the
nature
of
range.
So
that
concludes
the
fifth
tip.
So
hopefully
this
was
useful
for
you.
So
if
you
enjoyed
watching
this,
please
do
leave
a
comment
subscribe
to
this
channel,
and
hopefully
will
this
will
help
you
improve
your
productivity.
Thank.