A Rough & Tumble Data Merge Tool
Create a Menu & Send Sheet Rows to a Merged Document in Google Drive
We have all been there, needing an old school document merge, but Microsoft Tools from 1997 are no where to be found.
Here is a quick merge template for Google Apps Script.
You need to make a folder for the documents to be saved to.
The script will actually make individual documents, and then, in the end you run a final process to create one document.
Check it out:
Apps Script Setup
You need to have a sheet, and open the Apps Script Editor on the Sheet
Services needed: Drive, Sheets, and Docs
Make sure your spreadsheet has a target tab
Create a folder and copy the ID
When you make a change, you have to refresh the sheet, the script editor will close, this is normal (and annoying)
Step 1: Make a Menu
// This function runs automatically when the Google Spreadsheet is opened.
// It creates a custom menu in the spreadsheet's UI for easy access to mail merge features.
function onOpen() {
// Get the user interface (UI) of the active spreadsheet.
SpreadsheetApp.getUi()
// Create a new custom menu named "📄 Mail Merge" (with a document emoji for visual appeal).
.createMenu("📄 Mail Merge")
// Add a menu item labeled "Start Multi-Column Mail Merge" that triggers the function startMultiColumnMerge when clicked.
.addItem("Start Multi-Column Mail Merge", "startMultiColumnMerge")
// Add another menu item labeled "📑 Create FINAL MERGE Document" (with a clipboard emoji) that triggers the function createFinalMergedDocument when clicked.
.addItem("📑 Create FINAL MERGE Document", "createFinalMergedDocument")
// Add the custom menu to the spreadsheet's UI, making it visible to the user.
.addToUi();
}Step 2: Make the Documents
TAB_NAME: Replace 'TAB_NAME' with the actual name of the sheet (e.g., 'Sheet1', 'Data', or 'MailMergeData'). This ensures the script targets the correct sheet in the spreadsheet.
FOLDER_ID: Replace 'FOLDER_ID' with the actual Google Drive folder ID (e.g., '1b02fm9uRILu3IQcJ1LI2aJ0AzrKnKz-g'). You can find the folder ID in the URL of the folder in Google Drive (e.g., https://drive.google.com/drive/folders/1b02fm9uRIJ1LI2aJ0AzrKnKz-g).
// This function initiates a multi-column mail merge, creating Google Docs for each row of data.
// Users select column headers, and the script generates documents with labeled data from those columns.
function startMultiColumnMerge() {
// Get the user interface (UI) for displaying prompts and alerts.
const ui = SpreadsheetApp.getUi();
// Get the active spreadsheet and its active sheet (replace 'TAB_NAME' with the actual sheet name, e.g., 'Sheet1').
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TAB_NAME');
// Get the header row (row 1, from column 1 to the last column with data) and extract the values as an array.
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// Prompt the user to enter column headers they want to use in the mail merge, separated by commas.
const headerPrompt = ui.prompt(
'Mail Merge',
'Enter the column headers you want to use, separated by commas (e.g., Name,Email,Role):',
ui.ButtonSet.OK_CANCEL
);
// Check if the user clicked 'OK'. If they clicked 'Cancel' or closed the prompt, exit the function.
if (headerPrompt.getSelectedButton() !== ui.Button.OK) return;
// Split the user's input into an array of headers, trimming whitespace from each entry.
const selectedHeaders = headerPrompt.getResponseText()
.split(',')
.map(h => h.trim());
// Map selected headers to their column indices in the header row (0-based index).
const columnIndices = selectedHeaders.map(header => headers.indexOf(header));
// Check if any header was not found (indexOf returns -1 for missing headers).
if (columnIndices.includes(-1)) {
ui.alert('One or more headers not found. Please check your input.');
return;
}
// Calculate the number of data rows (total rows minus the header row).
const numRows = sheet.getLastRow() - 1;
// Get all data rows (starting from row 2, column 1, to the last row and last column).
const data = sheet.getRange(2, 1, numRows, sheet.getLastColumn()).getValues();
// Initialize an array to store the URLs of the created Google Docs.
const docUrls = [];
// Specify the Google Drive folder ID where the documents will be stored (replace 'FOLDER_ID' with the actual folder ID).
const folderId = 'FOLDER_ID'; // e.g., '1b02fm9uRILu3LI2aJ0AzrKnKz-g'
const folder = DriveApp.getFolderById(folderId);
// Loop through each row of data to create a Google Doc for each row.
for (let row = 0; row < data.length; row++) {
// Create a new Google Doc named 'MailMerge - Row X' (where X is the row number, starting from 2).
const doc = DocumentApp.create(`MailMerge - Row ${row + 2}`);
const body = doc.getBody();
// For each selected header, add a paragraph to the document with the header name and corresponding data value.
selectedHeaders.forEach((header, i) => {
const colIndex = columnIndices[i]; // Get the column index for this header.
const label = header; // Use the header as the label.
const value = data[row][colIndex]; // Get the data value for this row and column.
body.appendParagraph(`${label}: ${value}`); // Append a paragraph like "Name: John Doe".
});
// Save and close the document to ensure all changes are applied.
doc.saveAndClose();
// Move the document to the specified folder in Google Drive.
const file = DriveApp.getFileById(doc.getId());
folder.addFile(file); // Add the file to the specified folder.
DriveApp.getRootFolder().removeFile(file); // Remove the file from the root 'My Drive' folder.
// Store the document's URL in the docUrls array.
docUrls.push([doc.getUrl()]);
}
// Add a new column to the sheet to store the document URLs.
const docUrlCol = sheet.getLastColumn() + 1; // Get the next available column.
sheet.getRange(1, docUrlCol).setValue('Doc URL'); // Set the header for the new column.
sheet.getRange(2, docUrlCol, docUrls.length, 1).setValues(docUrls); // Write the URLs to the column.
}Step 3: Build the Final Document
FOLDER_ID: Replace 'FOLDER_ID' with the actual Google Drive folder ID (e.g., '1b02fm9uRILu3IQcJ1LI2aJ0AzrKnKz-g'). This should match the folder ID used in startMultiColumnMerge() to ensure the function accesses the same folder where individual mail merge documents are stored.
Tab Name: The createFinalMergedDocument() function does not directly reference a sheet, so no tab name placeholder is needed. However, if you want to ensure consistency with startMultiColumnMerge(), you can remind users that the spreadsheet’s active sheet (or a specific sheet named, e.g., 'Sheet1') is where the script is triggered from via the custom menu.
// This function combines all Google Docs in a specified folder into a single "FINAL MERGE" document.
// Each document's content is copied into the final document with formatting and a page break between them.
function createFinalMergedDocument() {
// Specify the Google Drive folder ID containing the individual mail merge documents (replace 'FOLDER_ID' with the actual folder ID).
const folderId = 'FOLDER_ID'; // e.g., '1b02fm9uRILu3IQcJ1LJ0AzrKnKz-g'
const folder = DriveApp.getFolderById(folderId);
// Get all files in the folder that are Google Docs (based on MIME type).
const files = folder.getFilesByType(MimeType.GOOGLE_DOCS);
// Create a timestamp for the final document's name (formatted to replace colons and dots with hyphens).
const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
// Create a new Google Doc named "FINAL MERGE [timestamp]" to store the merged content.
const finalDoc = DocumentApp.create(`FINAL MERGE [${timestamp}]`);
const finalBody = finalDoc.getBody();
// Iterate through all Google Docs in the folder.
while (files.hasNext()) {
const file = files.next(); // Get the next Google Doc file.
const doc = DocumentApp.openById(file.getId()); // Open the document by its ID.
const body = doc.getBody(); // Get the document's body content.
// Add an empty paragraph for optional spacing between documents.
finalBody.appendParagraph('');
// Loop through each element (e.g., paragraph, table, list item) in the source document.
for (let i = 0; i < body.getNumChildren(); i++) {
const element = body.getChild(i).copy(); // Copy the element to preserve formatting.
const type = element.getType(); // Get the element's type (e.g., paragraph, table).
// Handle different element types using a switch statement.
switch (type) {
case DocumentApp.ElementType.PARAGRAPH: {
// Append the paragraph text to the final document.
const para = finalBody.appendParagraph(element.getText());
// Apply custom formatting: Arial font, size 20, bold.
para.setFontFamily('Arial');
para.setFontSize(20);
para.setBold(true);
break;
}
case DocumentApp.ElementType.TABLE:
// Append tables directly to the final document.
finalBody.appendTable(element);
break;
case DocumentApp.ElementType.LIST_ITEM:
// Append list items directly to the final document.
finalBody.appendListItem(element);
break;
default:
// For unsupported element types, append the text (if available) or an empty paragraph.
finalBody.appendParagraph(element.getText ? element.getText() : '');
}
}
// Add a page break after each document's content to separate them in the final document.
finalBody.appendPageBreak();
}
// Save and close the final document to ensure all changes are applied.
finalDoc.saveAndClose();
// Move the final document to the specified folder in Google Drive.
const finalFile = DriveApp.getFileById(finalDoc.getId());
folder.addFile(finalFile); // Add the file to the specified folder.
DriveApp.getRootFolder().removeFile(finalFile); // Remove the file from the root 'My Drive' folder.
// Display an alert to the user with the URL of the final merged document.
SpreadsheetApp.getUi().alert('✅ Final merged document created:\n' + finalDoc.getUrl());
}