hey folks. In this video, we're going to be discussing how to insert images into Google documents that are coded up through Google Apps Script. So, this is the second lesson in my video on automating the creation of Google Documents. The first one focused on page elements and inserting paragraphs. This one is going to be focusing on images. So, what I would recommend that you do, let's go to Google Drive and you create a project folder so that all of your stuff is nice and organized. We're going to be focusing on two different types of images today. One is going to be images from the internet, so like a JPEG or a PNG file, and then we're also going to learn how to take charts from Google Sheets and then insert those images into a Google doc. So, within my images project folder, I have a Google Sheet that I've already created that has some data in it and it has two charts on the page, and that's going to be another thing that we learned how to do is how to get all of the charts from one sheet.

or from any sheet within your Google Document. Cool, so we're going to begin by um going to the extensions menu and going to apps script. From a sheet, I've already cleared out my um boilerplate function. I've also allowed access to my Google sheet as well as my Google Drive and I think to Google Documents. So, during the course of this tutorial, the first time that you hit run for those things, you might have to allow access. So, if that happens, pause the video. I'll try and give you a prompt while we're going. Apologies in advance if I forget. Cool, so we're going to begin by creating a function. I'm going to call it Main. Uh, inside the curly bracket, we're going to begin by accessing the spreadsheet. Uh, so I'm just going to say access the spreadsheet so that we can kind of keep track before we wreck. So, we're going to declare a VAR for WB, which stands for Workbook, and I am going to call the spreadsheet app. I'm going to get the active spreadsheet. I'm going to do an

open parenthesis and a semicolon. I'm going to create another variable called sheets, and sheets is going to reference the WB variable. Then it's going to use the get sheets method. The reason that we're going to do this is because we want to create a for Loop where we have a variable of I, which stands for iterator in sheets. Space curly bracket, enter. We're going to say if sheets square bracket I. So this stands for if sheets iterator dot get sheet ID parentheses double equal sign zero. The reason I'm using 0 is because the sheet1 ID is zero and it is always zero anytime you open up a Google sheet. It's going to be zero for that sheet1id within the parameters of RF statement. So we're going to say if sheets iterator.get sheet ID is equal to zero, close the parentheses. Space curly bracket, hit enter. Within that if statement, we are going to create a variable called sheet name, and that is going to equal sheets iterator dot get sheet name. Square, sorry, semicolon. You can see once

we close the semicolon; we have these gray signifiers showing that we're still within the if. Once we go down one row, you can see we are now in the for loop, and now we are back into our main function. We're going to declare a variable called access, and we're going to call the WB variable from line three. We're going to say get sheet by name, and then we're going to pass in that sheet name variable that we just created, and we are now accessing our spreadsheet via the sheet ID. I address this in my level 2 Data automation video, so if you're curious to learn more about what we did and why we did it, check that out. The long and short of it is that Google Apps Script does not have a native function or method that allows you to access a sheet by its ID. It has the get sheet by name method. The challenge with using names in data work is that if the name changes, then your automation, your script will break by referencing the sheet ID, which in this case is zero.

we are extracting the name via the sheet ID. So, if you have it, if you're using a spreadsheet that is not sheet1, go up to the GID, which is at the end of your URL, collect the sheet ID, and paste it into this file where there is a zero cool. So now that we have access to the sheet, we are going to extract the two charts. So, we are going to add another comment, and you can call it whatever you want to. I'm going to write export the charts or you know what, I'm going to call it extract because we're not actually exporting anything cool. So, I'm going to declare a variable for bar, and I'm going to use that access variable that we just created on line 12. And I'm going to use the get charts method, and we're going to use a square bracket and a zero, and then we're going to use get as, and then we're going to extract it as an image/jpeg because we want to insert it into our Google Doc. So, in order for us to get that pie chart.

which is going to be VAR Pi. We're going to use the access variable again, and we're going to use the get charts method, and we're going to use parentheses except for instead of using 0, we're going to use one, and then we're going to use the get as we're going to do image slash JPEG, and now we have access to our charts. Now the way that this is working is the get charts method allows us to enumerate the charts based upon their creation. I use the word enumerator, I don't know if that's the right use of it, it sounded good, just roll with it. Basically what happened is this bar chart was created first, therefore it is indexed as zero. The pie chart was created second, therefore it is indexed as one. When we go into our script and we say access dot get charts, and then we use that index point, that's allowing us to isolate the in the first chart, right? So, theoretically I haven't done this, but theoretically we could do the exact same thing that we did up here with sheets.

where we could create a charts variable, and then we could loop through the charts. But because we know precisely that we want both charts like that, we can declare them as variables and then reference them so that we don't have to deal with looping through them and all that type of stuff. Cool, so now that we've been rambling on for a little bit, why don't we get started and figure out how to insert these into a document. So we are going to create a document first, so we're going to declare a variable for Doc Creator, and we're going to call the document app. And then we are going to use the create method. And at this point, we need to... This is where we give the document a name. So I'm going to say How to Insert Images, Headers, and Page Breaks. The reason I did that is that I have a note to myself to remember to do all those things. So now that we've created a doc, or we will be creating the doc, we are going to want to access the document's body.

we're going to call a variable or create a variable called Doc, and then we're going to call that doc Creator variable, and then we're going to use the get body method. And as a refresher, Google documents have three sections: the header, the body, and the footer. So if you want to create a header, all you have to use is the get header method. If you want to go into the footer section, you can use the get footer method. We just want to work in the body, so we are going to do doc Creator.getbody. And now that we have access to the body, we can call that doc variable, and we can do a bunch of things. So we're going to begin by creating a header. So I'm going to do doc.insert, and then we're going to do a paragraph. And when we insert paragraphs, we need to tell the document where the element goes, which is where we use the child index argument. So we're going to begin at index 0. So this is going to be the very first thing.

that goes into our document. And we're going to put in there is bar chart. Um, and we want this text to be a header. So after we use the insert paragraph method, we are going to use the set heading method. And within set heading, we are going to use the document app. And we are going to access the paragraph heading method. And then once we do that, we have access to all the various headings: the normal text, the subtitle text, and the title text. We're just going to do a heading one. After that, we're going to insert our bar chart. So I'm going to call Doc.insert. And then we're going to use image. And then we're going to have to give it an element like where to insert it. So I'm going to do, I'm going to do one. Um, sorry, that was funny. Um, okay. So I'm gonna call that bar variable. And it's at this point, I'm going to save my script. And I'm going to run it. If this is the first time that you're running this.

script, you're going to have to allow access for this script to, you know, be able to create, read, edit, and delete spreadsheets and documents, so you might want to pause the video now before you hit run because you are going to have to take a moment just to allow access. Cool, so I'm going to hit run and hopefully I don't get any errors. Cool, that's great. I'm going to go back to my drive so you can see I'm insert. I'm in my insert images folder. I'm going to just click up here to my drive, and that's going to take me to a home, and check it out, I have how to insert images, headers, and page breaks, and when I open it up, I have a header for bar chart, and eventually that image will load, and then I have my bar chart there. Cool, so now what we're going to do is insert a page break, and then we're going to recreate this, but instead of saying bar chart, it's going to say pie chart, and then we're going to have the pie.

chart. So we're going to have page one be a bar chart, and a bar chart page to be a pie chart, and a pie chart. So we're back in our script and um what we're going to do now is called doc.insert insert paragraph. And then we're going to do index two. And then we're going to say pie chart. And then we're going to say set heading. And then we're going to call document app.paragraphHeading.heading one. And then we're going to do doc.insert image three. And now we're going to call Pi cool. Now the thing about this though is we haven't put in our page break yet. So in order to do that, we are going to uh add a space in there so that we have some room. And then we're going to call Doc. And then we're just going to say insert page break. And that page break needs to have the same child indexing as a paragraph. So now what we're going to have to do is change uh to a two.

and then at our pie chart, we're gonna have to change that to three, and then down here we're gonna have to change it to a four, and we're going to hit save, and we're going to hit run across our fingers, go to our drive, and give it a moment. I'll just refresh it. Cool, now we have a second document in here, and my computer is so slow. Cool, so we got a bar chart, and hopefully we'll see a bar chart. Sweet, and we scroll down, we have a page break, and then we have a pie chart. We have the pie chart. Great, everything about that was wonderful, except for the process for adding those child elements in here, in here, right? So you saw we needed to add something that didn't exist, and then we had to change up all those elements. So instead of us having to manually add that stuff in there, what we can do is declare a variable for index and set it to zero, and then instead of writing out numbers, we can just type in index, and then we

can do all the way at the end after our semicolon. Here we can do index plus plus and a semicolon. And then that will automate. Or it won't automate. It will iterate the indexing of numbers so that we don't have to hard code anything in here. So take a moment now and remove those numbers and change them into index. And at the end of these statements, make sure you paste in semicolon index plus plus semicolon at the very end of the statements. And this will allow you to no longer have to type in those numbers. And if you decide that you want to put in a page break after doing something. Or you want to add in a new element or whatever. You don't have to do things over and over again. So for example what I'm going to do now. And you don't have to do this. But I just want you to say we're going to add in. We're going to learn how to access an image from Google Drive. And instead of having the page break between bar chart and pie chart we're

going to have the page break after bar chart and pie chart. In the previous iteration of our script, where we were hard coding 0 1 2 3 4 in there, we would have had to have made direct alterations to each line of code. Now we can just copy and paste and move them around, and they will dynamically respond to our changes. So what we're going to do now is access that image from our Drive. So I'm going to go back into my document creation folder, and I'm going to access this image that I have here, and it's just called image.jpg. Bonus points if you know what this is referencing. I'm going to go up to more actions, and then I'm going to get share, and I'm sure there's an easier way to do this, but then I'm just going to copy the link. Basically, we want to get the asset ID of this image, so I'm going to copy the link, and then I'm going to paste it into my script file, and I'm going to get rid.

of this portion of the URL. I'm going to call it VAR image ID, and then I'm going to wrap it into a string. Before that, I'm going to get rid of this backslash view sharing, all that type of stuff, and now we have a variable that we can access. So I'm going to add in a comment here just so that you can see access image from Google Drive. And what we want to do is we want to take this image and we want to insert it into our Google Document. So what we're going to have to do is we have our image ID. So let's call it VAR image, and we will call Drive app. get file by ID, and then we're going to pass in that image ID variable that we just created. And then what we're going to do is we're going to do doc. insert image, and then we're going to do index, comma, and then we're going to reference, nope, we're going to reference image, and we are going to do semicolon index.

plus plus semicolon and we're going to cross our fingers and hope that that works but it might not so we'll see. Gotta review permissions on this this is that process that I thought I took care of at the beginning of the video. Okay let's see sweet looks like that worked so now we're gonna get rid of this gonna go to our drive go to our most recent document and hope for the best. So we got our part our bar chart and an image and then we got a pie chart and our image and then whoa so we got our image but we did not get the image the way that we wanted to. And the other interesting thing in here is that we didn't even need a page break because it ended up just like dynamically doing it because it was too big to even fit on one page so much for that. Anyway we're gonna go back and the problem that we have and maybe I skipped over that a little bit too fast so let me just kind of talk about it.

this image is way too big. And this is going to happen sometimes. Like you can literally see that it's like spanning multiple pages, right? And this is going to happen a lot. Usually, it happens with these charts. But for whatever reason, these charts um ended up fitting in very nicely into the document. But obviously, like our image from the internet did not. And it probably has something to do with the actual size of the asset, right? So like the amount of data that is saved into Drive or is being passed from the sheet or anything in there. I'm not an expert. I don't actually know. But what I do know is that what we can do is resize the image, right? So in the event that you have an image that is too large for a document and you want to resize it, what you can do is after `doc.insertImage(index, image);` right before the semicolon, you can do `.setWidth` in order to put in pixels the width that you want the image to be. So I'm going to

do 450, and then you can also set the height, and I'm going to do 300. Now I am not an expert at image sizing and all that type of stuff, and frankly, I struggle a little bit with it because I don't really have a way to gauge those things. So if you are watching this and you do have strategies for that, let me know because I need the help. Um, cool. So we're back in here, and I'm gonna refresh, and hopefully we'll have a new document with a much nicer image. Cool. So we're going to go to this one, the most recent one at 6:28 PM, you can see, and we're going to try and open it up, but my computer is dragging. Cool. So we got bar chart, got a pie chart, hopefully those things will load. They did, and then we go down here and check it out. We got an image. Now, does it look great? No, not really. It's kind of stretched out a little bit, but at least it fits on the page now, right? So last

thing we're going to do is just add in a header for it, and then I want to do like a very clear page for it because I feel like you're not really getting a page break uh tutorial so uh we're gonna do doc dot insert paragraph, and then we're going to do index, and then we're going to do here we are now entertain us. If you know, you know. And then we're going to do it as set heading, and then we're going to document app dot paragraph paragraph heading, and then we're going to do heading one, and then we're going to do index plus plus, and then we're going to bring in the page break that you've been promised. So I'm going to do doc dot insert page break. That's going to be at index, and then we're going to do index plus plus. If you don't do that index plus plus at the end, it's not going to iterate on itself, and it will not put it where you want to. So what I'm going to do is I'm going to say

Doc insert paragraph, and then I'm going to do index, and then I'm going to say, um, one baby to another, said, um, lucky. Oh, sorry, actually, that's great. So, like, you can see here how, like, um, lucky to have met you. This is one of those instances where using single quotes gets kind of wonky. If you have a problem like this where you need to use, like, an apostrophe, you can just change this to be double quotes, right? And those work interchangeably with one another. So, we have, doc.insert paragraph index, comma, and then whatever you want to put in there. If you get my reference, again, bonus points to you. After our paragraph, we're going to do, semicolon index plus plus semicolon. We are going to do, save. We're going to hit, run. Hopefully, it will finish someday. Wonderful. Go back here, gonna refresh because hopefully that's faster, and then we have a new file at 6:30. We have the new file at 6:31. We have our bar chart, and hopefully that

image will be a bar chart. Maybe it's a bar chart. Who knows? It could be a bar chart. Hey, it's a bar chart. Okay, cool. And then we got a pie chart. And then we have our heading and our gnarly looking smiley face there. Here we are now entertain us. And then we have a page break. And then we have the paragraph that says, One baby to another said I'm lucky to have met you wonderful cool. So to recap what we learned how to do today, we learned how to insert images, headers, and page breaks into Google Documents via Google Apps Script. This is one fundamental that you will require if you want to automate the creation of Google Documents. In addition to inserting images into docs, you also learned how to resize images. You also learned how to iterate upon a child index. And yeah, you're just generally in really good shape now. I hope you found this video to be helpful. I love getting, you know, comments and suggestions on stuff, so please feel free to reach out if you got the reference in.

today's video also points that out you deserve the recognition um and yeah if you want to learn more about you know automating stuff in Google Documents or Google Sheets or Google Slides or any of that type of stuff I got the videos so check out my channel let me know what you think and I wish you the best of luck on your data Journey

Insert Data Charts into Google Docs using Google App Script

Insert Data Charts into Google Docs using Google App Script

스크립트