Aanpassen google docs script

[Danielpf1]

Ik ben bezig met het opzetten van een mailmerger voor mijn sportvereniging. We hebben een template gevonden die bijna geheel voldoet aan onze eisen, er is echter een klein probleempje, we willen de mails kunnen sturen vanaf een alias ipv mijn eigen email.

Het is alleen een kwestie van het vervangen van een paar regels in het script, echter geen van ons is goed/heeft programmeer ervaring. Na een halve dag google geraadpleegd te hebben zijn we geen steek verder gekomen. Op andere fora hebben we al een oplossing gevonden:

If you instead use GmailApp.sendEmail() you can get that functionality. See the documentation.

You just add a from:"[e-mail]" to the advancedArgs.

You can query for what aliases are available to use by using GmailApp.getAliases().

Echter het implementeren hiervan was niet succesvol. Zou iemand ons kunnen helpen?

Hier volgt de originele code:

function onOpen() {
var mySheet = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Test Mail Merge", functionName: "testMailMerge"},
{name: "Run Mail Merge", functionName: "runMailMerge"},
{name: "Help / About", functionName: "showHelp"}];
mySheet.addMenu("Mail Merge", menuEntries);
}

function testMailMerge() {

//set up the UI
var mydoc = SpreadsheetApp.getActiveSpreadsheet();
var myapp = UiApp.createApplication().setTitle('Preview: click in the box and use the arrow keys to scroll if needed');
myapp.setWidth(700);
myapp.setHeight(700);

ScriptProperties.setProperty('newLine',"\n\n"); //sets the new line appropriately for the test

//set the properties, and get the data range
settingsDataRange=setProperties();

var mergeDataValues=getMergeDataValues();
var arrayOfTags=mergeDataValues[0]; //gets the first row

//get the attachment list. It's not used in this test, but if there's an error, it will show.
var attachmentArray=getAttachmentArray(settingsDataRange);
var attachmentFiles = Array();
for (var i=0; i<attachmentArray.length; i++) {
var filename=DocsList.getFileById(attachmentArray[i]);
attachmentFiles.push(filename);
}


//run through the remaining rows of the data, which are the values to be merged in
var numRows=mergeDataValues.length;
if (numRows>3) numRows=3; //we only use the first three in the test phase

//add explanatory note to the start of the pop up panel
var info = formatTextPanel(myapp,"\nThe first " + numRows + " emails are shown below, so you can check the merger is working properly.\n\n");
myapp.add(info);

for (i=1; i<=numRows; i++) { //loops through the rows in the merge sheet. Misses 0, which is column headings
var thisEmailAddress=mergeDataValues[i][1];
if (thisEmailAddress=="") Browser.msgBox("Oops","We noticed a problem - an email address is blank. Check email address number "+i,Browser.Buttons.OK);
var thisEmailText=ScriptProperties.getProperty('genericEmailText'); //set the base text, before making changes
var thisEmailSubject=ScriptProperties.getProperty('subject');
var arrayOfValues=mergeDataValues[i];
for (j=1; j<arrayOfTags.length; j++) { //loops through the columns
var replaceData=arrayOfValues[j];
var tag=ScriptProperties.getProperty('before')+arrayOfTags[j]+ScriptProperties.getProperty('after');
while (thisEmailText.search(tag)>=0) { //loop through, to ensure we replace every occurence of "tag"
thisEmailText=thisEmailText.replace(tag,arrayOfValues[j]);
thisEmailSubject=thisEmailSubject.replace(tag,arrayOfValues[j]);
}
}

var emailAddressPanel = formatTextPanel(myapp,"Email to " + thisEmailAddress + "\nSubject " + thisEmailSubject + "\n");
var line = formatTextPanel(myapp,"------\n");
thisEmailText=stripHTML(thisEmailText);
var emailTextPanel = formatTextPanel(myapp,thisEmailText)
myapp.add(emailAddressPanel);
myapp.add(emailTextPanel);
if (i<numRows) myapp.add(line); //add a "---" separator, if this isn't the last one
}

mydoc.show(myapp); //we're all done - show the UI element

}

function runMailMerge() {

//set up the status column, so it's blank, and pink
var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data to be merged"); //used later in updating the status box
var totalRows=sheet.getMaxRows();
var range=sheet.getRange(2,1,totalRows,1);
var failed=false;
range.clearContent();
range.setBackgroundRGB(230,153,153);
if (totalRows>25) Browser.msgBox("It will take some time to send all these emails. There are more than 25 rows, so we're using the slower mode, to send as many as possible. See the Instructions and Settings sheet for details.");

ScriptProperties.setProperty('newLine',"<br><br>");
settingsDataRange=setProperties();
var mergeDataValues=getMergeDataValues();
var arrayOfTags=mergeDataValues[0]; //gets the first row

var attachmentArray=getAttachmentArray(settingsDataRange);
var attachmentFiles = Array();
for (var i=0; i<attachmentArray.length; i++) {
var filename=DocsList.getFileById(attachmentArray[i]);
attachmentFiles.push(filename);
}


//run through the remaining rows
for (i=1; i<mergeDataValues.length; i++) { //loops through the rows in the merge sheet. Misses 0, which is column headings
try {
var thisEmailAddress=mergeDataValues[i][1];
var thisEmailText=ScriptProperties.getProperty('genericEmailText');
var thisEmailSubject=ScriptProperties.getProperty('subject');
var arrayOfValues=mergeDataValues[i];
for (j=1; j<arrayOfTags.length; j++) { //loops through the columns
var replaceData=arrayOfValues[j];
var tag=ScriptProperties.getProperty('before')+arrayOfTags[j]+ScriptProperties.getProperty('after');
while (thisEmailText.search(tag)>=0) { //loop through, to ensure we replace every occurence of "tag"
thisEmailText=thisEmailText.replace(tag,arrayOfValues[j]);
thisEmailSubject=thisEmailSubject.replace(tag,arrayOfValues[j]);
}
}
thisEmailText=thisEmailText.replace(/\n/g,"<br>"); //ensure any linebreaks from Google doc are carried to the HTML version. The /g means all occurences.

//create a plain text version, by swapping <br> for \n, and stripping any other HTML tags
var plainEmailText=thisEmailText.replace(/<br>/g,"\n");
plainEmailText=stripHTML(plainEmailText);

var advancedArgs = {htmlBody:thisEmailText, name:ScriptProperties.getProperty('senderName'), replyTo:ScriptProperties.getProperty('replyTo'), attachments:attachmentFiles };


MailApp.sendEmail(thisEmailAddress,thisEmailSubject, plainEmailText, advancedArgs);
var range=sheet.getRange(i+1,1);
//Browser.msgBox(thisEmailAddress);
if (totalRows>25) Utilities.sleep(500);
range.setValue("OK");
range.setBackground("LightGreen");
} //end of the try section
catch(e){
var range=sheet.getRange(i+1,1);
range.setValue("Failed");
range.setBackground("Red");
failed=true;
} //end of the catch section
} //end of the loop, looping through all the rows
if (failed) {
Browser.msgBox("Oops","Not all your emails were sent ok. Please check the Data to be Merged sheet to see which were sent OK and which failed.",Browser.Buttons.OK);
} else {
Browser.msgBox("All done!","All your emails have been sent",Browser.Buttons.OK);
}
}


function setProperties() {
var mySpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var settingsDataRange = mySpreadSheet.getSheetByName("Instructions and settings").getDataRange();
var emailDataRange = mySpreadSheet.getSheetByName("Email text").getDataRange();

//get the generic email text, and set so it can be read by other functions
ScriptProperties.setProperty('genericEmailText',getGenericEmailText(emailDataRange));

//get the settings from the settings sheet, and set so they can be read by other functions
ScriptProperties.setProperty('before',findKeyValuePair(settingsDataRange,"before","across"));
ScriptProperties.setProperty('after',findKeyValuePair(settingsDataRange,"after","across"));
ScriptProperties.setProperty('replyTo',findKeyValuePair(settingsDataRange,"Reply-to address","across"));
ScriptProperties.setProperty('subject',findKeyValuePair(settingsDataRange,"Email subject","across"));
ScriptProperties.setProperty('senderName',findKeyValuePair(settingsDataRange,"Your name","across"));
return settingsDataRange;
}

function getMergeDataValues() {
var mySpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var mergeDataRange = mySpreadSheet.getSheetByName("Data to be merged").getDataRange();
return mergeDataRange.getValues();
}

function getAttachmentArray(settingsDataRange) {
settingsRangeValues=settingsDataRange.getValues();
var attachmentColumn=findValue(settingsRangeValues,"Start of attachment list","column");
var attachmentStartRow=findValue(settingsRangeValues,"Start of attachment list","row");
var attachmentEndRow=findValue(settingsRangeValues,"End of attachment list","row");
var attachmentArray=new Array();
for (var i=attachmentStartRow+1; i < attachmentEndRow; i++) {
if (settingsRangeValues[i][attachmentColumn]!="") attachmentArray.push(settingsRangeValues[i][attachmentColumn]);
}
return attachmentArray;
}

function getGenericEmailText(emailRange){
var emailRangeValues=emailRange.getValues();
var newLine = ScriptProperties.getProperty('newLine');
var emailTextColumn=findValue(emailRangeValues,"Start of email text","column");
var emailStartRow=findValue(emailRangeValues,"Start of email text","row");
var emailEndRow=findValue(emailRangeValues,"End of email text","row");
var emailText="";
for (i=emailStartRow+1; i < emailEndRow; i++) {
if (emailRangeValues[i][emailTextColumn]!="") emailText=emailText + emailRangeValues[i][emailTextColumn] + newLine;
}
return emailText;
}

function findValue(rangeValues,value,type) {
var reference=null;
for(var r in rangeValues) {
var row=(rangeValues[r]);
for (var c in row) {
if (row[c]==value) {
if (type=="row") reference=r;
if (type=="column") reference=c;
}
}
}
return parseInt(reference);
}

function findKeyValuePair(range,key,direction) {
var rangeValues=range.getValues();
var keyReferenceRow=findValue(rangeValues,key,"row");
var keyReferenceColumn=findValue(rangeValues,key,"column");
if (direction=="across") {
keyReferenceColumn++;
} else {
keyReferenceRow++;
}
value=rangeValues[keyReferenceRow][keyReferenceColumn];
return value;
}

function showHelp() {

var helpText="With Mail Merge, you can send personalized email messages from your Gmail or Google Apps email account using a similar method to a conventional mail merge in Microsoft Word.";
helpText = helpText + "\n\n -- Help is contained in the blue boxes within each sheet.";
helpText = helpText + "\n -- Yellow boxes are where you enter your information.";
helpText = helpText + "\n -- Don't change the content of red boxes, which are needed for the script to work.";
helpText = helpText + "\n\nor visit http://www.it4smallbusine...cations/google-mailmerge";
helpText = helpText + "\n\nWith thanks to";
helpText = helpText + "\n\n - labnol for the inspiration\nhttp://www.labnol.org/software/mail-merge-with-gmail/13289/";
helpText = helpText + "\n- Good Old Hacking for a function to help display\nhttp://goodoldhacking.blogspot.com/2011/01/workaround-for-html-in-apps-script.html";
helpText = helpText + "\n\nYou are free to use and modify this script in any way you wish. It comes with no warranty for any purpose.";

var mydoc = SpreadsheetApp.getActiveSpreadsheet();
var myapp = UiApp.createApplication().setTitle('About Mail Merge');
var helpPanel = formatTextPanel(myapp,helpText)
myapp.add(helpPanel);
mydoc.show(myapp);
}

/** * Returns a widget formatted with the text. */
function formatTextPanel(a, text) {
var app = a ;
var panel = app.createVerticalPanel();
var lines = text.split("\n");
for (var i=0; i<lines.length; i++) {
var cleaned = removeLeadingWhiteSpace(lines[i]);
var label = app.createLabel(cleaned[1]);
if (cleaned[1].length == 0) {
label.setText("-");
label.setStyleAttribute("visibility", "hidden");
}
if (cleaned[0] > 0) {
var margin = cleaned[0] * 6; // 6 px per char
label.setStyleAttribute("margin", "0px 0px 0px "+margin+"px");
}
panel.add(label);
}
return panel;
}

/** * Remove whitespaces from start and report how many. */
function removeLeadingWhiteSpace(text) {
var i = 0;
var res = [];
while (i < text.length && text[i] == ' ') {
i = i+1;
}
res[0] = i;
res[1] = text.substr(i);
return res;
}

function stripHTML(oldString) {
return oldString.replace(/<\/?[^>]+(>|$)/g, "");
}

[.oisyn]

Danielpf1 schreef op woensdag 25 februari 2015 @ 15:15:
Het is alleen een kwestie van het vervangen van een paar regels in het script, echter geen van ons is goed/heeft programmeer ervaring.

Dat is jammer, maar dat maakt je topic niet geschikt voor Programming, waar bedoeld is voor mensen die zelf willen programmeren.

Zie ook het beleid dat hier geldt: Devschuur® Beleid / #scriptrequests. Mocht je iemand willen inhuren, dan wil ik je graag verwijzen naar Vraag & Aanbod: Devschuurder werven? Gebruik Vraag & Aanbod!

[ Voor 14% gewijzigd door .oisyn op 25-02-2015 15:23 ]