{"id":221,"date":"2019-02-22T14:20:28","date_gmt":"2019-02-22T08:50:28","guid":{"rendered":"http:\/\/www.jawed.in\/blog\/?p=221"},"modified":"2021-08-20T19:09:24","modified_gmt":"2021-08-20T13:39:24","slug":"google-sheets-mail-merge-advanced","status":"publish","type":"post","link":"https:\/\/www.jawed.in\/blog\/google-sheets-mail-merge-advanced\/","title":{"rendered":"Google Sheets Mail Merge : Advanced"},"content":{"rendered":"\n
\n

Google Script for Mail Merge<\/p>\n<\/div><\/div>\n\n\n\n

Introduction<\/h4>\n\n\n\n

Last year, during\nChristmas, I was looking out for an easy to use script to send out\nPersonalised e-Cards using the power of Google. But unfortunately I\ncould not find a readily available solution. A few solutions were\navailable but they were not free or not ready-to-use.<\/p>\n\n\n\n

So I just looked at\nthe Google Help section where I found an example which was meeting\naround 90% of the requirement and then a little bit of browsing\nStackoverflow and I was good to go.<\/p>\n\n\n\n

I am sharing this Ready to Use Google Sheet with the script on web with the hope that it helps someone save time while doing Mail Merge.<\/p>\n\n\n\n

What this script does?<\/h4>\n\n\n\n

This script will\nread the email ids and the form of Address (Mr.\/Ms Firstname etc) and\nthen create a personalised email with the predefined subject and body\nand then insert an image(a Christmas card in my case) and then send\nout the email. The script picks the image to be embedded from the\nuser\u2019s Google Drive. After the mail is sent successfully, it will\nupdate the status column in the sheet with the words \u2018EMAIL_SENT\u2019.\nThis is important as Google limits the number of email which free\naccounts can send(using scripts) to 100 per day. \n<\/p>\n\n\n\n

So if you have 300 email ids in your list, then you have to break it into 3 parts. The code is written in such a way that based on the Status it will skip all those email ids which have already been sent the Greeting Email. So effectively we can schedule this script to run at a fixed time everyday from 22nd<\/sup> Dec to 24th<\/sup> Dec. Everyday it will fire 100 emails and then stop. It will continue this way till the 3rd<\/sup> day(i.e., 24th<\/sup> Dec) by when it would have sent out emails to your complete list.<\/p>\n\n\n\n

Requirements for this mail merge to function<\/h4>\n\n\n\n
  1. An existing Gmail id<\/li>
  2. Google sheet with your contact\u2019s email id and Form of address(Mr. Kent or Mr. Clarke Kent)<\/li>
  3. Greeting Image(upload it to your Google Drive)<\/li>
  4. Content of the email(subject and body)<\/li>
  5. <\/li><\/ol>\n\n\n\n

    You can download the ready to use Google Sheet with the code in it by clicking on the link below. Please login to your personal Gmail ID before clicking on the link below.<\/p>\n\n\n\n

    https:\/\/docs.google.com\/spreadsheets\/d\/14zdcYdM7iBF_YTV7n6NGj7CTYuCMrcTBsa1yqYL3amU\/edit?usp=sharing<\/a><\/p>\n\n\n\n

    Steps to copy the template sheet (along with code ) to your Google Drive<\/h4>\n\n\n\n

    First you have to Login to your Gmail ID and then click on the link given above.<\/p>\n\n\n\n

    Once the sheet has opened, please click on File–> Make a copy.. <\/strong>You won’t be able to make a copy if you are not logged in to your personal Gmail ID.<\/p>\n\n\n\n

    \"Copy
    Copy the Google Sheet to your own Google Drive<\/figcaption><\/figure><\/div>\n\n\n\n

    Then you need to open the Script Editor by clicking on Tools –> Script Editor<\/strong><\/p>\n\n\n\n

    \"How
    Opening the Script Editor in Google Sheets<\/figcaption><\/figure><\/div>\n\n\n\n

    Here you need to find the ID of this particular Google Sheet. For this we have to give a few authorizations first. <\/p>\n\n\n\n

    Run the function called ‘GetID’.<\/p>\n\n\n\n

    \"\"
    Click on the Function getId<\/figcaption><\/figure><\/div>\n\n\n\n
    \"\"
    Now click on Run to run the function getId<\/figcaption><\/figure><\/div>\n\n\n\n

    At this stage you will be prompted to Authorize the script.<\/p>\n\n\n\n

    \"\"
    Click on the button named Review Permissions<\/figcaption><\/figure><\/div>\n\n\n\n
    \"\"
    Your own email id will appear here. Please click on the email id.<\/figcaption><\/figure><\/div>\n\n\n\n
    \"\"
    You might also get a browser prompt informing you that the script has not been verified by Google. Which is true, but since you have created this code you can safely go ahead and Click on Go to <scriptname>

    <\/figcaption><\/figure><\/div>\n\n\n\n
    \"\"
    Click on Allow<\/figcaption><\/figure><\/div>\n\n\n\n

    After you have given the authorisation, please run the GetID script again and then <IMPORTANT>go to the Browser TAB where you have the Google Sheet Open. <\/IMPORTANT>In this sheet, a popup will be generated with the Sheet ID.
    <\/p>\n\n\n\n

    \"\"
    This long string is the Unique ID for this Google Sheet. <\/figcaption><\/figure><\/div>\n\n\n\n

    Please copy this string and paste it into the Script at the top where its mentioned.<\/p>\n\n\n\n

    To insert the image into the email using the script, you will also need to find the Image ID of that image. It can be done by opening the image in Google Drive and clicking on the Share link. In the next screen which opens, please copy the string between the slashes after \u2018d\u2019 and before \u2018view\u2019.<\/p>\n\n\n\n

    \"\"<\/figure><\/div>\n\n\n\n

    Pre-flight Checklist<\/h4>\n\n\n\n

    Check that you have changed the following things in the script.<\/p>\n\n\n\n