Bulk create Sheets from a Google Sheet

The following Google Apps Script is designed to bulk create Sheets from rows of data within a Google Sheet and to include some of that data within the new file in specific cells. It also creates a link to the new Google Sheet back in the original file on the relevant row.

For those wanting to bulk create Google Docs from a Google Sheet please see this blog post.

Screenshot of Google Sheets data

It takes data from the first four columns and will copy that into the new Google Sheet template file that is created for that row.

There is a Config sheet where you will need to specify some further information:

1. The ID of the Google Drive folder where you wish to store the newly created Google Sheets.
2. The ID of the template Google Sheet that is to be copied for each row.
3. The name of the Sheet/Tab in the template file where data is to be copied to. This option tries to make the tool universal as you may have other data in the Google Sheet that needs to be left untouched.
4. The four cell references for each piece of data that it to be copied in to the newly created Google Sheet. Again to be universal this allows you to specify exactly which cells you want the information to be put into.
   

Download

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

	/*
	This overall script is designed to bulk create Google Sheets from data within a Google Sheet.
	*/
	function getSpreadsheetData() {
	// Log starting of the script
	logEvent('Script has started');
	// get current spreadsheet
	var spreadsheet = SpreadsheetApp;
	var ss = spreadsheet.getActiveSpreadsheet();
	// display Toast notification to inform starting of the script
	ss.toast('Script has now started', 'Start');
	// get Config sheet
	var configSheet = ss.getSheetByName('Config');
	// get Drive Folder ID for storing created Docs
	var destinationFolderId = configSheet.getRange(1, 2).getValue();
	Logger.log('destinationFolderId is: ' + destinationFolderId);
	// get Template File ID
	var templateFileId = configSheet.getRange(2, 2).getValue();
	Logger.log('templateFileId is: ' + templateFileId);
	// get Cell References for mapping data
	var destSheet = configSheet.getRange(3, 2).getValue();
	var emailRef = configSheet.getRange(4, 2).getValue();
	var firstRef = configSheet.getRange(5, 2).getValue();
	var lastRef = configSheet.getRange(6, 2).getValue();
	var shoeRef = configSheet.getRange(7, 2).getValue();
	// get Data sheet
	var dataSheet = ss.getSheetByName('Data');
	// get all data as a 2-D array
	var data = dataSheet.getDataRange().getValues();
	// create a name:value pair array to send the data to the next Function
	var spreadsheetData = {
	spreadsheet: spreadsheet, dataSheet: dataSheet, destinationFolderId: destinationFolderId,
	templateFileId: templateFileId, destSheet: destSheet, emailRef: emailRef, firstRef: firstRef, lastRef: lastRef, shoeRef: shoeRef, data: data
	};
	// run Function to create Google Sheets
	createSheets(spreadsheetData);
	}
	function createSheets(spreadsheetData) {
	// Log starting createDocs Function
	logEvent('Starting createSheets Function');
	// separate out data from name:value pair array
	var spreadsheet = spreadsheetData['spreadsheet'];
	var ss = spreadsheet.getActiveSpreadsheet();
	var dataSheet = spreadsheetData['dataSheet'];
	var destinationFolderId = spreadsheetData['destinationFolderId'];
	var templateFileId = spreadsheetData['templateFileId'];
	var destSheet = spreadsheetData['destSheet'];
	var emailRef = spreadsheetData['emailRef'];
	var firstRef = spreadsheetData['firstRef'];
	var lastRef = spreadsheetData['lastRef'];
	var shoeRef = spreadsheetData['shoeRef'];
	var data = spreadsheetData['data'];
	try {
	// try getting the Folder and then set variable as true if successful
	var destinationFolder = DriveApp.getFolderById(destinationFolderId);
	var gotDestinationFolder = true;
	}
	catch (e) {
	// if failed set variable as false and Log
	var gotDestinationFolder = false;
	logEvent('Failed to get destinationFolder with error: ' + e);
	}
	// only proceed if got destination Drive Folder
	if (gotDestinationFolder) {
	try {
	// try getting the Template File and then set variable as true if successful
	var templateFile = DriveApp.getFileById(templateFileId);
	var gotTemplateFile = true;
	}
	catch (e) {
	var gotTemplateFile = false;
	logEvent('Failed to get templateFile with error: ' + e);
	}
	}
	else {
	// do nothing
	}
	// only proceed if got Template File also
	if (gotTemplateFile) {
	// loop through each row of data and create new Sheet ******************************
	var dataLength = data.length;
	for (var i = 1; i < dataLength; i++) {
	// extract values from array of data
	var emailAddress = data[i][0];
	var firstName = data[i][1];
	var lastName = data[i][2];
	var shoeSize = data[i][3];
	// create name for new File
	var newFileName = 'Bulk create Sheets - ' + lastName + ', ' + firstName;
	// 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();
	try {
	// try opening the new File, get the named Sheet and then set variable as true if successful
	var newFileBody = SpreadsheetApp.openById(newFileID).getSheetByName(destSheet);
	var openNewFile = true;
	}
	catch (e) {
	// if failed set variable as false and Log
	var openNewFile = false;
	logEvent('Failed to open newFileBody with error: ' + e);
	}
	// only proceed if got Body of new File
	if (openNewFile) {
	// replace tags with data from sheet
	newFileBody.getRange(emailRef).setValue(emailAddress);
	newFileBody.getRange(firstRef).setValue(firstName);
	newFileBody.getRange(lastRef).setValue(lastName);
	newFileBody.getRange(shoeRef).setValue(shoeSize);
	// paste a hyperlink to new File in spreadsheet
	var newFileLink = '=HYPERLINK("' + newFileUrl + '","' + newFileName + '")';
	dataSheet.getRange(i + 1, 5).setFormula(newFileLink);
	// refresh spreadsheet so links appear as soon as added
	spreadsheet.flush();
	// display Toast notification to confirm row complete
	ss.toast('Row ' + (i + 1) + ': ' + newFileName, 'Completed Sheet');
	}
	else {
	// do nothing
	}
	}
	// loop through each row of data and create new Sheet ******************************
	}
	else {
	// do nothing
	}
	// display Toast notification to inform end of the script
	ss.toast('Script has now ended', 'End');
	// Log end of the script
	logEvent('Script has ended');
	}
	function logEvent(action) {
	// get the user running the script
	var theUser = Session.getActiveUser().getEmail();
	// get the relevant spreadsheet to output log details
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var logSheet = ss.getSheetByName('Log');
	// create and format a timestamp
	var dateTime = new Date();
	var timeZone = ss.getSpreadsheetTimeZone();
	var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");
	// create array of data for pasting into log sheet
	var logData = [niceDateTime, theUser, action];
	// append details into next row of log sheet
	logSheet.appendRow(logData);
	}
	/*
	Create menu item to run script from spreadsheet.
	*/
	function onOpen() {
	SpreadsheetApp.getUi()
	.createMenu('Create Sheets')
	.addItem('Create Sheets', 'getSpreadsheetData')
	.addToUi();
	}

view raw Bulk create Sheets from a Google Sheet.gs hosted with ❤ by GitHub