This is exactly what I was looking for. Thank you for helping me make my first trigger from Google Spreadsheet. This has been very useful and the way you explain is very methodical.
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 TH-cam. Thank's for your great lesson Sir.
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.
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?
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.
3 ปีที่แล้ว
Thank you so much. I'm immediately start using this :-)
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.
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?
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.
@@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.
@@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.
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.
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.
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.
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.
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
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?
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.
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.
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!
The correct way to do this is to use Arrays and Filter method. All you need to know is in this playlist th-cam.com/play/PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD.html
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 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".
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?
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?
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
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(); }
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.
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...
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.
In mail merge, am facing issue to make table in html
3 ปีที่แล้ว
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.
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 .
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.
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.
@@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.
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 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.
@@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.
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
@@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.
![[TH] 2024 PMSL SEA W3D4 | Summer | ฮึดเข้าไว้ ถ้าอยากไปรอบไฟนอล](http://i.ytimg.com/vi/W0eoG-wrVVU/mqdefault.jpg)
You're the best tutor in app scripts out there! Love to learn again and again from you!
You are a saviour for me. I have been digging for this a whole week.
This is exactly what I was looking for. Thank you for helping me make my first trigger from Google Spreadsheet. This has been very useful and the way you explain is very methodical.
Can we learn it to sell online?
Perfect 100%. You have made my life a whole lot easier with the html part, i used to go in circles to achieve this...
ি
Congratulations, excellent contribution
Very helpful with detailed explanation
Just what i needed..thanks..save me time doing it from scratch.. KUDOS..
Thank you, it works like a charm. Love your work!!
Amazingly useful. Thank you!
OMG! Thanks to you and youtube for let share this!
My friend you are doing does amazing things with google sheets!
You made my day......thanks for this video
Thanks mate I've been searching it for a long time
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 TH-cam. Thank's for your great lesson Sir.
Awesome once again!
Thank you so much for sharing the video. It was very helpful.
Man, thank you for these valuable contents!
Really great content!
Muito obrigado, Mestre!
Great Video! i learned a lot while recreating the spreadsheet and the script as i watched (start-stopping) the video. Many thanks!
great to hear!
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.
Great video, thanks a lot
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?
Great stuff. My weakness is HTML so it really helped.
great content
Asombroso!!, Gracias!!
Life saver 👍
You are great sir thank...
The best!
Ajudou muito!!
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.
Thank you so much. I'm immediately start using this :-)
Wonderful!
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.
:O so awesome
Thank you
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?
Great video - do you have a version that runs on V8
Hey Bro, I love your tutorials, they are the most useful I've found on TH-cam. Could you please make a tutorial of Google Sites (editor) + web apps?
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.
@@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.
@@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.
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.
With functions th-cam.com/video/B8118U0z0_k/w-d-xo.html
In code th-cam.com/video/tp50JMxCPSs/w-d-xo.html
Hello, possible to add charts together with the tables?
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.
Thank you for another great video! How would you add an embedded image from the sheet?
Are you asking for an external image link or an actual image that is sent together with email?
@@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.
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.
@@ExcelGoogleSheets That would be awesome!
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?
I didn't understand a thing but i enjoyed watching this
Is it also possible to send a print screen of this table directly instead of doing the whole HTML formatting?
Did you make avaliable this code in some place? I really need it, because my code isn't working.
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.
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
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?
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?
that what i need
Sir ,
Can u help me to create graph in html body of this mail and also image attachment .
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.
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.
Can you share the code?
2allak eh ?
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!
The correct way to do this is to use Arrays and Filter method. All you need to know is in this playlist th-cam.com/play/PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD.html
@@ExcelGoogleSheets Thank you very much for the reply! I'll check out the playlist.
Great video! Is there any source link?
Your videos are pretty awesome and very helpful, wants to know how can i enhance the limit of email by default it is 100
Limits are defined based on the type of account you have with Google developers.google.com/apps-script/guides/services/quotas
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.
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.
@@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".
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?
It's possible, but you'll need to use Outlook to send out emails.
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?
I wonder if there is a way to track if an email got opened/read?
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
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();
}
@@ExcelGoogleSheets thank you ur really amazing 👍🏽
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.
I think this should be possible IF you use installable onEdit trigger, not the simple onEdit.
@@ExcelGoogleSheets can you make a video on that please. There is no such video on TH-cam. Many thanks
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...
Probably you forget this line
"htmlTemplate.tableRangeValues = tableRangeValues"
can you also do a print function to integrate as a button..
Email clients will filter this out for security reasons.
ok thanks...any suggestion?
Could you send a link to this sheet?
The body of the email isn't showing up for me. Any ideas?
How to add. Clickable link in this table can you help me please ???
Can we have all the script code， so that we can test it on the spot. Thank you.
How to bind this function to HTML Button
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.
In mail merge, am facing issue to make table in html
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.
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 .
I'm going through the same issue, how to fix it?
At 19:50, when you put "arrow function", thats not working for google script in my code. showing "unexpected >"
Either switch to V8 or use function(a){ return a} instead of a => { return a}
@@ExcelGoogleSheets thanks, but i am not getting this. What should i replace this with "tablerangevalues.forEach((r,i) => { ?>" ?
"tablerangevalues.forEach(function(r,i) { ?>" ?
Hi I keep getting a syntax error on line 34 “const htmlForEmail...” , error reads unexpected token ‘>’ , I can’t figure out the error
The debugger states the htmlForEmail is undefined
Share the code. I can't guess.
@@ExcelGoogleSheets figured out the error came from the html part
@@andreipop8324 what did you fix on the html part
How do I contact you..
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?
Same here
I was not getting values for this line....const tableRangeValues = ws.getRange(7,2,lr-7,4).getValues()
How can i create date and time-specific trigger
What exactly are you trying to do?
Тема раскрыта не до конца. Почему не сделать вложенный перебор массива?
Hi bro , how to set background image for send email
its only for gmail?
GmailApp for gmail or you can use MailApp to use your default google account email.
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.
script link need
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.
How did you fixed it?
@@zafaraeqbal Open the script in the Apps Script editor.
Select the Run > Enable new Apps Script runtime powered by V8.
@@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.
@@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?
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.
This is not Outlook where you can copy a word document as body.
@@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.
@@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.
@@seopoet There is no apps script method to replicate it.
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
I got Unexpected Token ';' on this line: const htmlForEmail = htmlTemplate.evaluate().getContent();
Check previous and next lines.
@@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.