Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)

Google Apps Script: SpreasheetApp, DocumentApp, DriveApp; Google Sheets, Google Docs

If you have ever worked in LibreOffice or Microsoft Excel you will probably be familiar with the mail merge. Traditionally, mail merge is used to create multiple versions of a document and snail-mail them to someone.

These days, we don’t often use the snail mail approach, but it is a regular occurrence for us to need to produce multiple versions of reports based on a data set usually from a spreadsheet.

In this tutorial, we will create a document merger that will create new Google Documents based on a dataset from a Google Sheet using Google Apps Script.

If you want to quickly jump into your own project with our script, I’ll provide you with a quick-use guide.

Then, we will set up a template for our Google Doc and generate our Google Sheet data (don’t worry, I’ll share the document so you can follow along).

Finally, we will jump into the breakdown of the code for those legends who are learning how to create their own Google Apps Script.

Let’s get started:

Note: As always, take what you need and don’t worry about the rest. 

Continue reading “Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)”

Google Apps Script: Add and removed Google Sheets columns based on a search array

Google Apps Script: V8 engine, map, filter, reduce, includes, 2d arrays, matrix

Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?

I know I have.

There have been a number of instances where I wanted to insert new columns or removed unused columns in large Google Sheets projects with Google Apps Script.

In the past, I have dynamically set headers based on something like an IMPORTRANGE or FILTER, or simply from an array ({}) from another sheet tab or Google Sheet. However, when I update the original data the headers change but all the data underneath them does not move along to the updated column with it.

Now all my data is not lined up with the header! As you can imagine, this creates some serious problems.

In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.

Take a look at the visual example below:

Add removes columns based on a search array visual example

As always, read as much or as little as you need to get the job done or learn the skill. 

Continue reading “Google Apps Script: Add and removed Google Sheets columns based on a search array”

Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 3                                         <<Intro>>

In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice of each question item from the survey results in a Google Sheet.

This time we are going to add a final element to our mix. Let’s say we have multiple questions and multiple groups. We want to find out the count for each choice for each question for each group.

Continue reading “Google Apps Script Course – Part 4: 2D Array Data Transformation of Multiple Question Multiple Group Items Data to Total Count of Rating Choices in Google Sheets”