Mail Merge using Google Sheet and Google Docs

Google Sheets and Google Docs are powerful tools for organizing and presenting data.

However, when it comes to performing mass mailing, it can be quite challenging to personalize each email for every recipient.
This is where mail merge comes into play. 

With the help of Google Apps Script, you can easily automate the mail merge process and send personalized emails to all the recipients.

In this blog, we will discuss how to perform mail merge in Google Sheets and Google Docs using Google Apps Script.

Step 1: Create a Google Sheet with Data 

The first step in performing a mail merge is to create a Google Sheet with the data you want to merge. 

The sheet should contain columns with information such as recipient name, email address, and any other data you want to include in the email.

Step 2: Create a Google Docs Template

Next, create a Google Docs template with the content you want to include in your email. 

You can use placeholders such as <<Recipient Name>> and <<Email Address>> in the template to personalize the email for each recipient.

Step 3: Create a Google Apps Script 

Once you have created the Google Sheet and the Google Docs template, it's time to create a Google Apps Script. To create a new script, open your Google Sheet, click on Tools -> Script Editor.

In the script editor, create a new function and give it a name. This function will be used to perform the mail merge.

Step 4: Write the Google Apps Script Code

The following code can be used to perform mail merge in Google Sheets and Google Docs using Google Apps Script:

function sendEmails() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // First row of data to process var numRows = 10; // Number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()) var data = dataRange.getValues(); var templateFile = DriveApp.getFileById('TEMPLATE_DOC_ID'); // replace with your template file ID var templateDoc = DocumentApp.openById(templateFile.getId()); for (var i = 0; i < data.length; ++i) { var row = data[i]; var recipientName = row[0]; var recipientEmail = row[1]; var copyDoc = templateDoc.makeCopy(); var copyId = copyDoc.getId(); var copyDocFile = DriveApp.getFileById(copyId); var copyDocFolder = DriveApp.getFolderById('FOLDER_ID'); // replace with your folder ID copyDocFolder.addFile(copyDocFile); var copyDocUrl = copyDocFile.getUrl(); var subject = 'Your Subject Here'; var body = 'Your email body text here'; var emailOptions = { htmlBody: body, attachments: [copyDoc.getAs(MimeType.PDF)], name: 'Automatic Emailer Script' } MailApp.sendEmail(recipientEmail, subject, body, emailOptions); copyDocFile.setTrashed(true); } }

The above code uses the SpreadsheetApp and DocumentApp services to read data from the Google Sheet and create a copy of the Google Docs template for each recipient.

It then replaces the placeholders in the copied document with actual data from the sheet, converts the document to a PDF, and sends an email to the recipient with the PDF as an attachment.

Make sure to replace 'TEMPLATE_DOC_ID' and 'FOLDER_ID' with the actual ID of your Google Docs template and folder, respectively. Also, customize the subject and body of the email as per your requirements.

Step 5: Run the Script

Read More

Sub Query Vs Corelated Query in SQL

In SQL, both subqueries and correlated queries are used to retrieve data from the database. However, they have different ways of executing and returning data.

A subquery is a query that is embedded within another query. It is executed first, and its result is then used by the outer query to retrieve data. A subquery can return a single value or a set of values, and it can be used in various clauses, such as the WHERE clause, the HAVING clause, or the SELECT clause.

For example, consider the following subquery that returns the average salary of all employees:

SELECT AVG(salary) 

FROM employees;

A correlated query, on the other hand, is a query that refers to a column from the outer query. It is executed for each row in the outer query, and its result is used to filter or retrieve data from the inner query. A correlated query can also return a single value or a set of values, and it is commonly used in the WHERE clause.

For example, consider the following correlated query that returns the employees who have a salary greater than the average salary of their department:

SELECT * FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id ); 

Here, the inner query is correlated to the outer query by the department_id column, and it is executed for each row in the outer query.

The main difference between subqueries and correlated queries is that subqueries are executed first and their result is used by the outer query, while correlated queries are executed for each row in the outer query.

Subqueries are generally more efficient for retrieving a large set of data, while correlated queries are more efficient for filtering data based on a condition that cannot be expressed using a simple WHERE clause.

Here are some examples of subqueries and correlated queries in SQL:

Subquery example:

Suppose we have two tables, employees and departments, and we want to find the average salary of employees in the sales department. We can use a subquery in the following way:

SELECT AVG(salary) 

FROM employees 

WHERE department_id = ( SELECT department_id 

                                               FROM departments 

                                                WHERE department_name = 'Sales' );

In this example, the inner query retrieves the department_id of the sales department, which is then used by the outer query to retrieve the average salary of employees in that department.

Correlated query example:

Suppose we have the same two tables, employees and departments, and we want to find all employees who have a salary greater than the average salary of their department. We can use a correlated subquery in the following way:

SELECT *
FROM employees e 

    WHERE salary > ( SELECT AVG(salary) FROM employees 

                                           WHERE department_id = e.department_id );

In this example, the inner query is correlated to the outer query by the department_id column, and it is executed for each row in the outer query. The result is a list of employees who have a salary greater than the average salary of their department.

In conclusion, subqueries and correlated queries are both important tools for retrieving data in SQL, but they have different ways of executing and returning data. Subqueries are executed first and their result is used by the outer query, while correlated queries are executed for each row in the outer query.

Thank You,

Ashwani

Read More

vba vs apps script

VBA Vs Google App Script

When it comes to automating tasks in spreadsheets or creating custom functions, VBA (Visual Basic for Applications) and Google Apps Script are two popular choices.

Both languages have their strengths and weaknesses, and which one you choose depends on your specific needs.

VBA is a programming language developed by Microsoft and is primarily used to automate tasks in Microsoft Office applications such as Excel, Word, and Access.

VBA is powerful and flexible, and it can do a lot of things that Google Apps Script cannot do. VBA is also widely used in the corporate world, so if you work in a company that uses Microsoft Office extensively, VBA is a good choice.

Google Apps Script, on the other hand, is a scripting language developed by Google and is used to automate tasks in Google Sheets, Google Docs, and other Google Workspace apps.

Google Apps Script is simpler than VBA and is easier to learn, especially if you're already familiar with JavaScript.

Google Apps Script is also cloud-based, which means you can access it from anywhere with an internet connection and collaborate with others in real-time.

Here are some specific differences between VBA and Google Apps Script:

Syntax: VBA uses a syntax that is similar to Visual Basic, whereas Google Apps Script uses a syntax that is similar to JavaScript.

Accessibility: VBA is only accessible within Microsoft Office applications, whereas Google Apps Script is accessible from any web browser.

Libraries: VBA has a wide variety of third-party libraries available, whereas Google Apps Script has a limited number of libraries available.

Integration: VBA integrates well with other Microsoft Office applications, whereas Google Apps Script integrates well with other Google Workspace apps.

Cost: VBA is included with Microsoft Office, whereas Google Apps Script is free to use.

When it comes to choosing between VBA and Google Apps Script, consider what you need to accomplish and where you need to accomplish it.

If you primarily work with Microsoft Office applications and need to automate tasks within them, VBA is probably the better choice.

However, if you primarily work with Google Workspace apps and need to automate tasks within them, Google Apps Script is likely the better choice.

In summary, both VBA and Google Apps Script are powerful tools for automating tasks and creating custom functions in spreadsheets.

Choose the language that best suits your needs based on the applications you work with and the tasks you need to accomplish.

I am starting a weekend VBA training course from 06 Jan 2024. If you are interested, please register yourself and go through the course details. Here is the link below: 

VBA Training by Ashwani

Thank You

Ashwani

Read More

Dictionary in VBA

Dictionary in VBA

One of the most useful data structures in VBA is the dictionary object, which allows you to store and retrieve data using a key-value pair.

In this blog, we will explore some examples of how dictionaries can be used in VBA.

1. Storing and Retrieving Data

Dictionaries are useful for storing data that can be easily accessed using a key. For example, if you have a list of employees and their salaries, you can store this data in a dictionary with the employee name as the key and the salary as the value. You can then retrieve the salary for a specific employee by using the employee name as the key.

2. Creating Look-Up Tables

Dictionaries can be used to create look-up tables. For example, if you have a list of product codes and their descriptions, you can store this data in a dictionary with the product code as the key and the description as the value. You can then use this dictionary to quickly look up the description of a product by using its code.

3. Checking for Duplicates

Dictionaries can be used to check for duplicates in a list. For example, if you have a list of names, you can use a dictionary to check whether each name appears more than once. You can do this by using the name as the key and the value as a count of how many times the name appears in the list.

4. Storing Data in a Specific Order

Dictionaries can be used to store data in a specific order. For example, if you have a list of dates and events, you can store this data in a dictionary with the date as the key and the event as the value. You can then retrieve the events in chronological order by iterating through the keys in the dictionary.

5. Storing Complex Data Structures

Dictionaries can be used to store complex data structures. For example, if you have a list of employees and their information such as name, age, salary, and job title, you can store this data in a dictionary with the employee name as the key and a dictionary of their information as the value.

Examples of how to use Dictionary object in VBA:

Here are some real-world examples of how you can use the Dictionary object in VBA:

1. Creating a lookup table Suppose you have a table of data with two columns, and you want to create a lookup table to find the value in the second column based on a value in the first column. You can use a Dictionary object to create the lookup table with the keys being the values in the first column and the values being the values in the second column. Here's an example:

               Dim lookupTable As New Dictionary

    lookupTable.Add "A", 1
    lookupTable.Add "B", 2
    lookupTable.Add "C", 3
    Dim result As Integer
    result = lookupTable("B") Debug.Print result 'displays 2

2. Storing data in a cache If your VBA code is accessing data that is slow to retrieve, you can use a Dictionary object to cache the data in memory to speed up subsequent access. Here's an example:

Dim dataCache As New Dictionary

Function GetData(key As String) As String

If dataCache.Exists(key) Then 

    GetData = dataCache.Item(key) 

Else 

    'retrieve data from slow source 

    Dim result As String 

    result = RetrieveDataFromDatabase(key) 

    dataCache.Add key, result 

    GetData = result 

End If 

End Function

3. Counting occurrences of items Suppose you have a list of items, and you want to count how many times each item appears in the list. You can use a Dictionary object to keep track of the counts. Here's an example:

Dim itemCounts As New Dictionary

Sub CountItems()

Dim itemList As Variant

itemList = Array("Apple", "Banana", "Apple", "Orange", "Banana", "Apple") 

Dim item As Variant 

For Each item In itemList 

    If itemCounts.Exists(item) Then 

        itemCounts.Item(item) = itemCounts.Item(item) + 1 

    Else 

        itemCounts.Add item, 1 

    End If 

Next item 

Dim key As Variant 

For Each key In itemCounts.Keys 

    Debug.Print key & ": " & itemCounts.Item(key) 

Next key 

End Sub

Here is the syntax for some of the commonly used properties and methods of the Dictionary object in VBA:

Properties:

• Count Syntax: DictObject.Count Example: Debug.Print dict.Count

• 
  

• Keys Syntax: DictObject.Keys Example: Debug.Print Join(dict.Keys, ",")

Items Syntax: DictObject.Items Example: Debug.Print Join(dict.Items, ",")




CompareMode Syntax: DictObject.CompareMode Example: dict.CompareMode = TextCompare

Methods:

Add Syntax: DictObject.Add Key, Item Example: dict.Add "Apple", 1




Item Syntax: DictObject.Item(Key) Example: Debug.Print dict.Item("Apple")




Exists Syntax: DictObject.Exists(Key) Example: If dict.Exists("Apple") Then ...




Remove Syntax: DictObject.Remove(Key) Example: dict.Remove("Apple")




RemoveAll Syntax: DictObject.RemoveAll() Example: dict.RemoveAll()




Keys Syntax: DictObject.Keys() Example: For Each key In dict.Keys ...




Items Syntax: DictObject.Items() Example: For Each item In dict.Items ...




CompareMode Syntax: DictObject.CompareMode = CompareMethod Example: dict.CompareMode = vbTextCompare

In conclusion, dictionaries are a powerful tool in VBA that can be used for storing and retrieving data, creating look-up tables, checking for duplicates, storing data in a specific order, and storing complex data structures.

By leveraging the power of dictionaries, you can create efficient and effective VBA programs.

Thank You,

Ashwani

Read More