Google Sheets - Email HTML Tables & Schedule Apps Scripts

[@kingjoshuamanatad2140]

You're the best tutor in app scripts out there! Love to learn again and again from you!

[@genysisonline8496]

Perfect 100%. You have made my life a whole lot easier with the html part, i used to go in circles to achieve this...

[@BalaMurugan-yo9ln]

ি

[@mayurhalli148]

You are a saviour for me. I have been digging for this a whole week.

[@TheHepeete]

My friend you are doing does amazing things with google sheets!

[@ConsulthinkProgrammer]

Most people, usually have a habit. People also hold values ​​that they believe in--values ​​that fit with themselves. One simple value that is useful for us and others is not to skip advertisements when watching videos on KZbin. Thank's for your great lesson Sir.

[@umayTv]

Just what i needed..thanks..save me time doing it from scratch.. KUDOS..

[@promentosolutions]

Thank you so much for sharing the video. It was very helpful.

[@guruprasadgaikwad4921]

Very helpful with detailed explanation

[@mr_shanes]

Great Video! i learned a lot while recreating the spreadsheet and the script as i watched (start-stopping) the video. Many thanks!

[@ExcelGoogleSheets]

great to hear!

[@atanusamanta8162]

You made my day......thanks for this video

Congratulations, excellent contribution

[@pedroviterbo1010]

OMG! Thanks to you and youtube for let share this!

[@dharmeshkashyap761]

Thanks mate I've been searching it for a long time

[@henniescheepers2072]

Thank you, it works like a charm. Love your work!!

[@ferrydavebicoy436]

Man, thank you for these valuable contents!

[@rgetsy1]

Great stuff. My weakness is HTML so it really helped.

[@damitrioustucker1576]

I didn't understand a thing but i enjoyed watching this

[@IntrepidFox]

Awesome once again!

[@viper33m]

great content

[@myhelpdesk6414]

You are great sir thank...

[@mercadoboricua6154]

The best!

[@AntonioSantos-ve6zv]

Really great content!

[@quintus5180]

Amazingly useful. Thank you!

[@rs32323]

Life saver 👍

[@chuckf5540]

Great video - do you have a version that runs on V8

[@dnanton72]

Great video, thanks a lot

[@vinayjangid4254]

How to add. Clickable link in this table can you help me please ???

[@Charu1007]

At 19:50, when you put "arrow function", thats not working for google script in my code. showing "unexpected >"

[@ExcelGoogleSheets]

Either switch to V8 or use function(a){ return a} instead of a => { return a}

[@Charu1007]

@@ExcelGoogleSheets thanks, but i am not getting this. What should i replace this with "tablerangevalues.forEach((r,i) => { ?>" ?

[@ExcelGoogleSheets]

"tablerangevalues.forEach(function(r,i) { ?>" ?

Thank you so much. I'm immediately start using this :-)

[@ExcelGoogleSheets]

Wonderful!

[@alexanderpirvu6560]

is there any way to make it so that the table in the email only takes rows that have a specific email attached to them?

[@Dekar0s]

How can we include an if function inside the html template to evaluate, for example, if we want to ommit a particular row to be shown?

[@eneiasmg]

Muito obrigado, Mestre!

[@ArunTheScorpion]

Thanks for the useful and precise video. This helped a lot. It would b great if some insight/ approach to handle the following case can also be shared. How can we embed the hyperlink from source data into email body as well. e.g. suppose in the given example "total value" of each Item had an hyperlink (in google sheets) to a webpage with details of respective item, the intent is to list this value along with the hyperlink in the email body as well.

[@nickseah7942]

Hello, possible to add charts together with the tables?

[@luizerajoga1506]

:O so awesome

[@rishirajjaingupta]

What if we want to use a specific date and time trigger to use the date & month from the spreadsheet? Imagine if I have a list of my friend on a spreadsheet with their Name, email and birth date, and birth month stored in separate columns, and I want to send an email to them on their birthday.

[@babasaly1]

I wonder if there is a way to track if an email got opened/read?

[@suyunagidenadam552]

Thank you so much for the video. It is absolutely fantastic. Is there a way to conditionally format a specific row in the e-mail html. For example, I would like to change the colour of a cell in the table to red if it is smaller than a certain value, and to green if it is larger.

[@pedrotomasdominguezcardena9206]

Asombroso!!, Gracias!!

Can you share the code?

[@haithamhassan2358]

2allak eh ?

[@quintus5180]

Is it also possible to send a print screen of this table directly instead of doing the whole HTML formatting?

[@rosebellbicar1125]

Your videos are super amazing. I followed these steps and it's working perfectly. I do have a question, how do I reference the emails based on the email value from another tab on the same worksheet? It will be much appreciated.

[@ExcelGoogleSheets]

With functions kzbin.info/www/bejne/eGmUYmuLZd9jlc0 In code kzbin.info/www/bejne/qqGYYX2DraiDidU

[@Prince_Chaurasia]

Sir , Can u help me to create graph in html body of this mail and also image attachment .

[@brunomarquez8958]

Did you make avaliable this code in some place? I really need it, because my code isn't working.

[@FunSpiritman]

Very useful tutorial. I like your approach to explaining complicated processes and testing in stages. I also appreciate your speeding up a lot of the rote typing. Nice enhancement once we get the idea of the task at hand (i.e. blah, blah, blah... we can fill in the repeating details). Do you have a tutorial on styling with CSS? I learn so much (I'm thinking REGEX) with your channel. Thanx.

[@Inspiringeverydaymum]

Hi, just out of a wonder would you be able to point me in the right direction... it gives me an error “r is not defined”, where have I missed something...

[@joao_galdino]

Probably you forget this line "htmlTemplate.tableRangeValues = tableRangeValues"

[@andersonsilva-kf6hh]

Ajudou muito!!

[@Alx8282]

Hey Bro, I love your tutorials, they are the most useful I've found on KZbin. Could you please make a tutorial of Google Sites (editor) + web apps?

[@ExcelGoogleSheets]

What exactly do you need from Google Sites? Have you checked my Web App series? It does include a video that shows how to place your Web App within Google Sites.

[@Alx8282]

@@ExcelGoogleSheets Thank you for your response! I haven't finished the series yet, I'll keep watching. I was thinking of using sites as a more visual editor for web pages.

[@coreymccall2963]

@@ExcelGoogleSheets In my Google Form, a respondent can enter a date when they want to be tutored. I didn't finish watching this video but I was wondering if there was a way that I could schedule a reminder email to send to that respondent the day before of the day they have to get tutored. I was also wondering if there was a way that I could add their response in the email or something like that? Thank you.

[@quhtani1]

What if I want to give the client a simple form to enter his number and email then click submit so the script finds that client number then send his report OR I click a button on the sheet so the script sends all clients their reports. The script will be excellent that way.

[@darrenlongnecker1386]

How do you get the table into a Google Doc so it can be converted into a pdf and emailed? I'm good on the pdf conversion, but can't for the life of me write the table into the doc.

[@jairajispat7365]

your video is very intersting and challnging also. I like your explaing method. I want to know how to add font size for alternate rows along with the backgound color ? could you explain sir?

[@DustinStewart1]

Thank you for another great video! How would you add an embedded image from the sheet?

[@ExcelGoogleSheets]

Are you asking for an external image link or an actual image that is sent together with email?

[@DustinStewart1]

@@ExcelGoogleSheets An actual image that is sent together with the email. Example would be a logo in cell B1 or pasted image in another merged cell.

[@ExcelGoogleSheets]

I'll probably make a video for this, but keep in mind that generally sending this type of images with email is not recommended. Good practice is to host images on a server and link to those with tag.

[@DustinStewart1]

@@ExcelGoogleSheets That would be awesome!

[@rs32323]

Just needed your help I wanted to use hourly trigger but wanted it to be triggered to working window only for example my working window is 9 am to 9 pm

[@ExcelGoogleSheets]

There is no easy way. You need to write a script to create triggers programmatically. For Example this will create a trigger for function called myFunction to run on Monday 9AM. You can make a for loop and pass the 9 as a variable. function createTriggers() { ScriptApp.newTrigger('myFunction') .timeBased() .onWeekDay(ScriptApp.WeekDay.MONDAY) .atHour(9) .create(); }

[@rs32323]

@@ExcelGoogleSheets thank you ur really amazing 👍🏽

[@nileshphalke2365]

How can i create date and time-specific trigger

[@ExcelGoogleSheets]

What exactly are you trying to do?

[@gajendrasingh5656]

Hello sir thanks for giving knowledge about script sir I face the issue when sending email HTML table not show in gamil mail but show in other mail client yahoo outlook. If any solution please suggest me

[@rahulnath3072]

Can we add conditional formatting code in column E like Red, Green, Yellow? Also, It will be better if we can add some graphs along with the same table in email intimation if possible. Please help me with this.

[@ExcelGoogleSheets]

I'll make a video of emailing a graph at some point. I don't think I understand how you want conditional formatting to work.

[@rahulnath3072]

@@ExcelGoogleSheets Thank you for your reply. I just want to send a signal page dashboard intimation with a data table and graph. I was able to send a data table via google sheet to Gmail as you mentioned in your video but my challenge is to send an object which appears in the sheet but not able to send via email. So, I don't know to use sheet value and sheet object (graphs) to send in one Gmail as intimation. ** In conditional formatting I just want to highlight some data points with some given condition like (1) if the data value is more than $10000000, so the color should be "Green" in the same value cell. (2) data value is in between $10000000 to $1000000 = "Yellow" and (3) data value is below $1000000 = "Red".

[@abrahamriggs463]

Hi first of all thanks for sharing the information! I copied your code but It seems I encountered a problem. the HTML copied your table even the number formats(currency and decimal places), mine is not. Is there something you can help?

[@TambayangPinoy2023]

I did exactly what's on the video but I am getting "TypeError: tableRangeValues.forEach is not a function". can you tell me what's going on?

[@10radamel]

Same here

[@10radamel]

I was not getting values for this line....const tableRangeValues = ws.getRange(7,2,lr-7,4).getValues()

[@IntrepidFox]

If you get the chance to reply could I bother you with something additional?: is it possible by adding a simple if statement in the current HTML code to select rows based on a condition? i.e. if unit price is > 20 if( r[2] > 20 ) If yes, where would you place the statement? Many thanks again!

[@ExcelGoogleSheets]

The correct way to do this is to use Arrays and Filter method. All you need to know is in this playlist kzbin.info/aero/PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD

[@IntrepidFox]

@@ExcelGoogleSheets Thank you very much for the reply! I'll check out the playlist.

[@growmybusinessonline5711]

Really good

[@furkhanpatel1648]

Your video is fantastic. I am able to loop the rows but I also want column to be dynamic like rows. how can I do that? If you can pls help that would be great.

[@umayTv]

How to bind this function to HTML Button

[@umayTv]

can you also do a print function to integrate as a button..

[@ExcelGoogleSheets]

Email clients will filter this out for security reasons.

[@umayTv]

ok thanks...any suggestion?

[@andreipop8324]

Hi I keep getting a syntax error on line 34 “const htmlForEmail...” , error reads unexpected token ‘>’ , I can’t figure out the error

[@andreipop8324]

The debugger states the htmlForEmail is undefined

[@ExcelGoogleSheets]

Share the code. I can't guess.

[@andreipop8324]

@@ExcelGoogleSheets figured out the error came from the html part

[@surgeelectricestimating4916]

@@andreipop8324 what did you fix on the html part

When execute de code: "SyntaxError: Malformed arrow function parameter list (línea 34, archivo "Code")", and that line is: const htmlForEmail = htmlTemplate.evaluate().getContent(); I wrote the line as in the example, I don't know what the fault is, please help me.

[@kenneyshepard4511]

I have the same problem. If you get rid of .evaluate it will work. It's always a good idea to use the content assist provided. It looks like google has removed .evaluate .

[@zafaraeqbal]

I'm going through the same issue, how to fix it?

[@camdendunlop8142]

I love your videos. They really help me improve my coding skills. I'm trying to adapt this to something where I have multiple subheader rows that should be color coded differently from the banded rows. These are signified by a "1" in the second column. Any ideas on how I could achieve this?

[@enjoywithsunandasharma6193]

its only for gmail?

[@ExcelGoogleSheets]

GmailApp for gmail or you can use MailApp to use your default google account email.

[@williamfowl]

Could you send a link to this sheet?

[@darioscardina]

Great video! Is there any source link?

[@miyagenco.ltd.3100]

Hi bro , how to set background image for send email

[@ExcelGoogleSheets]

[@zafaraeqbal]

I'm getting error "TypeError: tableRangeValues.forEach is not a function (line 23)", Please help me to fix it, somebody wrote that "EDIT: found issue - the script runtime needs to be most recent, mine was running a deprecated version. " But i don't know how to fix it.

[@pichit.raetai]

Thank you

[@EduardoZertuche]

Thanks for the great video! is there a way to schedule a list of emails from a spreadsheet where each email needs to be sent at a specific time and date?

[@Naveenkumar-lq3ik]

How do I contact you..

[@wonglili3482]

Can we have all the script code， so that we can test it on the spot. Thank you.

[@victoriaporcel6477]

This is great! I created one based on this video and it's just perfect! The problem is my company is migrating from gsuite to microsoft 365. Is it possible to recreate this in excel? If so, how can I do it?

[@ExcelGoogleSheets]

It's possible, but you'll need to use Outlook to send out emails.

[@rambairwa4851]

Your videos are pretty awesome and very helpful, wants to know how can i enhance the limit of email by default it is 100

[@ExcelGoogleSheets]

Limits are defined based on the type of account you have with Google developers.google.com/apps-script/guides/services/quotas

[@Shkur777]

Тема раскрыта не до конца. Почему не сделать вложенный перебор массива?

[@findthetruth3021]

You are always making amazing videos ✌️👌😁. But, I am currently struggling with Google sheet online in which I am trying to create a protection in the enter sheet only those cells and columns that has a specific word, so in a nutshell, I want to make automatic protection based on conditional. For instance, I have a data entry in which the team leaders fill it and I have a column to approve them, if I said yes then automatically lock these cells for them, and if it was no then leave it as it is. Thanks in advance. I have searched a lot but didn't find anything like this.

[@ExcelGoogleSheets]

I think this should be possible IF you use installable onEdit trigger, not the simple onEdit.

[@findthetruth3021]

@@ExcelGoogleSheets can you make a video on that please. There is no such video on KZbin. Many thanks

[@Relarsirloco]

that what i need

[@ACAnimations]

My script won't run due to a "syntax error" when trying to evaluate the htmlTemplate - it 'seems' to be an issue with the for each loop in HTML but I do not see a single issue anywhere, my code is exactly the same as yours. EDIT: found issue - the script runtime needs to be most recent, mine was running a deprecated version.

[@zafaraeqbal]

How did you fixed it?

[@ACAnimations]

@@zafaraeqbal Open the script in the Apps Script editor. Select the Run > Enable new Apps Script runtime powered by V8.

[@surgeelectricestimating4916]

@@ACAnimations Could you show me what you mean by that. I am having the same problem. In my settings I have the Google chrome V8 run time enabled already.

[@ACAnimations]

@@surgeelectricestimating4916 I believe Google Editor is now running in a new way, are you using the new modern code editor or still the old one?

[@hfalcon77]

Triggers do not work for scheduled task because if you have triggers then after 20 things do not send emails any more. Triggers are a huge failure of google sheets, need to find how to schedule in the code.

[@bdhis24]

script link need

[@seopoet]

Instead of recreating that html template -- first embed the table in g doc --> then send it as email, but do not attach pdf, just send the doc in e-mail body. This way, you might just saved some time and effort.

[@ExcelGoogleSheets]

This is not Outlook where you can copy a word document as body.

[@seopoet]

@@ExcelGoogleSheets Oh, but it works perfectly when you try manually: File --> Send as attachemnt --> check option "do not attach, send in body". Just wondered if this can be acheived programatically.

[@seopoet]

@@ExcelGoogleSheets The exact commands in US_EN are: GDocs --> File --> Email as attachement --> check Don't attach. Include conetent in the email. Works really well including tables, charts and other stuff.

[@ExcelGoogleSheets]

@@seopoet There is no apps script method to replicate it.

[@AntonioSantos-ve6zv]

I got Unexpected Token ';' on this line: const htmlForEmail = htmlTemplate.evaluate().getContent();

[@ExcelGoogleSheets]

Check previous and next lines.

[@AntonioSantos-ve6zv]

@@ExcelGoogleSheets Sorry to insist on this one. I now get SyntaxError: Unexpected identifier on the same line: const htmlForEmail = htmlTemplate.evaluate().getContent(); ...and this is only when I run it. Saving presents no error.

[@Jossamking]

I love your tutorials but why is not helpful for me is because you do your coding on Google app scripts while I do my coding on vscode. So your technique not working for me. I tried it but keep saying error whenever I want to try out my form