Membuat Form Service Dengan Form Emailer

 

Hallo Timur 3 🙂

Pada cermi Henny kali ini akan membahas  Membuat Form Service Dengan Form Emailer. Henny udah membuat tutorial cara membuat Form emailer, cermi tersebut bisa dilihat dibawah ini

Berikut ini merupakan script untuk membuat Form Emailer

var VERSION = 3.65;
var NAME = ‘FormEmailer’; //used as menu and settings sheet namevar T = {
sender: “Sender Name”, replyTo: “Reply To”, //please try to keep these first texts short
to: “To”, cc: “Cc”, bcc: “Bcc”,
subject: “Subject”, body: “Body”, html: “html”,fSheet: “Form sheet”, fSheetDESC: “Name of the sheet that holds the form answers”,
qtt: “Qtt Emails”, qttDESC: “Max quantity of emails sent per form submitted”,
qWarn: “Quota warning”, qWarnDESC: “Daily quota threshold that will trigger a warning email”,
qLimit: “Quota limit”, qLimitDESC: “Quota minimum value where FormEmailer will stop sending emails”,
fLoc: “Formulas location”, fLocDESC: “Enter the location using the format: SheetName!A:B”,
closure: “Closure mode”, closureDESC: “What the script should do after your formulas are evaluated”,
closureValues: “values”, closureFormulas: “formulas”, closureClear: “clear”,
quota: “Remaining quota”, quotaDESC: “Amount of your daily quota you got left now”,menuSettings: “Settings”, menuManually: “Process manually”, menuAbout: “About”, menuFix: “Fix”,
version: “{1} – version {2}”, //script name; version number
about1: “Script developed by {1}”, //my name
about2: “Help, samples and tutorials can be found at”, //site link will be in the line bellowstatusEmail: “Email{1} sent”, //email index (will be blank if there”s only one)
statusNot: “Email{1} not sent”, //email index
statusError: “Error sending email{1}: {2}”, //email index; error message
mailError: “Error sending email{1} on line{3}: {2}”, //email index; error message; line number
statusQuota: “Quota limit reached”,
statusMissingField: “Field does not exist: {1}”, //placeholder name
statusFormattingErr: “Error formatting field: {1}”, //full placeholder definition
statusDateFormatErr: “Error formatting date field: {1}”, //full placeholder definitionmanualQuestion: “Which line do you want process?\nAppend an * to the line number to process from it to the end”,
manualInvalid: “\”{1}\” is not a valid number. Aborting.”, //value entered by user
manual1stMult: “The first line is the n.2. I’ll consider that you want to process from the beginning.”,
manual1stSingle: “The line number should be equal to or greater than 2.\nSince the 1st row is the header.”,
manualAfterLast: “The line number you typed is beyond the last one with data.”,
manualQuotaWarn: “Quota warning threshold reached! Continue anyway?”,
manualQuotaLimit: “Quota minimum limit reached\nProcess stopped at line {1}”, //line number
manualSuccessMulti: “All lines from {1} to {2} processed successfully”, //1st line; last line
manualSuccessSingle: “Line {1} processed successfully”, //line number
manualErrors: “There were some errors on the processing, check out the rows status”,yes: “Yes”, no: “No”, ok: “Ok”,
title: “{1} Settings”, //script name
emailTab: “Email{1}”, //tab index (may be empty if there’s only one)
advTab: “Advanced”,
advIntro: “You don’t need to worry with these settings if you don’t want to.”,
advTip1: “*You have to ‘{1}’ and re-open to see the updates on any changes you make here.”, //save and close
advTip2: “Having doubts with these parameters? Want to learn more about {1}?”, //script name
advTip3: “Access the help site at”, //site link bellow
placeholders: “Answers placeholders:”,
addField: “Add field:”,
insertPlaceholder: “insert placeholder”, //there will be an arrow in front of this
saveAndClose: “Save and Close”,sheetError: “{1} informed does not exist!”, //’Form sheet’ parameter name
numberError: “{1} is not a valid number!”, //parameter name
formulasError: “Invalid {1}”, //’formulas location’ parameter name
formulasSheetError: “Invalid sheet for {1}”, //’formulas location’ parameter name
formulasColsError: “Invalid column reference for {1}”, //’formulas location’ parameter name
errorTitle: “Error”,
badSettings: “Your data was not saved.”,
versionConflict: “Config version is {1}, but script’s is {2}. They are not compatible.\n”, //config version; script version
resolveConflict: “You settings version \”{1}\”, is not compatible with the script’s version \”{2}\”. Do you want to overwrite it new default settings?”,  //config version; script version
badConfig: “Your config is damaged or missing!\nDo you want to overwrite it with the default values?”,
badConfigCancel: “Nothing done.\nHere is the error message:\n{1}”, //error message
noProblem: ‘There seem to be no problem with your settings.’,
missingSheet: “Unable to find sheet: {1}”, //sheet name
fSheetMissing: “Your \”{1}: {2}\” cannot be found, please choose a new one and click \”{3}\” to fix”, //fSheet parameter name; fSheet parameter value; yes
cancelMessage: “Canceled”,
errorReportSubject: “{1} errors report”, //script name
errorReportBody: “The following errors occurred on \”{1}\” spreadsheet:\n{2}”, //spreadsheet name; errors
alsoQuota: “Also, your quota is below the warning limit”,
quotaMailSubject: “{1} quota warning”, //script name
quotaMailBody: “Your actual quota is: {1}\nIt is bellow the warning threshold: {2}”, //remaining quota; quota warning

pickLanguage: “Select a language:”,
langIncomplete: “This language was written for an older version of the script and is probably incomplete.”,
pickSheet: “Select the sheet that has your form answers (or data to be merged):”,
install: “Install”,
overwrite: “Do you confirm you want to overwrite your whole \”{1}\” sheet?”, //script name
defaultBody: “Submitted values:”,
defaultSubject: “New form submitted”,
blankSheet: “Your \”{1}\” sheet is blank! Please create your form or basic data structure before installing, so the script can create nice defaults for you.”, //sheet name
statusColumn: “{1} Status”, //script name
relocateStatus: “In this new version of the script, the status column is the 1st instead of last. I have moved it for you, please verify.”,
statusComment: “This column must always be the first one”,
settingsComment: “Please do NOT modify this manually! Use the Settings interface”,
header: “{1}\nRunning automatically:\nYour settings are save here:”, //script’s name (this text must always have 3 lines)
instructions: “Settings tips:\n”+ //’Form Sheet’ parameter name
“If you want to rename your form sheet, remember to rename the \”{1}\” parameter at the same time.\n”+
“A very good practice is to have the first row of data with some bogus values so you can make some tests before running it on the whole data set.\n”+
“The placeholders may be used in any field, e.g. To, Subject, etc.\n”+
“The \”#\” character is used to delimit the placeholders’ boundaries, if you want to use this char in any field, write it two times \”##\”. They will be replace by only one.\n”+
“You may provide formatting instructions in the placeholder by using the \”|\” char after the field name. Take a look at the available format in the help site.\n”+
http://sites.google.com/site/FormEmailer\n“+
“\n”+
“New install instructions:\n”+
“You need to set up the trigger so the script can run automatically. Steps:\n”+
“- Click the menu \”Tools\” > \”Script editor…\”\n”+
“- In the editor’s window\n”+
“– Make sure the {1} script is opened and selected (it normally is)\n”+
“– Click the menu \”Triggers\” > \”Current script’s triggers…\”\n”+
“— Click the only link and set up a new trigger as follows:\n”+
“timeDriven – Time-driven – Minutes timer – Every minute\n”+
“— Click save\n”+
“– You may close the editor\n”+
“The status indication in this sheet (cell B2) may take some minutes to update.”
};

var ef = ‘sender,replyTo,to,cc,bcc,subject,body,html’.split(‘,’); //email fields
var ap = ‘fSheet,qtt,qWarn,qLimit,fLoc,closure,quota’.split(‘,’); //advanced parameters
var s = {}; //settings
var c = {}; //current variables
var ddf = ‘M/d/yyyy H:mm:ss’; //default date format
var nbs = unescape(‘%A0’); //non-breaking space

var _appSize = [760,480];

//css
var _emailField = {
width: ‘480px’,
fontSize: ’12px’
};
var _body = {
height: ‘234px’,
minHeight: ‘234px’, //to avoid resizable textArea of some browsers
maxHeight: ‘234px’,
width: ‘100%’,
minWidth: ‘100%’,
maxWidth: ‘100%’ //this one does not work
};
var _placeHolders = {
width: ‘230px’,
fontSize: ’12px’
};
var _link = {
color: ‘blue’,
cursor: ‘hand’
};
var _right = {
width: ‘100%’,
textAlign: ‘right’
};
var numParam = {
width: ’70px’,
textAlign: ‘right’
};
var textParam = {
width: ‘150px’
};

//This is the monitoring function. You should set up a time-driven trigger to run it.
function timeDriven(e) {
if( e !== undefined )
errorHandling_(monitor_,true);
else
monitor_(false);
}

function about() {
if( !loadOrDefaults_() )
return;
var app = UiApp.createApplication().setTitle(repl_(T.version, NAME, VERSION)).setWidth(340).setHeight(100);
app.add(app.createScrollPanel().setSize(‘340px’, ‘100px’).add(
app.createVerticalPanel().setSpacing(3).setStyleAttribute(‘margin’, ’10px’) .add(
app.createLabel(repl_(T.about1, ‘Henrique G. Abreu’))).add(
app.createLabel(T.about2)).add(
app.createAnchor(‘http://sites.google.com/site/formemailer‘, ‘http://sites.google.com/site/formemailer’))));
SpreadsheetApp.getActiveSpreadsheet().show(app);
}

function onOpen() {
c.ss = c.ss || SpreadsheetApp.getActiveSpreadsheet();
c.cs = c.cs || c.ss.getSheetByName(NAME);
if( c.cs != null ) {
try {
loadConfig_();
c.ss.removeMenu(NAME+’ ERROR’);
c.ss.addMenu(NAME, [{name:T.menuSettings, functionName: ‘settings’},
{name:T.menuManually, functionName: ‘processManually’},
{name:T.menuAbout, functionName: ‘about’}]);
} catch(e) {
c.ss.removeMenu(NAME);
c.ss.addMenu(NAME+’ ERROR’, [{name:T.menuFix, functionName: ‘fix’}])
}
checkTrigger_(false);
} else //language pack will be available only after the install
c.ss.addMenu(NAME, [{name:’Install’, functionName: ‘install’}]);
}

function onInstall() {
onOpen();
}

function onEdit(e) {
c.ss = e.source;
c.cs = c.ss.getSheetByName(NAME);
if( c.cs != null )
checkTrigger_(false);
}

function monitor_(trigger) {
loadConfig_();
checkTrigger_(trigger);
var last = c.fs.getLastRow();
if( last == 1 )
return null;
var status = c.fs.getRange(2,1,last-1,1).getValues();
for( var i = 0; i < status.length; i++ )
if( status[i][0] === ” )
doIt_(i+2);
}

function doIt_(line) {
var raw = c.fs.getRange(line,2,1,c.size).getValues()[0];
var answers = {};
for( var i = 0; i < raw.length; i++ )
answers[c.questions[i]] = raw[i];
if( c.fl ) {
var all = c.fl.getRange(line,c.fc[0],1,c.fc[1]);
var values = all.setFormulasR1C1([c.formulas]).getValues()[0];
for( var i = 0, j = c.fl === c.fs ? c.fc[0]-2 : raw.length; i < values.length; ++i, ++j )
answers[c.questions[j]] = values[i];
}
var status = [];
for( var i = 0; i < s.qtt; ++i ) {
if( s.quota > s.qLimit ) {
try {
var email = {};
var html = s[‘html’+i] == ‘true’;
for( var j = 0; j < ef.length-1; ++j ) {
var parts = s[ef[j]+i].split(“#”);
email[ef[j]] = parts[0];
for( var k = 1; k < parts.length; k+=2 )
email[ef[j]] += (parts[k] != ” ? format_(answers,parts[k],status,html) : ‘#’) + parts[k+1];
}
if( email.to == ” ) {
status.push(repl_(T.statusNot, s.qtt == 1 ? ” : i+1));
continue;
}
var args = {name:email.sender, cc:email.cc, bcc:email.bcc};
if( html ) args.htmlBody = email.body;
if( email.replyTo == ‘noReply’ ) args.noReply = true;
else if( email.replyTo != ” ) args.replyTo = email.replyTo;
MailApp.sendEmail(email.to, email.subject, html ? ‘html’ : email.body, args);
status.push(repl_(T.statusEmail, s.qtt == 1 ? ” : i+1));
var m = [email.cc != ” || email.bcc != ” ? ‘,’ : ”,
email.cc != ” && email.bcc != ” ? ‘,’ : ”];
s.quota -= (email.to + m[0] + email.cc + m[1] + email.bcc).
replace(/”[^”]*”/g,”).split(/,/).length;
} catch(e) {
status.push(repl_(T.statusError, s.qtt == 1 ? ” : i+1, e));
c.err.push(repl_(T.mailError, s.qtt == 1 ? ” : i+1, e, line));
}
} else {
status.push(T.statusQuota);
if( c.err.length == 0 || !startsWith_(c.err[c.err.length-1], T.statusQuota) )
c.err.push(T.statusQuota+’. ‘+new Date());
break;
}
}
c.fs.getRange(line,1).setValue(status.join(‘; ‘));
if( c.fl && line != 2 ) {
if( s.closure === ‘values’ )
all.setValues([values]);
else if( s.closure === ‘clear’ )
all.clearContent();
//else formulas > just leave them there
}
}

function format_(answers,part,status,html) {
var t;
if( answers[part] !== undefined ) {
t = [part,”];
} else {
t = part.match(/^(.+)\|([^\|]*)$/);
if( t == null || answers[t[1]] == null ) {
status.push(repl_(T.statusMissingField, t == null ? part : t[1]));
return ”;
}
t = t.slice(1);
}
var v = answers[t[0]];
try {
if( t.length == 1 )
t.push(”);
if( typeof(v) == ‘object’ ) {
try {
v = Utilities.formatDate(v, c.tz || (c.tz = c.ss.getSpreadsheetTimeZone()), t[1] == ” ? ddf : t[1]);
} catch(e) {
status.push(repl_(T.statusDateFormatErr, part));
v = Utilities.formatDate(v, c.tz, ddf);
}
} else if( typeof(v) == ‘number’ && t[1] != ” && t[1] != ‘s’ ) {
var p = t[1].length-1;
if( t[1].charAt(p) == ‘%’ ) {
v *= 100;
p–;
}
if( t[1].charAt(p).match(/\d/) ) { //number of decimals
v = v.toFixed(+t[1].charAt(p));
p–;
} else
v = v.toString();
var s = v.split(‘\.’);
var sep = t[1].charAt(p);
p–;
if( p >= 0 && t[1].charAt(p) != ‘(‘ ) { //thousands sep
s[0] = s[0].split(”).reverse().join(”).match(/\d{1,3}/g).join(t[1].charAt(p)).split(”).reverse().join(”);
p–;
}
if( v.charAt(0) == ‘-‘ ) {
if( p >= 0 && t[1].charAt(p) == ‘(‘ ) {
s[0] = ‘(‘ + s[0];
s[1] = s[1] + ‘)’;
} else
s[0] = ‘-‘ + s[0];
}
v = s.join(sep);
} else {
v = v.toString();
if( t[1] != ” && t[1].charAt(0) == ‘s’ ) {
if( t[1].charAt(1) == ‘U’ )
v = v.toUpperCase();
else if( t[1].charAt(1) == ‘D’ )
v = v.toLowerCase();
else if( t[1].charAt(1) == ‘P’ && v.length > 0 )
v = v.replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase()+txt.substr(1).toLowerCase();});
}
if( html && (t[1] == ” || t[1].charAt(1) != ‘H’) )
v = v.replace(/&/g,’&amp;’).replace(/</g,’&lt;’).replace(/>/g,’&gt;’).replace(/”/g,’&quot;’);
}
} catch(e) {
status.push(repl_(T.statusFormattingErr, part));
}
return v;
}

function processManually() {
if( !loadOrDefaults_() )
return;
var v = showInput_(T.manualQuestion);
if( v == ‘cancel’ )
return null;
var multi = false;
if( v.charAt(v.length-1) == ‘*’ ) {
multi = true;
v = v.substring(0,v.length-1);
}
var start = parseInt(v);
var last = c.fs.getLastRow();
if( isNaN(start) ) {
return showMsg_(repl_(T.manualInvalid, v));
} else if( start < 2 ) {
if( multi ) {
start = 2;
showMsg_(T.manual1stMult);
} else {
return showMsg_(T.manual1stSingle);
}
} else if( start > last )
return showMsg_(T.manualAfterLast);
if( !multi )
last = start;
var cont = false;
for( var line = start; line <= last; line++ ) {
if( s.quota <= s.qLimit )
return showMsg_(repl_(T.manualQuotaLimit, line));
if( s.quota <= s.qWarn && !cont ) {
cont = ‘yes’ == showMsg_(T.manualQuotaWarn, ‘YES_NO’);
if( !cont )
return null;
}
doIt_(line);
}
showMsg_(c.err.length == 0 ? repl_(last > start ? T.manualSuccessMult : T.manualSuccessSingle, start, last) : T.manualErrors);
}

function checkTrigger_(runningNow) {
var state = runningNow || (new Date().getTime())-(+sget_(‘lastRun’,0)) < 10*60*100; //10 min
var r = c.cs.getRange(‘B2’);
if( r.getValue() != (state ? T.yes : T.no) ) {
if( state )
r.setValue(T.yes).setFontColor(‘#008000’);
else
r.setValue(T.no).setFontColor(‘#993300’);
}
}

function settings() {
if( !loadOrDefaults_() )
return;
var app = UiApp.createApplication().setTitle(repl_(T.title, NAME)).setWidth(_appSize[0]).setHeight(_appSize[1]);
var tabPanel = app.createDecoratedTabPanel().setId(‘tabPanel’);

var handlers = {insert: app.createServerClickHandler(‘insertQuestion_’).addCallbackElement(tabPanel),
link: app.createServerClickHandler(‘linkClick_’),
save: app.createServerClickHandler(‘save_’).addCallbackElement(tabPanel)};
var tabs = [];
for( var i = 0; i < s.qtt; i++ )
tabs.push(createEmailTab_(app,i,handlers));
if( s.qtt == 1 )
tabs[0].name = repl_(T.emailTab, ”);

var adv = app.createGrid(ap.length, 3);
var x = 0;
for(; x < ap.length-2; x++ )
adv.setWidget(x, 0, createLabel_(app,T[ap[x]]+’:’)).
setWidget(x, 1, createTextBox_(app,ap[x])).
setWidget(x, 2, app.createLabel(T[ap[x]+’DESC’]));
var listBox = css_(app.createListBox().setName(ap[x]), {i:textParam, fontSize:_placeHolders.fontSize});
var opts = [[‘formulas’,T.closureFormulas], [‘values’,T.closureValues], [‘clear’,T.closureClear]];
for( var i = 0; i < opts.length; ++i ) {
listBox.addItem(opts[i][1], opts[i][0]);
if( s[ap[x]] === opts[i][0] )
listBox.setSelectedIndex(i);
}
adv.setWidget(x, 0, createLabel_(app,T[ap[x]]+’:’)).
setWidget(x, 1, listBox).
setWidget(x, 2, app.createLabel(T[ap[x]+’DESC’]));
x++;
adv.setWidget(x, 0, createLabel_(app,T[ap[x]]+’:’)).
setWidget(x, 1, createTextBox_(app,ap[x]).setEnabled(false)).
setWidget(x, 2, app.createLabel(T[ap[x]+’DESC’]));
var save = app.createButton(T.saveAndClose).addClickHandler(handlers.save);
tabs.push({name:T.advTab, tab:app.createVerticalPanel().setSpacing(10).add(
app.createLabel(T.advIntro).setStyleAttribute(‘fontWeight’, ‘bold’)).add(
adv).add(
app.createLabel(repl_(T.advTip1, T.saveAndClose))).add(
app.createLabel(‘ ‘)).add(
app.createLabel(repl_(T.advTip2, NAME))).add(
app.createHorizontalPanel().add(
app.createLabel(T.advTip3+nbs)).add(
app.createAnchor(‘sites.google.com/site/FormEmailer‘, ‘http://sites.google.com/site/formemailer’))).add(
save).setCellHorizontalAlignment(save, UiApp.HorizontalAlignment.CENTER)});

for( var i in tabs )
tabPanel.add(tabs[i].tab.setSize(‘100%’,’100%’), tabs[i].name);
tabPanel.selectTab(0).setAnimationEnabled(true);
app.add(tabPanel.setSize(‘100%’,’100%’));
c.ss.show(app);
}

function createEmailTab_(app,idx,h) {
var questions = app.createListBox().setName(‘question’+idx);
for( var i in c.questions )
questions.addItem(c.questions[i]);
var tp = app.createVerticalPanel().setSpacing(0).setWidth(‘100%’).setHorizontalAlignment(UiApp.HorizontalAlignment.RIGHT).add(
app.createGrid(1,2).setCellPadding(0).setCellSpacing(0).setWidget(
0, 0, createLabel_(app,T.placeholders)).setWidget(
0, 1, css_(questions, _placeHolders)));

var grid = app.createGrid(ef.length-1, 2).setCellSpacing(0).setCellPadding(0).setId(‘grid’).setWidth(‘100%’);
var links = app.createHorizontalPanel().setSpacing(3).add(app.createLabel(msg_(T.addField)));
for( var i = 0; i < ef.length-2; i++ ) {
var visible = s[ef[i]+idx] != ” || i == 2;
grid.setWidget(
i, 0, createLabel_(app,T[ef[i]]+’:’,ef[i]+idx+’:’).setVisible(visible)).setWidget(
i, 1, app.createGrid(1,2).setId(‘box’+ef[i]+idx).setWidth(‘100%’).setCellPadding(0).setCellSpacing(0).setWidget(
0, 0, createTextBox_(app, ef[i]+idx, _emailField)).setWidget(
0, 1, createLabel_(app, unescape(‘%u2190%20’)+T.insertPlaceholder+nbs, ‘ip’+ef[i]+idx, h.insert)).setVisible(visible));
if( !visible )
links.add(createLabel_(app, T[ef[i]].replace(‘ ‘,”), ‘link’+ef[i]+idx, h.link));
}

var html = app.createCheckBox(T.html+nbs).setValue(s[‘html’+idx]==’true’).setName(‘html’+idx);
var vp = app.createHorizontalPanel().setSize(‘100%’, _body.height).setVerticalAlignment(UiApp.VerticalAlignment.TOP).add(
app.createVerticalPanel().setWidth(‘100%’).setHorizontalAlignment(UiApp.HorizontalAlignment.RIGHT).add(
css_(createLabel_(app,T.body+’:’), {marginTop:’5px’})).add(
html.setWidth(‘100%’)));
grid.setWidget(ef.length-2, 0, vp).setWidget(
ef.length-2, 1, css_(app.createTextArea().setText(s[‘body’+idx]).setId(‘body’+idx).setName(‘body’+idx), _body));

var save = app.createButton(T.saveAndClose).addClickHandler(h.save);
var sidesW = (_appSize[0]-180)/2;
var bp = app.createHorizontalPanel().setWidth(‘100%’).add( //bottom panel
links).setCellWidth(links, sidesW+’px’).add(
save).setCellWidth(save, (_appSize[0]-sidesW*2-15)+’px’).setCellHorizontalAlignment(save, UiApp.HorizontalAlignment.CENTER).add(
createLabel_(app, unescape(‘%u2191%20′)+T.insertPlaceholder+nbs,’ipbody’+idx,h.insert).setWidth(sidesW+’px’));
return {name:repl_(T.emailTab, idx+1), tab:css_(app.createVerticalPanel().add(tp).add(grid).add(bp), {width:’100%’, maxWidth:_appSize[0]+’px’})};
}

function linkClick_(e) {
var app = UiApp.getActiveApplication();
var id = e.parameter.source.substr(4);
app.getElementById(‘box’+id).setVisible(true);
app.getElementById(id+’:’).setVisible(true);
app.getElementById(‘link’+id).setVisible(false);
return app;
}

function insertQuestion_(e) {
var app = UiApp.getActiveApplication();
var id = e.parameter.source.substr(2);
var idx = id.match(/\d*$/);
var value = e.parameter[id]+’#’+e.parameter[‘question’+idx]+’#’;
app.getElementById(id).setText(value).setFocus(true);
return app;
}

function save_(e) {
var app = UiApp.getActiveApplication();
c.ss = SpreadsheetApp.getActiveSpreadsheet();

//load language
c.cs = c.ss.getSheetByName(NAME);
var r = c.cs.getRange(‘B3’);
T = JSON.parse(r.getValue()).T;

//read settings
for( var i in ap )
s[ap[i]] = e.parameter[ap[i]];
var idx = 0;
for( ; e.parameter[‘to’+idx] !== undefined; idx++ )
for( var i in ef )
s[ef[i]+idx] = e.parameter[ef[i]+idx];
//do validations
c.ss = SpreadsheetApp.getActiveSpreadsheet();
var id = ‘fSheet’;
c.fs = c.ss.getSheetByName(s[id]);
if( c.fs == null )
return showErrorDialog_(app,idx,id,T.sheetError);
s[id] = c.fs.getName();

var toNum = {qtt:1,qWarn:0,qLimit:0};
for( var id in toNum ) {
var num = parseInt(s[id].match(/^\d+$/));
if( isNaN(num) || num < toNum[id] || id == ‘qtt’ && num > 10 )
return showErrorDialog_(app,idx,id,T.numberError);
s[id] = num;
}

id = ‘fLoc’;
if( s[id] != ” ) {
var pos = s[id].lastIndexOf(‘!’);
if( pos == -1 )
return showErrorDialog_(app,idx,id,T.formulasError);
var name = s[id].substr(0,pos);
c.fl = name == s.fSheet ? c.fs : c.ss.getSheetByName(name);
if( c.fl == null )
return showErrorDialog_(app,idx,id,T.formulasSheetError);
var maxCol = c.fl.getMaxColumns();
var fc = s[id].substr(pos+1).split(‘:’);
for( var i = 0; i < fc.length; i++ )
if( fc[i].match(/^[a-zA-Z]{1,2}2?$/) ) {
var last = fc[i].length-1;
fc[i] = changeColumnNotation_(fc[i].charAt(last)==’2′ ? fc[i].substr(0,last) : fc[i],true);
if( fc[i] > maxCol )
return showErrorDialog_(app,idx,id,T.formulasColsError);
}
if( fc.length == 1 )
fc.push(fc[0]);
s[id] = c.fl.getName()+’!’+changeColumnNotation_(fc[0],false)+’:’+changeColumnNotation_(fc[1],false);
}

c.size = c.fs.getLastColumn()-1;
c.questions = parseQuestions_(c.fs.getRange(1,2,1,c.size).getValues()[0]);
for( var i = s.qtt-1; i > 0; i– ) {
if( s[ef[0]+i] == null )
createDefaultEmail_(i);
else
break;
}
//TODO validade emails fields
//look for the questions
//check the adv formattings
//verify the ‘To’ field; can even take a peek at the real answers
//parse the html

//save settings
delete s.quota;
s.Version = VERSION;
r.setValue(JSON.stringify({s:s, T:T}));
app.close();
return app;
}

function showErrorDialog_(app,tab,id,text) {
app.getElementById(‘tabPanel’).setVisible(false).selectTab(tab);
app.add(css_(app.createDialogBox(false,true).setId(‘errorDialog’).setText(T.errorTitle),
{position:’fixed’, top:’30%’, left:’35%’}).add(
app.createVerticalPanel().add(
app.createLabel(repl_(text,T[id]))).add(app.createLabel(T.badSettings)).add(
app.createButton(T.ok, app.createServerClickHandler(‘closeDialog_’)).setId(‘focus’+id))));
return app;
}

function closeDialog_(e) {
var app = UiApp.getActiveApplication();
app.getElementById(‘errorDialog’).setVisible(false);
app.getElementById(‘tabPanel’).setVisible(true);
app.getElementById(e.parameter.source.substr(5)).setFocus(true);
return app;
}

function loadConfig_() {
c.ss = c.ss || SpreadsheetApp.getActiveSpreadsheet();
c.cs = c.cs || getSheet_(NAME);
var saved = JSON.parse(c.cs.getRange(‘B3’).getValue());
s = saved.s;
T = saved.T;
sVersion = +s.Version;
var compat = 3.6;
if( isNaN(sVersion) || sVersion < compat || sVersion > VERSION )
throw repl_(T.versionConflict, s.Version, VERSION, NAME).replace(/ /g,nbs);
c.origQuota = s.quota = getQuotaSecured_();

c.fs = getSheet_(s.fSheet);
c.err = [];
c.size = c.fs.getLastColumn()-1;
var headers = c.fs.getRange(1,2,1,c.size).getValues()[0];
if( s.fLoc != ” ) {
var pos = s.fLoc.lastIndexOf(‘!’);
var name = s.fLoc.substr(0,pos);
c.fc = s.fLoc.substr(pos+1).split(‘:’);
for( var i = 0; i < 2; i++ )
c.fc[i] = changeColumnNotation_(c.fc[i],true);
c.fc[1] = c.fc[1]-c.fc[0]+1;
for( var i = 2; i < 4; i++ ) //custom closure mode
c.fc[i] = c.fc.length > i ? +c.fc[i] : 0;
if( name == s.fSheet ) {
c.fl = c.fs;
} else {
c.fl = c.ss.getSheetByName(name);
headers = headers.concat(c.fl.getRange(1,c.fc[0],1,c.fc[1]).getValues()[0]);
}
c.formulas = c.fl.getRange(2,c.fc[0],1,c.fc[1]).getFormulasR1C1()[0];
}
c.questions = parseQuestions_(headers);
}

function fix() {
if( loadOrDefaults_() ) {
onOpen();
showMsg_(T.noProblem);
}
}

function loadOrDefaults_() {
try {
loadConfig_();
return true;
} catch(e) {
onOpen();
var conflict = e == repl_(T.versionConflict, s.Version, VERSION, NAME).replace(/ /g,nbs);
var app = UiApp.createApplication().setTitle(NAME).setHeight(140);
var panel = app.createVerticalPanel().setHorizontalAlignment(UiApp.HorizontalAlignment.CENTER).setSpacing(10);
var err = app.createVerticalPanel().setHorizontalAlignment(UiApp.HorizontalAlignment.CENTER);
var closeHandler = app.createServerHandler(‘closeApp_’);
var noHandler;
var yesHandler = ‘install’;
var msg;
if( !conflict ) {
if( s.Version && !c.fs ) {
var sheetList = app.createListBox().setName(“fSheet”);
var sheets = c.ss.getSheets();
for( var i in sheets ) {
var name = sheets[i].getName();
if( name != NAME )
sheetList.addItem(name);
}
yesHandler = ‘fixSheetName_’;
msg = app.createGrid(1,2).setWidth(‘100%’).setWidget(
0, 0, app.createLabel(repl_(T.fSheetMissing, T.fSheet, s.fSheet, T.yes))).setWidget(
0, 1, css_(sheetList, _placeHolders));
} else
msg = T.badConfig;
var msgs = repl_(T.badConfigCancel, e).replace(/ /g,nbs).split(‘\n’);
for( var i in msgs )
err.add(app.createLabel(msgs[i]).setWidth(‘100%’));
err.add(app.createButton(T.ok, closeHandler));
noHandler = app.createClientHandler().forTargets(panel).setVisible(false).forTargets(err).setVisible(true);
} else {
msg = T.resolveConflict;
noHandler = closeHandler;
}
panel.setWidth(‘100%’).add(
typeof(msg) == “string” ? app.createLabel(repl_(msg, s.Version, VERSION, NAME)) : msg).add(
app.createFlowPanel().add(
app.createButton(T.yes, app.createServerHandler(yesHandler).addCallbackElement(panel))).add(
app.createButton(T.no, noHandler)));
c.ss.show(app.add(panel).add(err.setVisible(false)));
return false;
}
}

function fixSheetName_(e) {
c.ss = SpreadsheetApp.getActiveSpreadsheet();
c.cs = getSheet_(NAME);
var r = c.cs.getRange(‘B3′);
var saved = JSON.parse(r.getValue());
saved.s.fSheet = e.parameter.fSheet;
r.setValue(JSON.stringify(saved));
onOpen();
return closeApp_();
}

function closeApp_() {
return UiApp.getActiveApplication().close();
}

function parseQuestions_(q) {
var map = {};
for( var i = 0; i < q.length; i++ ) {
q[i] = q[i].toString();
q[i] = q[i].length > 70 ? q[i].substr(0,70)+’…’ : q[i];
if( map[q[i]] !== undefined ) {
if( map[q[i]].length == 1 )
q[map[q[i]][0]] += ‘(‘+changeColumnNotation_(map[q[i]][0]+2,false)+’)’;
map[q[i]].push(i);
q[i] += ‘(‘+changeColumnNotation_(i+2,false)+’)’;
} else
map[q[i]] = [i];
}
return q; //useful for chaining
}

//change a column notation between number and letter, e.g. B <> 2; AB <> 28
function changeColumnNotation_(col,toNumber) {
if( toNumber ) { //since the greatest value is 256(IV). I prefer this ‘if’ based solution over a loop one
var c = col.toUpperCase();
var v = c.charCodeAt(0)-64;
return c.length > 1 ? v*26 + c.charCodeAt(1)-64 : v;
} else {
var toS = function(c) { return String.fromCharCode(c+64); }
return col < 27 ? toS(col) : toS(Math.floor((col-1)/26)) + toS((col-1)%26+1);
}
}

function createDefaults_() {
s.Version = VERSION;
//Form sheet,Qtt Emails,Quota warning,Quota limit,Formulas location,Closure mode
var apv = [c.fs.getName(),1,150,30,”,T.closureValues];
for( var i in apv )
s[ap[i]] = apv[i];
c.questions = parseQuestions_(c.fs.getRange(1,2,1,c.fs.getLastColumn()-1).getValues()[0]);
createDefaultEmail_(0);
}

function createDefaultEmail_(idx) {
var email = ‘user@example.com‘;
try { email = Session.getEffectiveUser().getEmail(); } catch(suppress) {}
var body = T.defaultBody+'<br>\n<b>’+c.questions[0]+’: </b>#’+c.questions[0]+’|M/d/yyyy H:mm:ss#<br>\n’;
for( var i = 1; i < c.questions.length; i++ )
body += ‘<b>’+c.questions[i]+’: </b>#’+c.questions[i]+’#<br>\n’;
//sender,replyTo,   to,        cc,bcc, subject,          body, html
var v = [NAME, ”, email, ”, ”, T.defaultSubject, body, ‘true’];
for( var i in ef )
s[ef[i]+idx] = v[i];
}

function install(e) {
var overwrite = e !== undefined ? true : false;
var app = UiApp.createApplication().setTitle(NAME).setWidth(520).setHeight(180);
c.ss = c.ss || SpreadsheetApp.getActiveSpreadsheet();
var langDefinitionUrl = ‘https://sites.google.com/site/formemailer/translate/languages.txt?attredirects=0&d=1‘;
var langs = UrlFetchApp.fetch(langDefinitionUrl).getContentText().split(‘\n’);
var locale = SpreadsheetApp.getActive().getSpreadsheetLocale();
var myLang = locale.split(‘_’)[0];
var found = false;
var langList = css_(app.createListBox().setName(“language”), _placeHolders);
var langMap = {};
var langCompat = 0;
var firstLang = null;
var count = 0;
for( var i in langs ) {
var csv = langs[i].split(‘,’);
if( csv[0] == ‘versions’ ) {
langCompat = 0;
for( var j = 1; j < csv.length; ++j )
if( +csv[j] == VERSION ) {
langCompat = 1;
break;
} else if( +csv[j] < VERSION )
langCompat = 2;
} else if( langCompat != 0 && langMap[csv[0]] === undefined ) {
langMap[csv[0]] = 1;
if( firstLang === null ) firstLang = csv[2];

langList.addItem(csv[1]+(langCompat == 1 ? ” : ‘*’), langCompat+’,’+csv[2]);
if( csv[0] == locale || !found && csv[0].split(‘_’)[0] == myLang ) {
found = csv[2];
langList.setSelectedIndex(count);
}
++count;
}
}

eval(“pack=”+UrlFetchApp.fetch(found || firstLang).getContentText());
for( var i in pack )
T[i] = pack[i];

var sheetList = css_(app.createListBox().setName(“fSheet”), _placeHolders);
var sheets = c.ss.getSheets();
found = false;
for( var i in sheets ) {
var name = sheets[i].getName();
if( name != NAME )
sheetList.addItem(name);
else
found = true;
}

var panel = app.createVerticalPanel().setHorizontalAlignment(UiApp.HorizontalAlignment.CENTER).setSpacing(5);
panel.add(
app.createGrid(2, 2).setWidget(
0, 0, app.createLabel(T.pickLanguage).setId(‘pickLanguage’)).setWidget(
0, 1, langList.addChangeHandler(app.createServerHandler(‘langChange_’).addCallbackElement(langList))).setWidget(
1, 0, app.createLabel(T.pickSheet).setId(‘pickSheet’)).setWidget(
1, 1, sheetList)).add(
css_(app.createLabel(T.langIncomplete).setId(‘langIncomplete’).setVisible(false), {fontStyle:’italic’}));
var button = app.createButton(T.install, app.createServerHandler(‘proceedInstall_’).addCallbackElement(panel)).setId(‘install’);
panel.add(
app.createCheckBox(repl_(T.overwrite, NAME)).setName(‘overwrite’).setId(‘overwrite’).setValue(overwrite).setVisible(found).
addValueChangeHandler(app.createClientHandler().forTargets(button.setEnabled(!found||overwrite)).setEnabled(false)).
addValueChangeHandler(app.createServerHandler(‘overwrite_’).addCallbackElement(panel)));
panel.add(button);
c.ss.show(app.add(panel));
}

function langChange_(e) {
try{
var app = UiApp.getActiveApplication();
var lang = e.parameter.language.split(‘,’);
eval(“pack=”+UrlFetchApp.fetch(lang[1]).getContentText());
var update = [‘pickLanguage’,’pickSheet’,’install’,’overwrite’,’langIncomplete’];
for( var i = 0; i < update.length; ++i )
app.getElementById(update[i]).setText(repl_(pack[update[i]] === undefined ? T[update[i]] : pack[update[i]], NAME));
app.getElementById(‘langIncomplete’).setVisible(lang[0] == ‘2’);
} catch(err) {
app.add(app.createLabel(String(err)));
}
return app;
}

function overwrite_(e) {
var app = UiApp.getActiveApplication();
if( e.parameter.overwrite == ‘true’ )
app.getElementById(‘install’).setEnabled(true);
return app;
}

function proceedInstall_(e) {
eval(“pack=”+UrlFetchApp.fetch(e.parameter.language.split(‘,’)[1]).getContentText());
for( var i in pack )
T[i] = pack[i];

c.ss = SpreadsheetApp.getActiveSpreadsheet();

c.fs = getSheet_(e.parameter.fSheet);
if( c.fs.getLastColumn() == 0 ) {
var app = UiApp.createApplication().setTitle(NAME).setHeight(100);
app.add( app.createVerticalPanel().setHorizontalAlignment(UiApp.HorizontalAlignment.CENTER).add(
app.createLabel(repl_(T.blankSheet, c.fs.getName()))).add(
app.createButton(T.ok, app.createServerHandler(‘closeApp_’))));
return c.ss.show(app);
}

var col = c.fs.getRange(1,1);
var val = col.getValue();
var status1 = repl_(T.statusColumn, NAME);
var status2 = NAME+’ Status’; //old default english name
var relocateStatus = false;
if( val != status1 ) {
if( val != status2 ) {
c.fs.insertColumnBefore(1).setColumnWidth(1, 133).
getRange(1,1).setValue(status1).setComment(T.statusComment);

var lastCol = c.fs.getLastColumn();
val = c.fs.getRange(1, lastCol).getValue();
if( val == status1 || val == status2 ) {
relocateStatus = true;
var qttRows = c.fs.getLastRow()-1;
c.fs.getRange(2, lastCol, qttRows, 1).moveTo(c.fs.getRange(2, 1, qttRows, 1));
c.fs.deleteColumn(lastCol);
}
} else
col.setValue(status1);
}
createDefaults_();

c.cs = c.ss.getSheetByName(NAME);
if( c.cs != null ) {
c.cs.clear();
} else
c.cs = c.ss.insertSheet(NAME);
var header = repl_(T.header, NAME).split(‘\n’);
var prepare = [];
for( var i in header )
prepare.push([header[i]]);
c.cs.getRange(“A1:A3″).setValues(prepare);
var instructions = repl_(T.instructions, T.fSheet).split(‘\n’);
prepare = [];
var boldLine = 6;
for( var i in instructions ) {
prepare.push([instructions[i],”]);
if( boldLine == 6 && instructions[i] == ” )
boldLine += +i;
}
c.cs.getRange(5, 1, instructions.length, 2).setValues(prepare).mergeAcross().setWrap(true);
c.cs.getRange(‘A1:B1’).mergeAcross().setHorizontalAlignment(‘center’).setFontSize(14);
c.cs.getRange(‘B2’).setValue(‘No’).setFontColor(‘#993300’).setHorizontalAlignment(‘left’).setFontWeight(‘bold’);
c.cs.getRange(‘B3’).setWrap(false).setValue(JSON.stringify({s:s, T:T})).setComment(T.settingsComment);
c.cs.getRange(‘C3’).setValue(‘ ‘);
c.cs.getRange(‘A5’).setFontWeight(‘bold’);
c.cs.getRange(boldLine,1).setFontWeight(‘bold’);
c.cs.setColumnWidth(1,217).setColumnWidth(2,342);
onOpen();

if( relocateStatus ) {
var app = UiApp.createApplication().setTitle(NAME).setHeight(100);
app.add(app.createVerticalPanel().setHorizontalAlignment(UiApp.HorizontalAlignment.CENTER).add(
app.createLabel(repl_(T.relocateStatus, c.fs.getName()))).add(
app.createButton(T.ok, app.createServerHandler(‘closeApp_’))));
return c.ss.show(app);
} else
return UiApp.getActiveApplication().close();
}

function createLabel_(app,text,id,handler) {
var label = css_(app.createLabel(text,false), _right);
if( id != null )
label.setId(id == ” ? text : id);
if( handler !== undefined )
css_(label.addClickHandler(handler), _link);
return label;
}

function createTextBox_(app,id,style) {
return css_(app.createTextBox().setId(id).setName(id).setText(s[id]+”),
style || (typeof(s[id]) == ‘string’ ? textParam : numParam));
}

function getSheet_(name) {
var s = c.ss.getSheetByName(name);
if( s == null )
throw name == ‘cancel’ ? T.cancelMessage : repl_(T.missingSheet, name+”);
return s;
}

function getQuotaSecured_() {
try { //prevent the script from stop working if the quota server fails
return MailApp.getRemainingDailyQuota();
} catch(e) { return 500; }
}

function errorHandling_(f,p) {
var now = new Date().getTime();
sset_(‘lastRun’,now);
//the idea here is to suppress random errors that occurs when using time-driven triggers.
var errs = +sget_(‘filterRnd’, ‘0’);
if( errs < 4 ) { //number of consecutive errors that are filtered
try { f(p); }
catch(suppress) { return sset_(‘filterRnd’, errs+1); }
} else
f(p);
if( errs != 0 )
sset_(‘filterRnd’, 0);
var warn = s.quota < s.qWarn;
if( c.err.length > 0 ) {
MailApp.sendEmail(Session.getEffectiveUser().getEmail(), repl_(T.errorReportSubject, NAME),
repl_(T.errorReportBody, c.ss.getName(), c.err.join(‘\n’))+
(warn ? T.alsoQuota : ”) +’\n\n’+c.ss.getUrl());
if( warn )
uset_(‘quota.lastWarning’, now);
} else if( c.origQuota != s.quota && warn && now-(+uget_(‘quota.lastWarning’,”+now)) > 24*60*60*1000 ) { //1 day
MailApp.sendEmail(Session.getEffectiveUser().getEmail(), repl_(T.quotaMailSubject, NAME),
repl_(T.quotaMailBody, s.quota, s.qWarn)+ ‘\n\n’ + c.ss.getUrl());
uset_(‘quota.lastWarning’, now);
}
}

//apply css styles to the element
function css_(element, style) {
for( var key in style )
if( typeof style[key] === ‘object’ ) //cascade
css_(element, style[key]);
else
element.setStyleAttribute(key, style[key]);
return element; //useful for chaining
}

function showMsg_(msg, buttons) {
return Browser.msgBox(NAME, msg_(msg), Browser.Buttons[buttons || ‘OK’]);
}
function showInput_(msg, buttons) {
return Browser.inputBox(NAME, msg_(msg), Browser.Buttons[buttons || ‘OK_CANCEL’]);
}
function msg_(msg) {
return msg.toString ? msg.toString().replace(/ /g,nbs).replace(/\n/g,’\\n’) : msg;
}
//copied and adapted from http://stackoverflow.com/questions/610406/javascript-equivalent-to-printf-string-format
function repl_() {
var args = arguments;
return args[0].replace(/{(\d+)}/g, function(match, number) {
return typeof args[number] !== ‘undefined’ ? args[number] : match;
});
}
function startsWith_(string, start) {
return string.length >= start.length && string.substr(0,start.length) === start;
}

//shorter names for Script and UserProperties
//also ignore errors on these APIs, since they’re not used for core features of FormEmailer
function sget_(prop,def)   { return get_(ScriptProperties, prop ) || def; }
function sset_(prop,value) { set_(ScriptProperties, prop, value); }
function uget_(prop,def)   { return get_(UserProperties, prop) || def; }
function uset_(prop,value) { set_(UserProperties, prop, value); }
function set_(o,p,v) { try { o.setProperty(p,v); } catch(e) {} }
function get_(o,p) { try { return o.getProperty(p); } catch(e) { return ”; } }

Berikut ini isi dari script yang telah Henny buat pada  merupakan email yang mengarah ke ric (ric@raharja.info) dan saya sendiri Henny (henny.wahyuni@raharja.info). Data tersebut berisikan data pribadi raharja yang telah submit pada form service.

Berikut ini merupakan tampilan jika ada yang submitt pada Form Keluhan

Sekian cermi Henny kali ini, semoga cerminya bermanfaat ya. Terimakasih 🙂

Views All Time
Views All Time
189
Views Today
Views Today
2
200 Total Views 3 Views Today

One Response

Leave a Reply

You must be logged in to post a comment.