Bulk create Google Docs from Google Sheet data

The following Google Apps Script is designed to bulk create Google Docs from rows of data within a Google Sheet and to include some of that data within the new Doc body (so they become customised files). It also creates a link to the new Google Doc back in the Sheet on the relevant row.

Screenshot of Google Sheet data

There are some notable features implemented:

1. Toast notifications have been used to inform the user of the progress of the script.
2. Logging has been implemented to support troubleshooting any problems that may occur. It is a slightly improved version of this previous blog post.
3. Try/catch supports the previous logging by capturing any errors within the script and outputting them to the Log sheet.
4. Name:value pairs are used to pass the spreadsheet data to the Function that then creates the Google Docs.

1) getSpreadsheetData.gs
This Function gets data from the Google Sheet - including IDs for the Google Drive folder to store the created Docs, the template file which will be copied for each row and the data itself:

// get Drive Folder ID for storing created Docs
var destinationFolderId = configSheet.getRange(1, 2).getValue();

// get Template File ID
var templateFileId = configSheet.getRange(2, 2).getValue();

// get all data as a 2-D array
var data = dataSheet.getDataRange().getValues();

2) createDocs.gs
This Function takes the data from the Google Sheet and loops through each row to create a Google Doc. It makes use of 'try/catch' in order to capture any errors that could occur with the process to output them into the spreadsheet. The template document (which is copied each time) and the destination folder (location to save the new Google Docs) have their IDs stored in the Config sheet so that they can be easily updated:

// get destination Folder
var destinationFolder = DriveApp.getFolderById(destinationId);

// get Template File
var templateFile = DriveApp.getFileById(templateFileId);

// make copy of Template File and place in destination Drive Folder
var newFile = templateFile.makeCopy(newFileName, destinationFolder);

// get ID and Url of new File
var newFileID = newFile.getId();
var newFileUrl = newFile.getUrl();

Once we have the new Google Doc its ID we can open it, get the body of the document and replace our tags with data from the Google Sheet:

// open document file and get Body
var newFileBody = DocumentApp.openById(newFileID).getBody();

// replace tags with data from sheet
newFileBody.replaceText('<<emailAddress>>', emailAddress);

The final task is to create a link to the Google Doc in the spreadsheet next to the relevant row:

var newFileLink = '=HYPERLINK("' + newFileUrl + '","' + newFileName + '")';
dataSheet.getRange(i+1, 5).setFormula(newFileLink);

Download

Bulk create Google Docs from Google Sheet data download (please use 'File' > 'Make a copy' for your own version).