okay so in this video let's make mail
merge to Google Docs using Google sheets
data I have a folder here in my Google
Drive in this folder there's this
spreadsheet let me just open this to
show you what's in here so basically
it's some spreadsheet with some people
listed in here with their names their
phone numbers etc we want to be able to
use this data in our mail merge to
create a document for each line in the
spreadsheet in order for me to do this
I'm gonna go back here this is my
document I already have it open I'm
gonna create two new files one is gonna
be a document that will be a template
for the mail merge and the second one is
gonna be where the mail merge is
actually going to happen so let's go
ahead and do that I'm gonna right click
here Google Docs blank document I'm
gonna call this merge template that's
gonna be this I'm gonna make one more
document here this one will call final
this is where we're gonna get the
results so let's first of all go ahead
and create the example for our mail
merge so I'm gonna go to my template and
say
you
great so now we have this document we
want to use this as a template to
basically create mail merge and what its
gonna be looking like it's basically
we'll have this final document and each
page on this document should be using
this example to create the document but
what we need to do we need to replace
this John Doe with the actual name here
and the telephone number should be
replaced with this telephone number on
the right so for that I'm gonna go here
and replace this with some brackets I'm
gonna say first and this will be last so
I'm gonna use these curly brackets you
could really use anything pretty much
you want to use something some character
that you would never use in the actual
template itself
so this will work fine for me and I'm
gonna change the phone number right here
and this will be phone excellent so this
is my template I have that now we're
gonna do the actual merge we could use a
separate script file we could create a
script file from really anywhere in here
and you could also use a script file
from your spreadsheet so it doesn't
really matter I'm gonna do this from the
spreadsheet so I'm gonna go here tools
script editor let me zoom in just a
little bit and let's get started there
are a few things we need to do number
one thing we need to do we need to save
this project so command S or ctrl s give
this a name I'll call this mail merge
stuff again it doesn't matter what you
call it we need to save all the IDS for
all these documents we're gonna use to a
variable so I'm gonna say var doc
template ID equals 2 I'm gonna create
three variables here
doc final ID and finally this will be
worksheet ID actually I'm just gonna do
WS ID for worksheet that's fine
so the worksheet is this one so I'm
gonna use this ID right here see right
there so that's gonna be the worksheet
so the ID is both between this D slash
and there's the idea until the next
slash so I'm gonna copy that go here and
paste that that's the worksheet ID for
this
I also need IDs for this merge template
so the ID for this again here after this
D slash keeps going until the next slash
copy that this is my template I'm gonna
go here that's the ID for this and
finally we need the final one which is
this one so I'm gonna copy that to our
script as well so here we go so before I
even get to using any information from
the actual spreadsheet itself I want to
just get to these documents and be able
to modify them so let's start with
something simple let's do one example
try to just modify this and create one
person's information and put this on
this document so for that I'm gonna go
here we need to first read the template
document which is this ID so to be able
to do that we need to do our document
app and here we're going to open by ID
and the ID for the template is this so
I'm gonna copy and paste that variable
there that's gonna open that document so
I'm gonna save our doc template equals
to this so that will basically just open
that document we also need to do this
with
the other one so I'm gonna also open
this one the final document and save it
in a variable just like that so now we
have these we need to read what's in
this document so I'm gonna take this
document template and do that get the
body of that document and body is
basically all the content in that
document and then in this body I'm gonna
get all the paragraphs and that will
basically be an array of paragraphs so
I'm gonna save this in a variable
I'll save our template paragraphs equals
to this we can loop through that array
of paragraphs as I'm looping through
them I'm thinking I could make some
changes and then I could take that and
put it back to this other spreadsheet so
let's just try to read every paragraph
and just put this in this spreadsheet
without really making any changes so I'm
just trying to put this in the final
document just like that so to do that
I'm gonna take that variable right here
this is gonna be the array of our
paragraphs and because it's an array we
can run a for each loop on this and that
will accept a callback function oops
let's fix that there we go
this callback function will accept a
parameter and each parameter will be a
new paragraph
I'll just call short P and it's gonna
basically loop through paragraphs and
each one of these is gonna be a
paragraph so when I get the paragraph
I'm just gonna take that paragraph and
put it in the other document so this one
and apparently I already let me spell
that correctly final ID final ID final
ID it makes no difference but let's just
spell it
so I have to go to this document that
I've opened and in this document I'm
gonna get the body of that document and
in that body we're going to append a
paragraph so every time will append a
paragraph and that will accept the
paragraph which will be that P which is
the paragraph let me zoom out just a
little bit so hopefully you can see the
whole thing I'm gonna save this I'm
gonna try to run this let's see what
happens now when we run this it's gonna
ask for permissions to modify those
spreadsheets which will give access to
so regular permissions that advanced
this allow all the regular permission
stuff so element must be detached so as
I was trying to basically write the line
this is the paragraph and I'm trying to
put that paragraph in the other document
but because that paragraph is already in
the other paragraph it's not letting me
do this because that paragraph is the
paragraph on the other document so this
detached thing that's what we're getting
so what we need to do we need to make a
copy of that paragraph and then put it
in the other document instead of just
trying to put that paragraph in the
other document so for that I'm gonna do
this so I'm gonna use this copy method
on this and save this and try to run
this again so I'll run this and see now
it went through no problem we made a
copy of that let's go check what
happened this is our final file and this
is our template file so basically I was
able to just grab the contents of this
file and copy them over here so now the
next step I want to do instead of just
copying and putting the contents in this
file I want to make some changes before
I put it in this file what I need to
change I need to change this first last
names and the phone number with my
desired information so I'll go back here
and here after I make a copy I'm gonna
do this
and see I can't really access all the
methods here because this editor doesn't
let you do this but to be able to figure
out what elements are available in the
paragraph because this is going to be a
paragraph what I could do I could just
do a little trick like this I could take
this that's gonna return an array and if
I get the first element of that array
that will be a paragraph so now if I do
a dot that should show me what methods
are available and one of those is that
actually that copied thing which I
already used now what I want to do after
I copy this actually let me just do that
copy dot I want to change the text so if
I go here see there is this option
replace text and basically I need to
search for text and replace with some
other text and that's exactly what I'm
gonna do so I'm gonna use this method
but I'm gonna use it right here on each
one of those paragraphs instead of doing
this so that I was just doing to get the
autocomplete to see what methods we have
available and just to show you how you
get those when you need them so what I'm
gonna be searching for here I'll be
searching for well a few different
things one thing I have to do I have to
search for this first thing there and
I'm gonna replace it with whatever I
want so I'm just gonna type some name
here so John so that should hopefully
replace that name with John let's save
this and run this just to see what
happens I'm gonna run this go back and
take a look in my final file it kept the
first one and now we tagged on another
one but you can see that for the second
one see we have John as the person here
so we were able to replace the first
name now let's also replace the last
name and we should be able to chain
these so I should be able to just do
another one of these and now I'm gonna
do last and this will be the last name
here so
just gonna send this things to a new
line because it's hard to read so
maybe I'll send the copy to a new line
with JavaScript we can do this it's
gonna read it the same way it doesn't
matter if I put it on the same line or
if I do this it's really the same let me
run this let's see what happens let's go
back check it out see for this new one
we get John Doe we now have to replace
the phone number as well so I'm gonna do
that too which basically just means we
need to run another replace line and
that will be phone and we should replace
it with whatever phone number we want to
use here save this Ronda's let me
actually this time just clean this up
and then go back save this and run the
script let's go take a look so we were
able to use this template right here to
get the information replace our bracket
stuff and just put it back to a new
document which is this file so that's
good what I want to do now because I was
able to do this for this one example I
want to transfer this entire thing to a
function so we can't just repeat this as
necessary using a function let's see how
we can accomplish that so all this stuff
that I'm doing I want to be able to just
read replace and put it in a document I
want to do it using F function zoom out
a little bit create a new function I'm
gonna call this function create mail
merge in this function I want to do this
so if I copy and paste it right here
here we need to be able to access some
of this things so one of the things is
this final document is not going to be
really available here because this is a
different function and we don't really
have that dot final over here to even
access it so we need to
that as a parameter so we're gonna do
this here that's one of the parameters
we're gonna send to this function the
final document we need to also send this
array of paragraphs which is basically
the template paragraphs so I guess that
will be before that it doesn't really
matter we just have to decide the order
so I think that should be enough for now
I'm gonna add some things to this in
just a little bit for now let's just
make sure that this works so I should be
able to just now replace this entire
thing with my function and I'm gonna try
to run this and see if it works so I'm
gonna run this again I'm still gonna run
this first one which is my function
let's go check out great we have another
one so that works so it creates another
one if I run this again it should create
another one yet again good so the
function works with this function
however we also need to change a couple
of things we need to make sure that we
can actually change what the name the
last name and the phone number is
supposed to be so there are nice ways to
go about this and not so nice ways to go
about this I guess for this I'm gonna
keep it simple and just pass those
parameters like the name last name and
the phone number so I'll go here and do
first last and phone so those would be
the parameters I want to pass like this
and those same parameters we have to do
here because this function needs to
accept those parameters right here so
then we'll just use the first name here
the last name here and the phone number
here save
so now when we want to pass a first name
we'll just say whatever first thing we
want to do so we'll do like Linda and
the last name whatever last name we need
to do so Smith and whatever phone number
this is supposed to be so we'll do some
phone number so now if I run this I go
back we should have Linda Smith and her
phone number in our mail merge good
so the function operates it does what
it's supposed to do it basically just
replaces whatever it needs to replace
now at this point I want to start
passing this Linda Smith and whatever
this information is from my spreadsheet
instead of passing it from this
hard-coded values which means we need to
also open our spreadsheet so I'll open
it here on top of the VAR s s for
spreadsheet and we'll do our spread
sheet app and we'll do get actually I
always keep doing this but it's open
open by ID the ID is gonna be this
worksheet ID which is that spreadsheet
we're gonna open that spreadsheet in
this spreadsheet we need to read the
information in that spreadsheet to an
array so we're gonna read this entire
content starting from the second row all
the way down to an array so to do that
we'll do SS dot get range a 1 actually
let's not do it this way let's just do
the range so we'll start from the second
row the first column so that's the
second row first column this is where we
start and we need to end over here
wherever this is so now we need to do
the number of rows so to do the number
of rows we need to do actually
spreadsheet we need to get the worksheet
so let's just first make sure we get the
sheet by name the name of our sheet
let me just rename this I'm gonna call
it data let's call this data so this way
we'll have the worksheet not the
spreadsheet so I'm gonna rename it WS so
in the worksheet we'll get a range 2 1
that's where we're gonna start then in
that worksheet we'll get the last row
and we'll do minus 1 and the reason I'm
doing the last row minus 1 is because
the last row is gonna get us 18 but we
don't need to provide it the last row we
need to provide the number of rows in
the range and the number of rows in the
range is from here through here which is
17 grows because the first row is a
label so that's why I'm doing 18 minus 1
to get to that 17 rows so the last row
minus 1 comma and then the number of
columns so 1 2 3 4 5 6 7 7 columns in
there and to get this to an array we'll
just say get values that should read
that entire thing to a JavaScript array
will save our data equals to this that's
gonna be our array so now that we have
our array we should be able to loop
through our array so I'm gonna go here
after we do this and I'm gonna do data
dot and to loop through our rate we'll
do our for each loop and that will
accept a function that will also accept
a call back so we'll call this our for
row this variable for each row so it's
going to loop through that array and
every time it's gonna get us a whole row
so the first one the second one the
third one etc so in that row the first
one in the row is gonna be the first
name the second one is the last name so
each time we want to create a new thing
in our documents so we just have to do
that function that we just be
a little while ago but instead of typing
Linda here which is the hard-coded name
I'm gonna do R 0 and 0 because in
JavaScript the position 1 is 0 so that
should give us the first name in a
similar way if I want the last name I'm
gonna do the second element which is 1
and the phone number we'll have to
figure out which element that is so that
would be 0 1 2 3 4 5 6 so 6 I'm gonna
save this I'm gonna try to Rhonda's now
what's gonna happen it's gonna ask us
for permission to access the spreadsheet
most likely so I'm gonna run this let's
give this the permissions it needs so
the usual stuff here allow and let's see
what happened so I'm gonna go to my
final file and we should see now that we
have new people see Monica Donna crystal
Ronnie etc so basically we made all of
them in this now one thing that's
annoying is that it's adding all this
stuff over and over so what I want to do
first I want to make sure that when I
run the script it cleans whatever's in
this final file and then puts the values
in instead of just adding it to it so
before we actually put anything in that
file let's just go to that doc final
we're gonna get the body of this thing
and we'll do clear so if I run this I
shouldn't have all that like John Doe
and all that stuff on top so let's try
this
I'm gonna save run and see now we start
with Monica so we have Monica Dona
crystal so we should have all their
information now Monica donna crystal etc
the next thing I want to
I want to make sure that after I put
this first person in there I want to
just send it to a whole new line instead
of doing this so here not only a new
line this should be sending here to a
new page I'm gonna go back here and to
do this after we create this merge see
that's gonna basically go through all of
these things and I pan all these
paragraphs we want to make sure that
after we do that we add a page break so
I'm gonna do that in this function so
that will be in that final document so
I'm going to go here after we do all of
this I'm gonna go here and do app and
there it is page break save this let's
go back and run I need to run this first
one on top run this one again let's go
take a look we have this we have the
second person we have the third person
now we have our mail merge with
everybody on a new page still using this
template right here so if you want to
change this something something
something and maybe this should be bold
or whatever if I run this it should use
that template so I go back and run this
let's go check what happened
seems to work just fine there's one
thing that's gonna create a problem is
when we start adding like bullet point
lists and stuff like that so let me show
you what's gonna happen when we do that
so I'm gonna say item one item two item
three let's go back and run this to see
what happens let's go back and take a
look did an update yet now it updated
see what happens it's not reading the
list items right
the
because what we did here when we read it
to paragraph right here to get the
paragraphs well some of those are
paragraphs and some of them are not
paragraphs some of them are list items
but we're trying to work with it as a
paragraph here and Dad create this
problem because when it's not a
paragraph it doesn't know what to do
with it and it just texts this list item
here in this list so to fix this issue
we need to check here in our code if
that is actually this P is actually a
paragraph or not
now that P see it's not gonna give me
the item list let me just get the list
so we can see what's available in there
so I'm gonna just do this for a second
and get the first item so let's get the
autocomplete so here there is this thing
called let's see get type so if Arantes
get type it should tell us what type of
element we're working with so let's
remove this let me show you what's gonna
happen here I'm gonna do P dot get type
right there let's just log this out see
what happens okay so when i get this
paragraph i'm first gonna get to type
here to see what the type is so for
right now we're just logging it out to
see what it is so i'm gonna run this
let's take a look it says paragraph
paragraph paragraph and then it's a list
item so what i'm trying to show you is
that sometimes you need to add handling
if not everything is a paragraph you
will have to add handling to whatever
doesn't work so in this case list item
doesn't work so first let's check if
that's a paragraph so i'm gonna copy
that so here instead of doing this log
this get type is going to give us the
tie
of the elements so I'm gonna save our L
type equals that get type so then I'll
just do an if statement I say if L type
equals to paragraph then we'll want to
do what we did in here so let's just do
this let me try to indent this a little
bit good so if it's a paragraph we want
to do this we want to take that and
append a paragraph now if it's not a
paragraph let's say it's a list item so
what I'm going to do I'm going to just
add an else if condition I'm gonna do
else if and I'm gonna check if that is a
list item and do we still have that yep
copy that from here so if the element
type is a list item then what are we
going to do we're gonna do a little
something different
I'm gonna copy that paste it here and
instead of doing a pen paragraph we're
gonna do append list item the rest
should be fine it should just work save
test this and see if it works now for
both run that go back take a look see
now we have our item one item two item
three however we don't have the bullet
point so to fix the bullet point problem
we need to add the bullet points to our
items so I'm gonna go back here and
after we did this list item thing I'm
gonna do this and for this list item we
do the set what was it cold there it is
this one to choose the type we have to
do a round bullet point so that's gonna
come from our document
and app see there are this types here
dots now there are different types we
can choose I'm gonna do a regular bullet
which should match the type from the
other one I'm gonna save go back here
let's run this now we have our bullet
points for our items item one two three
let's also make sure that if we have
text here it will change so I'm gonna go
here and in my template I'll just do
something like phone now this should be
replaced with the phone number two let's
go back and rerun our script go back and
take a look now we have Monica Dona etc
etc so that should be a little bit of
overview how you would go about creating
mail merge using Google sheets and
Google Docs and app scripts hopefully
that was helpful thanks for watching
please subscribe and I'll see in the
next one