[ASK] SCRIPT AutoSendEmail pada Form Google

Beranda
Komunitas
Tech
Templates & Scripts Stuff
[ASK] SCRIPT AutoSendEmail pada Form Google

RhezaKings

22-06-2013 00:52

[ASK] SCRIPT AutoSendEmail pada Form Google

Permisi para master dan suhu,

to the point,

Ane lagi mau buat Form semacem Angket dari Google Drive, nah ini baru testing aja,
jadi ane mau kalo responder submit form otomatis kita kirim email balesan untuk responder.
Nah abis search di google ane baru temuin 2 cara,
1. ValMerge (kurang efektif karena ga bisa otomatis, bener" otomatis, tapi script ini bisa kirim pesan dengan custom data sesuai apa yang di isi oleh responder)
2. AutoSendEmail (ini yang ane mau, perfect, tapi disini masalahnya)

Spoiler for Code Script nya gan:

Code:

function EmailFormConfirmation() {

 

 // 3-Step Instructions:

 // 1) Place the following formula into an empty cell in the first row, 

 // in a Column to the right of Columns that populate from the form: =indirect("C"&counta(A1:A))

 // Change C in above formula to the Column in your sheet that has email addresses from the form 



 // 2) In the following two program lines, "var sheetname" and "var columnnumber",

 // Change the name of the sheet (currently set to Sheet1, the default name)

 // Change the column number to the number representing the Column that you placed the indirect formula, 

 // from step 1, above

 // i.e. B=2, C=3, D=4, J=10, O=15, T=20, Z=26, AA=27, AZ=52, etc...(currently set to 10, for Column J)

 

 var sheetname = "testing"

 var columnnumber = 5

 

 // 3) After saving this script, select "Triggers" => "Current script's triggers" => Click to add a script

 // Choose this script's name, select "From spreadsheet" and select "On form submit", and then save 

 // 

 // That's it!







 // Main script follows 

 var ss = SpreadsheetApp.getActiveSpreadsheet(); 

 var sheet = ss.getSheetByName(sheetname);

 var email = sheet.getRange(1,columnnumber).getValue(); 



 // Determines row number of most recent form submission and sets it as "lastrow"

 if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {

 var lastrow = sheet.getMaxRows()

 } 

 else {

 var count = 0

 for (var i = 0; i < sheet.getMaxRows(); i++) {

 if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {

 var lastrow = sheet.getMaxRows()-i

 break;

 } 

 }

 }



 // Email address regex (regular expression)

 // Test for valid Email Pattern/Format - Allows any two-letter country code top level domain, 

 // and only specific generic top level domains 

 // (update via: http://en.wikipedia.org/wiki/List_of_top_level_domains) 



 var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;



 var validEmailAddress = emailPattern.test(email); 

 

 // The following sends an email if the email pattern is valid (i.e. if the email address is of an acceptable format) 

 // Edit the text you want in the body of the email and the subject you want (send a test message to yourself to test)

 

 if (validEmailAddress == true) {

 var message = "<BODY>"

 

 + "[color=red]This is test autoemail[/color]"

 + "</BODY>"; 

 MailApp.sendEmail(email, "Form Completion Email", "", {htmlBody: message}); 



 // Returns a confirmation message whether email was sent, in the Column designated in step 1

 // You can change these confirmation messages, or get rid of them altogether by making these

 // lines comment lines by adding the "//" to the beginning of the next 3 lines 

 

 sheet.getRange(lastrow,columnnumber,1,1).setValue("Email Sent");

 } else{

 sheet.getRange(lastrow,columnnumber,1,1).setValue("Email not Sent - Invalid Email Address submitted");

} 

}

Tulisan warna merah itu isi dari pesannya gan, ane uda coba isi form sendiri dan email terikirim langsung secara otomatis (Perfect)
Ane mau edit itu isi pesannya, biar menjadi

Quote:

Terimakasih *nama*telah berpartisipasi bla bla bla.

Nah ane pengen biar isi pesannya itu bisa sesuai sama nama yang diisi di form angket nya gan.

Spoiler for penampakan kolom nama di spreadsheet:

[ASK] SCRIPT AutoSendEmail pada Form Google

Ane ga ngerti apa" soal beginian gan, jadi ane minta tolong sama para master" disini.
atau mungkin ada script lain yang rekomen sesuai dengan permohonan ane.

Simbolis ane cuma bisa kasih +3 emoticon-Cendol (S)

aja sbg ucapan terimakasih emoticon-Smilie

NB: Ane bukan anak komputer sama sekali jadi apa yang selama ini ane bisa yaitu belajar secara otodidak, jadi mohon dimaklumi kalau masalah seperti ini saja ane ga ngerti

Diubah oleh RhezaKings 22-06-2013 00:56

1.2K

Kutip

Balasan

Komentar yang asik ya

Urutan

Terbaru

Terlama

Komentar yang asik ya

Komunitas Pilihan