Google Sheets Merge Data

 admin
  1. Merge Data In Google Sheets
  2. Merge Google Sheets Files
  3. Google Sheets Merge Data In Columns

You can create a mail merge data file with Google Sheets and a template file in Google Docs. However, the process can be quite a time consuming and can make you enter the details manually. This can also give rise to a few human errors and can make your mail merged emails, and communications look slightly awkward. In Google Sheets, you can consolidate data from multiple sheets using a formula based on the Query. It’s simple to learn! We can use the Query function in Google Sheets to combine multiple sheets and consolidate data. The data consolidation in this way is not limited to Sheets in a single file.

How to combine / merge multiple sheets into one sheet in Google sheet?

How to combine or merge multiple sheets into one single sheet in Google sheet? Here, I will talk about an easy formula for you to solve this task.

Combine / Merge multiple sheets into one sheet in Google sheet

To combine multiple sheets into one single sheet, the following useful formula may do you a favor. Please do as this:

1. Type this formula:

={filter('Qua1'!A2:C, len('Qua1'!A2:A)); filter('Qua2'!A2:C, len('Qua2'!A2:A)); filter('Qua3'!A2:C, len('Qua3'!A2:A));filter('Qua4'!A2:C, len('Qua4'!A2:A))} into a cell of the new sheet where you want to combine the data from other sheets. See screenshot:

Notes:

1. In above formula, Qua1, Qua2, Qua3, and Qua4 are the sheet names which you want to combine; A2:C is used to get all the cells of the sheets - Qua1, Qua2, Qua3, and Qua4 from the second row to the last row.

2. In the above formula, len('Qua1'!A2:A), len('Qua2'!A2:A), len('Qua3'!A2:A), len('Qua4'!A2:A) indicate that the entry in column A is nonempty, if there are blank cells, the row will be excluded in the merged sheet.

2. Then press Enter key, all rows from the specific sheets have been merged into one single sheet as following screenshot shown:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    HERBAL DR OKUOFOH WHO PREPARE HERBAL MEDICINE TO CURE ALL KINDS OF DISEASES INCLUDING HERPES DISEASE.
    I have been battling this Herpes disease for almost A year now....I tried all possible means to get cure from my Herpes Disease but all to be in vain until i saw a post in a health forum about a herbal Dr. Okuofoh who prepare herbal medicine to cure all kind of diseases including Herpes Disease, at first i doubted if it was real but decided to give it a try...when i contact Dr Okuofoh via his email ([email protected] ) write him and reply me explain how the process work so after ordering for the medicine I got it within 3/4 working days through DHL Delivery and I took it according to the way Dr Okuofoh instructed, I was so happy after 2 week I took the medicine there was very big change in my health when I was done with the process I go for test, I found out I am negative...Herpes patients should also get in touch with this herbalist Dr. Okuofoh to get rid of these Herpes Virus forever his whatsapp number +2349050141449.
  • To post as a guest, your comment is unpublished.
    Bei mir kommt 'Fehler beim parsen der Formel'. Die Formel erkennt jeweils den ersten Verweis auf die Tabelle nicht, also diesen Part ={filter(>>>'Qua1'!A2:C<<<<, len('Qua1'!A2:A)); filter('Qua2'!A2:C, len('Qua2'!A2:A)); filter('Qua3'!A2:C, len('Qua3'!A2:A));filter('Qua4'!A2:C, len('Qua4'!A2:A))}
    Das sehe ich daran, dass dieser nicht farbig markiert ist, der zweite Teil nach 'len' allerdings schon farbig markiert ist (selbstverständlich habe ich Qua1 und so weiter durch den Namen meines Tabellenblattes ausgetauscht).
    Habt ihr eine Ahnung, woran das liegen kann?
  • To post as a guest, your comment is unpublished.
    I am getting this
    'Error
    FILTER range must be a single row or a single column'

In this tutorial, I'll teach you how to send personalized emails from Google Sheets using Mail Merge.

Goal

You have a list of users and information about them in a Google Sheet. You want to send each of them a personalized email using the information you have about them.

For example, the sheet below tells us which newsletter users have signed up for. We want to send each user a confirmation email to let them know that their subscription was successful and that they'll begin receiving their newsletter shortly.

A generic email might look like this:

Hi there,

Thanks for subscribing! You'll begin receiving your newsletter shortly.

Cheers,

Jason

The above email lacks context (which newsletter etc.) and users might think it is spam. Wouldn't it be better if the email were personalized instead? The email below provides a lot more context and also addresses the recipient by name. It's so much better!

Hi Rob,

Thank you for subscribing to the Apps Script is awesome newsletter! We'll send you the latest news about Apps Script every week on Monday morning.

Cheers,

Jason

How does a Mail Merge work?

A Mail Merge involves using data from a spreadsheet or a database to create personalized emails or documents. To create a Mail Merge, you need a template that contains placeholders and a spreadsheet containing values to insert into the placeholders.

The graphic below shows you how a Mail Merge works. The values from the spreadsheet are 'merged with' (ie, inserted into the placeholders in) an email template to create a personalized email.

Now, let's create a Mail Merge using Google Sheets and Apps Script!

Prerequisites

This tutorial assumes that:

  • You're familiar with Google Sheets.
  • You know the basics of Apps Script and basic coding concepts. If you're new to coding or Apps Script, I've written a series of posts to teach you the basics of coding using Apps Script.
  • You've read the tutorial on sending an email for each row in a Google Sheet.

This tutorial is divided into four sections

  • Create a sheet called Data containing email addresses.
  • Create a sheet called Templates that contains the subject and body of the email message.
  • Create an Apps Script to read data from your sheet, perform a Mail Merge and send out emails.
  • Run your script to send emails.

Section 1: Create a sheet called Data containing information to personalize the email

  • Create a sheet called Data that contains the necessary information to populate the placeholders in the email template.
  • Ensure that you specify headers for each column since you'll be using these values as placeholders in the email template.

Section 2: Create a sheet called Templates containing the subject and body of the email message

  • Create a new sheet called Templates.
  • Enter the email subject and body in the sheet. Enter the subject in cell A2 and the body in cell A5.
  • Use placeholders to specify where the information from the Data sheet should be inserted. The format for a placeholder is ${<HEADER NAME>}} where <HEADER NAME> is the header of the column whose value should be inserted. For example, use {{First Name}} to insert the user's first name.
  • You can use placeholders within the subject and body of the email.

Section 3: Create an Apps Script to read data from your sheet, perform a Mail Merge and send out emails

The full code is below. It is a bit lengthy but don't worry, I'll explain how it works. The script consists of four functions:

  • getData(): Returns the contents of a sheet as a two-dimensional array.
  • renderTemplate(): Takes a template and an object as input and replaces the placeholders in the template with values from the object.
  • rowsToObjects(): Converts an array of rows into an array of objects. Each row becomes an object with the column headers as properties.
  • sendEmails(): This is the main function and it uses all of the other functions to create and send personalized emails.

Full code

Read the code below and try to understand how it works. Please also feel free to copy paste it into the Apps Script editor and play around with it. That's the best way to learn.

How does the code work? What does it do?

The best place to start understanding the code is the sendEmails() function since that is where the action begins.

The first thing the sendEmails() function does is it gets the templates from the Templates sheet.

Then it gets all of the rows in the Data sheet and converts these rows into an array of objects. Each row becomes an object.

The output of getData('Data') is the following two-dimensional array:

The function rowsToObjects(emailData) converts the above two-dimensional array into an array of objects.

Why are we converting rows into objects? It's because the renderTemplate() function uses values from these objects to populate the placeholders in the template.

The final step to loop through each object in emailData, populate the subject and body template with values from the object and send out emails.

Understanding how the rowsToObjects() function works

The rowsToObjects() function converts the rows in a sheet to an array of objects. Each row in the sheet, with the exception of the header row, gets converted to an object.

The column names in the header row are used as property names when creating the object.

Here is the code for the function rowsToObjects.

The first step is to remove the header row. These values will be used as property names. The shift() array function removes the first item in the array and returns the item.

MergeTwo

Then each row is converted into an object. The object is then added to an array called data.

Data

Finally the array data is returned.

Merge

Understanding how the renderTemplate() function works

The function takes two parameters as input: the template to render and an object containing values to populate the placeholders in the template. The function returns the rendered template.

What does 'rendering a template' mean?

Rendering is the process of converting a template into an object (such as an email or a document etc.) that can be used. In this tutorial, the renderTemplate() function replaces all the placeholders in the template with values.

The string /{{(.*?)}}/g is called a regular expression. A regular expression is like a pattern and it is used to find all the locations in a piece of text where the pattern occurs. Here, the regular expression is used to find all occurrences of the pattern {{<some string>}} in the template. The code template.match(/{{(.*?)}}/g) returns these occurrences as an array.

Google sheets merge data from two columns

Then, for each occurrence, we replace the placeholder with the corresponding value from the data object.

Here is an example:

Consider the email subject template below.

The match() method of the string object uses the regular expression to find all occurrences of the pattern {{<some string>}} in the template.

The params variable will contain an array of these placeholder values.

Then, each placeholder is replaced with the actual value to create the email subject.

Let's say the object data contains the following information about Rob.

The contents of data will be:

Merge Data In Google Sheets

The renderTemplate() function will then render the subject template.

The above template will get converted to the subject line below.

Notice that the placeholders {{First Name}} and {{Newsletter Name}} were replaced by the values 'Rob' and 'Apps Script is awesome' from the data object.

Merge Google Sheets Files

Section 4: Run your script to send emails

To run your script, choose the sendEmails function from the drop down menu and click the play icon.

You'll be asked to authorize the script. Once you authorize it, your script will run and emails will be sent. Here is a screenshot of an email that was sent when I ran the script.

Future work

A Mail Merge is a very powerful concept. There are so many ways for you to apply the underlying concept of merging the data in a sheet with a template in order to personalize content.

For example, you could:

  • Create invoices using a template in Google Docs and data in Google Sheets.
  • Create printable address labels to mail holiday cards to your friends and relatives.
  • Create personalized quizzes for your students using a template in Google Docs and a question library in Google Sheets.

Summary

In this tutorial you learned how to send personalized emails using Mail Merge and Google Sheets.

  • A Mail Merge involves using data from a spreadsheet or a database to create personalized emails or documents.
  • To set up a Mail Merge in Google Sheets, you need a spreadsheet containing information and templates that will be populated using this information.
  • You learned how to write an apps script to read information from the sheet, populate templates using this information and send out emails.

Thanks for reading.

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!

Google Sheets Merge Data In Columns