0

I have set up a Gsheet in which there are several Email for which drafts need to be created. I use the following code to achieve this:

const RECIPIENT_COL  = "Recipient";
const CC= "Sidekick email address";
const DRAFT_CREATED_COL = "Draft created";
const SUBJECT_COL="Subject";
const TEXT="Text";
 
/** 
 * Creates the menu item "Mail Merge" for user to run scripts on drop-down.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Create Drafts')
      .addItem('Create Drafts', 'createDraft')
      .addToUi();
}
 

function createDraft(sheet=SpreadsheetApp.getActiveSheet()) {
  const dataRange = sheet.getDataRange();
  // Fetch displayed values for each row in the Range HT Andrew Roberts 
  // https://mashe.hawksey.info/2020/04/a-bulk-email-mail-merge-with-gmail-and-google-sheets-solution-evolution-using-v8/#comment-187490
  // @see https://developers.google.com/apps-script/reference/spreadsheet/range#getdisplayvalues
  const data = dataRange.getDisplayValues();

  // assuming row 1 contains our column headings
  const heads = data.shift(); 
  
  // get the index of column named 'Email Status' (Assume header names are unique)
  // @see http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions
  const emailSentColIdx = heads.indexOf(DRAFT_CREATED_COL);
  
  // convert 2d array into object array
  // @see https://stackoverflow.com/a/22917499/1027723
  // for pretty version see https://mashe.hawksey.info/?p=17869/#comment-184945
  const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

  // used to record sent emails
  const out = [];

  // loop through all the rows of data
  obj.forEach(function(row, rowIdx){
    // only send emails is email_sent cell is blank and not hidden by filter
    if (row[DRAFT_CREATED_COL] == ''){
      try {
        // @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
        GmailApp.createDraft(row[RECIPIENT_COL], row[SUBJECT_COL], row[TEXT], {
          //htmlBody: emailHtml,
          cc: row[CC],
        });
        // modify cell to record email draft created date
        out.push([new Date()]);
      } catch(e) {
        // modify cell to record error
        out.push([e.message]);
      }
    } else {
      out.push([row[DRAFT_CREATED_COL]]);
    }
  });
  
  // updating the sheet with new data
  sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);
}

However, this does not include the links you can find in the GSheet I use, which you can find here: https://docs.google.com/spreadsheets/d/1hCcEr7L3ejGZJihJ5TPxwX8XMHd0jomHK22zhA5-0y8/edit?usp=sharing

I thought about changing the respective section to the following code section:

emailText=msg.getPlainBody();
emailHtml=msg.getBody();
GmailApp.createDraft(row[RECIPIENT_COL], row[SUBJECT_COL], emailText, {
htmlBody: emailHtml,

Note that this throws the error msg.getPlainBody is not a function. How could I fix this or how could I include the links (maybe with correct html formatting)?

Marios
  • 26,333
  • 8
  • 32
  • 52
datasciencelearner
  • 175
  • 2
  • 10
  • Related: https://stackoverflow.com/questions/61787891/how-to-extract-the-link-from-a-cell-now-that-links-are-not-reflected-as-hyperlin – TheMaster Sep 01 '20 at 16:58
  • Thank you, I added the following to the code for testing: `const value = spreadsheetApp.newRichTextValue().setText("Foo").setLinkUrl("https://bar.foo");` and removed the comment for htmlBody: `htmlBody: value` However, this returned following message in out: `com.google.apps.maestro.server.beans.trix.impl.RichTextValueBuilderImpl@ca57266` – datasciencelearner Sep 01 '20 at 18:28
  • The builder is different from the finished building. You need to `build()` the builder – TheMaster Sep 01 '20 at 19:51
  • Do you mean `const value = SpreadsheetApp.newRichTextValue().setText("Foo").setLinkUrl("https://bar.foo").build();`? This also returns `com.google.apps.maestro.server.beans.trix.impl.RichTextValueApiAdapter@4d5650d8` – datasciencelearner Sep 01 '20 at 19:56
  • Yeah sorry about that. It happens because that is also just a richtext and not valid html. You need to get the url and append actual html anchor: ````. How exactly are you creating the html here? – TheMaster Sep 01 '20 at 20:01
  • I am not actively creating the HTML yet, how could I do that? I am just assigning `htmlBody` to the text from the Google Sheet. How would I need to enter the link with your example? I assume that the built richtext needs to be passed in `link`? If so, this yields a syntax error: `const link = SpreadsheetApp.newRichTextValue().setText("Foo").setLinkUrl("https://bar.foo").build(); const linkHtml = '';` – datasciencelearner Sep 01 '20 at 20:25
  • Just `get` the link(no need to build anything). Something like `var link = range.getRichTextValue().getLinkUrl();const linkHtml = 'link';` – TheMaster Sep 01 '20 at 20:37
  • Thank you, I got this part working. 1) However, there is no "link formatting" shown in GMail, i.e. blue and underlined. Can I change this? 2) How could I append this newly created linkHtml with the remaining "blank" text to have one variable storing the combined HTML? – datasciencelearner Sep 01 '20 at 20:50
  • 1
    Kindly ask a new question with research as we are way off from the scope of the original question. – TheMaster Sep 02 '20 at 03:40

0 Answers0