彰化一整天的論壇

 找回密碼
 立即註冊
查看: 944|回復: 0

Google試算表寄email外掛Mail Merge如何設定預設值

[複製鏈接]
發表於 2015-11-17 16:56:08 | 顯示全部樓層 |閱讀模式
同事一直用Mail Merge透過gmail寄電腦維修通知單,最近他說每次都要輸入寄件者名稱及選寄信範本,可不可以有預設值,有要變更時再手動修改,不用每次都要輸入,看了一下語法及上網找相關指令,最後總算試出來了。
google_doc_setmail_01.jpg
1.預設範本及寄信者名稱
google_doc_setmail_02.jpg
2.【工具】/【指令碼編輯器】
google_doc_setmail_03.jpg
3.黃色部分是我加上去的,改好記得按儲存
  1. /*
  2. * Mail Merge HD with GUI, Notifications and better Gmail integration
  3. * @labnol 03/06/2012
  4. */

  5. // Updated 18/04/2012 - Fixed the BCC issue
  6. // Updated 29/06/2013 - Fixed the Sent Status issue
  7. // Updated 01/07/2013 - Fixed the Inline Images Issue
  8. // Updated 15/08/2013 - Fixed the UI
  9. // Updated 06/02/2014 - Bug Fixes, Premium Upgrade Option

  10. function onOpen() {
  11.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  12.   var menu = [
  13.     {name: "Help and Support »",functionName: "help"},
  14.     null,
  15.     {name: "Step 1: Initialize", functionName: "Initialize"},
  16.     {name: "Step 2: Start Mail Merge", functionName: "fnMailMerge"},
  17.     {name: "Clear Sheet", functionName: "labnolReset"},
  18.     null,
  19.     {name: "Upgrade to Premium »",functionName: "premium"},
  20.     null
  21.   ];  
  22.   ss.addMenu("Mail Merge", menu);
  23.   ss.toast("Please click the Mail Merge menu above to continue..", "", 5);
  24. }


  25. function help() {
  26.   var html = HtmlService.createHtmlOutputFromFile('help')
  27.   .setTitle("Google Scripts Support")
  28.   .setWidth(400)
  29.   .setHeight(260);
  30.   var ss = SpreadsheetApp.getActive();
  31.   ss.show(html);
  32. }


  33. function premium() {
  34.   var html = HtmlService.createHtmlOutput('Upgrade to the <a href="http://www.labnol.org/internet/personalized-mail-merge-in-gmail/20981/#premium">premium edition of Mail Merge</a> and unlock new features. You can also opt for one-on-one support via email, Skype or Google Hangouts.')
  35.   .setTitle("Mail Merge Premium")
  36.   .setWidth(240)
  37.   .setHeight(100);
  38.   var ss = SpreadsheetApp.getActive();
  39.   ss.show(html);
  40. }


  41. function labnolUpgrade() {
  42.   Browser.msgBox("Mail Merge Premium", "Adds support for aliases allowing you to send mails from a different account. Visit http://labnol.org/?p=20981 to learn more.", Browser.Buttons.OK);
  43. }


  44. function Initialize() {
  45.   Browser.msgBox("Script initialized. You can send " + MailApp.getRemainingDailyQuota() + " mails today!");
  46. }

  47. function labnolReset() {  
  48.   var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();   
  49.   mySheet.getRange(2, 1, mySheet.getMaxRows() - 1, mySheet.getMaxColumns()).clearContent();
  50. }

  51. /*
  52. * Forked from gist: 1838132 by ligthyear
  53. * https://gist.github.com/1907310
  54. */

  55. function fnMailMerge() {
  56.   var threads = GmailApp.search('in:draft', 0, 10);
  57.   if (threads.length === 0) {
  58.     Browser.msgBox("We found no templates in Gmail. Please save a template as a draft message in your Gmail mailbox and re-run the Mail Merge program.");
  59.     return;
  60.   }
  61.   var myapp = UiApp.createApplication().setTitle('Mail Merge v3.0').setHeight(160).setWidth(300);
  62.   var top_panel = myapp.createFlowPanel();   
  63.   top_panel.add(myapp.createLabel("Please select your Mail Merge template"));
  64.   var lb = myapp.createListBox(false).setWidth(250).setName('templates').addItem("Select template...").setVisibleItemCount(1);
  65.   
  66.   for (var i = 0; i < threads.length; i++) {
  67.     lb.addItem((i+1)+'- '+threads[i].getFirstMessageSubject().substr(0, 40));
  68.   }
  69.   
  70.   if(threads.length>0)
  71.     lb.setSelectedIndex(1);
  72.   
  73.   
  74.   top_panel.add(lb);
  75.   top_panel.add(myapp.createLabel("").setHeight(10));
  76.   top_panel.add(myapp.createLabel("Please write the sender's full name"));
  77.   var name_box = myapp.createTextBox().setName("name").setText("彰化一整天").setWidth(250);
  78.   
  79.   top_panel.add(name_box);  
  80.   top_panel.add(myapp.createLabel("").setHeight(10));
  81.   var bcc_box = myapp.createCheckBox().setName("bcc").setText("BCC yourself?").setWidth(250);
  82.   top_panel.add(bcc_box);
  83.   top_panel.add(myapp.createLabel("").setHeight(5));
  84.   var ok_btn = myapp.createButton("Start Mail Merge");
  85.   top_panel.add(ok_btn);
  86.   myapp.add(top_panel);
  87.   
  88.   var handler = myapp.createServerClickHandler('startMailMerge').addCallbackElement(lb).addCallbackElement(name_box).addCallbackElement(bcc_box);
  89.   ok_btn.addClickHandler(handler);
  90.   
  91.   SpreadsheetApp.getActiveSpreadsheet().show(myapp);
  92. }

  93. /*
  94. * The code is written by Romain Vialard - Yet Another Mail Merge
  95. * https://docs.google.com/document/d/1fsjHYL8TeHS2eiG217hqTgtGWI1RhRXcIvpfZFmIa3A/edit
  96. */

  97. function startMailMerge(e) {
  98.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  99.   var dataSheet = ss.getSheets()[0];
  100.   if(dataSheet.getRange(1,dataSheet.getLastColumn()).getValue() != 'Mail Merge Status'){
  101.     dataSheet.getRange(1,dataSheet.getLastColumn()+1).setValue('Mail Merge Status');
  102.   }
  103.   var headers = dataSheet.getRange(1, 1, 1, dataSheet.getLastColumn()).getValues();
  104.   var emailColumnFound = false;
  105.   for(i in headers[0]){
  106.     if(headers[0][i] == "Email Address"){
  107.       emailColumnFound = true;
  108.     }
  109.   }
  110.   if(!emailColumnFound){
  111.     var emailColumn = Browser.inputBox("Which column contains the recipient's email address ? (A, B,...)");
  112.     dataSheet.getRange(emailColumn+''+1).setValue("Email Address");
  113.   }
  114.   var dataRange = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, dataSheet.getLastColumn());
  115.   
  116.   ss.toast('Starting mail merge, please wait...','Mail Merge',-1);
  117.   
  118.   var selectedTemplate = GmailApp.search("in:drafts")[(parseInt(e.parameter.templates.substr(0, 2))-1)].getMessages()[0];
  119.   var emailTemplate = selectedTemplate.getBody();
  120.   var attachments = selectedTemplate.getAttachments();
  121.   var cc = selectedTemplate.getCc();
  122.   var bcc = "";
  123.   if (e.parameter.bcc == "true") {
  124.     bcc = selectedTemplate.getFrom();
  125.   }
  126.   
  127.   var regMessageId = new RegExp(selectedTemplate.getId(), "g");
  128.   
  129.   if (emailTemplate.match(regMessageId) != null) {
  130.     var inlineImages = {};
  131.     var nbrOfImg = emailTemplate.match(regMessageId).length;
  132.     var imgVars = emailTemplate.match(/<img[^>]+>/g);
  133.     var imgToReplace = [];
  134.     for (var i = 0; i < imgVars.length; i++) {
  135.       if (imgVars[i].search(regMessageId) != -1) {
  136.         var id = imgVars[i].match(/Inline\simages?\s(\d)/);
  137.         imgToReplace.push([parseInt(id[1]), imgVars[i]]);
  138.       }
  139.     }
  140.     imgToReplace.sort(function (a, b) {
  141.       return a[0] - b[0];
  142.     });
  143.     for (var i = 0; i < imgToReplace.length; i++) {
  144.       var attId = (attachments.length - nbrOfImg) + i;
  145.       var title = 'inlineImages' + i;
  146.       inlineImages[title] = attachments[attId].copyBlob().setName(title);
  147.       attachments.splice(attId, 1);
  148.       var newImg = imgToReplace[i][1].replace(/src="[^\"]+\"/, "src=\"cid:" + title + "\"");
  149.       emailTemplate = emailTemplate.replace(imgToReplace[i][1], newImg);
  150.     }
  151.   }
  152.   
  153.   objects = getRowsData(dataSheet, dataRange);
  154.   for (var i = 0; i < objects.length; ++i) {   
  155.     var rowData = objects[i];
  156.     if(rowData.mailMergeStatus != "EMAIL_SENT"){
  157.       
  158.       // Replace markers (for instance ${"First Name"}) with the
  159.       // corresponding value in a row object (for instance rowData.firstName).
  160.       
  161.       var emailText = fillInTemplateFromObject(emailTemplate, rowData);     
  162.       var emailSubject = fillInTemplateFromObject(selectedTemplate.getSubject(), rowData);
  163.       
  164.       GmailApp.sendEmail(rowData.emailAddress, emailSubject, emailText,
  165.                          {name: e.parameter.name, attachments: attachments, htmlBody: emailText, cc: cc, bcc: bcc, inlineImages: inlineImages});      
  166.       
  167.       dataSheet.getRange(i+2,dataSheet.getLastColumn()).setValue("EMAIL_SENT");
  168.       
  169.       
  170.     }  
  171.   }
  172.   
  173.   ss.toast('You can reach the script developer at amit@labnol.org for support and customization.','Mail Merge Complete',-1);
  174.   
  175.   var app = UiApp.getActiveApplication();
  176.   app.close();
  177.   return app;
  178. }

  179. // Replaces markers in a template string with values define in a JavaScript data object.
  180. // Arguments:
  181. //   - template: string containing markers, for instance ${"Column name"}
  182. //   - data: JavaScript object with values to that will replace markers. For instance
  183. //           data.columnName will replace marker ${"Column name"}
  184. // Returns a string without markers. If no data is found to replace a marker, it is
  185. // simply removed.
  186. function fillInTemplateFromObject(template, data) {
  187.   var email = template;
  188.   // Search for all the variables to be replaced, for instance ${"Column name"}
  189.   var templateVars = template.match(/\$\%[^\%]+\%/g);
  190.   if(templateVars!= null){
  191.     // Replace variables from the template with the actual values from the data object.
  192.     // If no value is available, replace with the empty string.
  193.     for (var i = 0; i < templateVars.length; ++i) {
  194.       // normalizeHeader ignores ${"} so we can call it directly here.
  195.       var variableData = data[normalizeHeader(templateVars[i])];
  196.       email = email.replace(templateVars[i], variableData || "");
  197.     }
  198.   }
  199.   return email;
  200. }


  201. /* This code is reused from the 'Reading Spreadsheet data using JavaScript Objects' tutorial */

  202. function getRowsData(sheet, range, columnHeadersRowIndex) {
  203.   columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  204.   var numColumns = range.getEndColumn() - range.getColumn() + 1;
  205.   var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  206.   var headers = headersRange.getValues()[0];
  207.   return getObjects(range.getValues(), normalizeHeaders(headers));
  208. }

  209. function getObjects(data, keys) {
  210.   var objects = [];
  211.   for (var i = 0; i < data.length; ++i) {
  212.     var object = {};
  213.     var hasData = false;
  214.     for (var j = 0; j < data[i].length; ++j) {
  215.       var cellData = data[i][j];
  216.       if (isCellEmpty(cellData)) {
  217.         continue;
  218.       }
  219.       object[keys[j]] = cellData;
  220.       hasData = true;
  221.     }
  222.     if (hasData) {
  223.       objects.push(object);
  224.     }
  225.   }
  226.   return objects;
  227. }

  228. function normalizeHeaders(headers) {
  229.   var keys = [];
  230.   for (var i = 0; i < headers.length; ++i) {
  231.     var key = normalizeHeader(headers[i]);
  232.     if (key.length > 0) {
  233.       keys.push(key);
  234.     }
  235.   }
  236.   return keys;
  237. }

  238. function normalizeHeader(header) {
  239.   var key = "";
  240.   var upperCase = false;
  241.   for (var i = 0; i < header.length; ++i) {
  242.     var letter = header[i];
  243.     if ( ( (letter == " ") || (letter == "-") || (letter == "_") ) && key.length > 0) {
  244.       upperCase = true;
  245.       continue;
  246.     }
  247.     if (!isAlnum(letter)) {
  248.       continue;
  249.     }
  250.     if (key.length == 0 && isDigit(letter)) {
  251.       continue; // first character must be a letter
  252.     }
  253.     if (upperCase) {
  254.       upperCase = false;
  255.       key += letter.toUpperCase();
  256.     } else {
  257.       key += letter.toLowerCase();
  258.     }
  259.   }
  260.   return key;
  261. }

  262. function isCellEmpty(cellData) {
  263.   return typeof(cellData) == "string" && cellData == "";
  264. }

  265. function isAlnum(char) {
  266.   return char >= 'A' && char <= 'Z' ||
  267.     char >= 'a' && char <= 'z' ||
  268.       isDigit(char);
  269. }

  270. function isDigit(char) {
  271.   return char >= '0' && char <= '9';
  272. }
複製代碼

回復

使用道具 舉報

您需要登錄後才可以回帖 登錄 | 立即註冊

本版積分規則

 ㄚ母滴雞湯
 員林香純滴雞精

Archiver|手機版|小黑屋|彰化一整天的論壇(Excel,Office)  |网站地图

GMT+8, 2019-11-23 06:35 , Processed in 0.138411 second(s), 22 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表