Automated email when new line is added to Google Sheets

Рет қаралды 3,814

Күн бұрын

The most recent row of data will be used in an HTML email sent with Google Apps Script.
This tutorial will show you one example of how to capture data in Google Sheets and then automatically send an email once the data is entered. This system is built on three Google Tools:
1. Google Colab is used to write a Python script that creates a message, which is sent to Google Sheets
2. Google Sheets tracks each successful run of the script
3. Google Apps Script collects the data pushed to Sheets and then sends an email with the most recent value after it is appended to the sheet
This tutorial will not teach you how to write the Python code but I am considering a series of Python lessons. Please let me know if you'd be interested.

Пікірлер: 15

@mechdom 10 ай бұрын

Hey Matt, This was a great video extremely helpful. Exactly what I was looking for, can't wait to see what else you produce, fantastic conveyor of knowledge you are!!

@mattbrigidi 10 ай бұрын

I can't thank you enough for the kind words - I'm so happy you found this helpful!

@echoesandnoise 6 ай бұрын

i am using a spreadsheet for my contact form. So anything filled on my contact form gets into the google sheets. I used this code for the same sheet to send data as email. It works well on Running the script manually. But i am unable to automate it with triggers for some reason. When someone fills the form i get the data in my sheets but trigger does not automate to send it to my email.

@mattbrigidi 6 ай бұрын

is the contact form a separate workbook that is fed into another sheet?

@echoesandnoise 6 ай бұрын

@@mattbrigidi Contact form is a different html code which i took from some other video "Regarding : Using html contact form to send data to google sheets using google scripts" but under the same scripts project, i added your script. So when i run it manually it works fine. I get the mail. Even while using triggers , if i add data directly to sheets it sends me email with data added. But when i use contact form to do that, it only adds data to sheets, and i dont get any email. Its under same workbook.

@echoesandnoise 6 ай бұрын

@@mattbrigidi I have sent you as email as well with Subject - "Commented on : Automated email when new line is added to Google Sheets" with link to my google sheet and website to check.

@houghtlingc 7 ай бұрын

Is there a way you could show me how to generate the email when a cell is changed to a specified status? I need to notify that a due date is coming up and give details within the row

@mattbrigidi 7 ай бұрын

just for anyone else coming to the comments section - Caleb sent me an email and we are discussing this! I think it's a cool idea and will be pursuing it in a future video. Thanks again for reaching out Caleb!

@latelierfloral 9 ай бұрын

Hi Matt, thank you so much for this tutorial. It almost worked for except the part where you talk about “recent.Response[0][0]” (line 19 of Code.gs) because my sheet has many columns. What should be the code in this case? Also, my sheet is filled up by a Google Form. When I tried filling out the Google Form, I saw my sheet updated, but it did not send me an email.. Did I do something wrong?

@mattbrigidi 9 ай бұрын

I'm sorry to hear that it's not working - i'd be happy to help you figure this out. What I think I'm hearing is that we have two problems: 1. You need to access more columns than the tutorial provides 2. You are looking to send an email "On Form Submit" so that the results of the form (or notification of a new submission) are delivered by email please let me know if the above is not correct ^ If that is the problem here's what I would recommend: Issue No.1: Adding Columns Response[0][0] is a *Two-Dimensional Array*, which is also known as a "list of lists". This means there are two access points - the list you want to access (aka the first "[0]") and the value in the list you want to access (aka the second "[0]"). For example, let's say your "list of lists looks like this: var response = [['first thing', 'second thing', 'third thing']] ^ this is an example of a list of lists with only one list. lists are zero-indexed, which means the first value is 0 instead of 1. so if you wanted to "access" the second value, aka 'second thing', you would need to: 1. access the first list (aka 0 in a zero-indexed list) 2. access the second value (aka 1 in a zero-indexed array) this would look like: response[0][1] let's , say you have more than one list: var response = [['first thing', 'second thing', 'third thing'], ['fourth thing', 'fifth thing', 'sixth thing']] if you want to access the second value from the second list, then you would 1. access the second list (aka 1 in a zero-indexed list) 2. access the second value (aka 1 in a zero-indexed array) this would look like: response[1][1] so if you have 10 columns then your "values" within your list would be: first value: 0 second value: 1 third value: 2 fourth value: 3 and so on where the tenth value is actually 9 because of the zero-index Issue No. 2 You might be using the "on Change" trigger - you'd be better served by using the "On Form Submit" trigger, which is outlined in this video: kzbin.info/www/bejne/boe7hIuHYreAf5I Let me know if you have issues and I'll be happy to help