Google Apps Script Tutorial to Email Google Sheet as PDF Document on Button Click in SpreadSheet

uh Hello friends today in this tutorial I will be showing you that how to email the use the data which is present inside your Google sheet spreadsheet as a PDF document to your email address so let's suppose you have this data here we have name country we have four columns out there four rows out there and it's a button out there so whenever I click this button guys all this data will be captured and it will be emailed to my this one so let me show you step by step as I hit this button guys what should happen now inside Google apps script it will execute this function in the background let me hit this button here send report you will see it is running the script here and as soon as this script is ending you will see finish script I will get a message here you will see on the right hand side I got a message and basically it will have this as a PDF document if I open this you will see all this information would be their name country John Williamson news and you will see that four call four rows are there and it has simply captured the screenshot as the image and email tell me as a PDF document so I can even download this as a PDF document so you will see that if I open this inside my browser you can print it out so it's multiple users are there so you can capture the information inside your Google sheet and then you can also email this as a PDF document as well so it's very easy step-by-step process I will show you how to do this process and for this I have written a complete blog post on my tutorial website which is webninjadeveloper.com so you can go to this blog post the link is given in the description of this video so I have explained each step in detail with instructions and all the source code is also given so let's start building this application guys from scratch so first of all if you don't know about Google apps script guys it's a scripting language developed by Google and if you just type Google apps script basically this is the language we will use so it is used to communicate with the Google apis that you have such as Google Drive YouTube or Gmail spreadsheet all those stuff Excel so specifically here we are using our Google sheet so just type Google sheet here and go to Google sheet here and click on go to sheets and here we will create a blank sheet guys and basically we can rename this you can go to file here click on rename and we are here you can say that our player data so inside player data guys we will have two columns out there name age and Country as well three columns you can have as many columns as you want guys totally depends upon you let me insert the details out there and let me you can have your data coming from a database or anything else API totally depends upon you let me just add this here so we have four columns out there guys which we will be emailing here so now guys you can see that we have four rows out there and now we have to have a simple button so that when we click that button we need to execute the script in the background so in order to draw a button guys simply go to this insert option here in menu bar and here is the option called as drawing option so simply select this and after this you need to draw a shape out there simply go to shape out there and simply select your shape which is this one and right here simply draw it and here we can simply say that send report you can just customize the text accordingly and resize the button like this and also you can move this button like this here also just to place it and after that you simply need to click save and close so now your button will be there guys you will see that inside your Google sheet and basically here we can assign it simply right click and here you will see these three dot options simply click it and this is the third option that you need to select which is assign script so it is telling me what script do you want to assign so here we need to assign the name of the function that we will be executing let me email sheet this is a function we would write simply click on OK that's it so now when you click this button guys you will see script function could not be found so we need to write this function go to your extensions app script and here we will write our app script code guys this is our editor here where you can create projects and deploy it on the live internet as well so now we will write this function guys which we have written which is email sheet so this is a function so basically it's scripting language if you are comfortable with JavaScript you will be understanding all the concept here it's a JavaScript language and here we will be first of all be getting the reference of the spreadsheet current spreadsheet you can use the spreadsheet app library for this this contains a method which gets the current active spreadsheet this is a method that we will invoke get active spreadsheet simply invoke this method now this will be basically you can see there are lots of sheets out there sheet1 sheet two you can even add she too as well sheet1 you can add as many sheets as you want so basically you can see that so we are simply getting the reference to the current sheet which the name of the sheet is sheet1 so after this guys we need to first of all get the file name so we will simply say a copy of and then we will simply get the name of the sheet which is sheet 1 so get name we have the method here get name that's it so this will output a copy of sheet1 you can even rename your sheet name as well guys simply go to rename and simply change it here to player sheet let me just rename this you will see now this sheet has been renamed to player sheet and after this guys after getting the file name we will now be declaring my new book and simply we will copy this copy all the content which is there inside the sheet all the columns all the rows out there it will copy all the data alongside with this button as well it will take a screenshot after this guys we need to put the email address to which you need to send out it so I will choose this email address after this you will be using this mail app guys mail app library and this contains a method called as send email which will actually send this spreadsheet as a PDF document and here this takes some options the first option is the two option and here we will be sending to the email address that we have specified here so we will simply say email the second option it takes is the subject property guys so we will simply say uh player info and the body we will simply say this is the sheet this is a player information sheet after this guys we need to add a attachment here there is also a attachment property so here you can just attach your images PDF documents alongside with the email address so this is an array guys basically here we will be attaching my new book and here we'll be converting it to a blob object so we will say get Blob so basically it will return the data inside this object as a blob if you don't know about a blob guys basically whenever you try to export your images you can export it to a blob object and now we simply need to set this a name here this is a method here set name so we will set the name for this block alongside with the PDF extension so file name is there we can see file names there so we can just add extension of PDF that's it that's all that you need to write code guys this is your code you guys it will take the screenshot of this spreadsheet as a image and it will insert it in the PDF document and it will mail this inside this email address that's it now Simply Save your project guys Simply Save your project and now if you click this button send report you will see it will run your script and basically it will ask for the permission whenever it runs for the very first time so it will first of all you need to allow the permission here you will see authorization required so you need to click continue and basically allow the permission simply select your account here and go to Advanced and go to this one and simply allow the permissions so this will add it create and delete all your Google Sheets spreadsheet and also it will send the email as well so simply click allow here and after this guys it will authorization is successful and now if you check your if I check my this one you will see that authorization is successful I think we need to click this button once again running script so now you will see after you run the script here finished script now if you check you will see player info this is the player information sheet and this is the PDF document which is attached here if you open this you will see that all the three columns are there four rows are also there this is a button so basically it has taken the screenshot as a PNG image or inserted it in the PDF document so you can now download this as a PDF document and simply print it out like this you will see that it can contain as many rows as you want guys so basically this is a nice little functionality you can develop anytime you click Send report it will automatically send out to the specified email address and you can even customize this let's suppose you want to just put a comma here if you want to send out to multiple people so you can even do that as well so here you can mention the second email address so now it will happen it will send out to multiple people so save your project and now if you click Send report so it is running the script once again and once again when it finishes now if I check the second email click on Gmail you will also see guys cleared info you can see the same data is received here also let me select my second one you will now see once again this same data will be there you can see for the second time it is received here so in this way you can also just send it to multiple people at once also with with a single click Send report in the Google sheet so that's all guys from this video please hit that like button subscribe the channel and if you want the full source code of the script you can go to the description of this blog post I have of this video I have written a complete blog post so I will be seeing you in the next video

Channel: Coding Shiksha

Views: 4,783

Keywords: email sheet as pdf document

Id: xirIOLRGo1U

Length: 11min 55sec (715 seconds)

Published: Sun Nov 27 2022