Bulk create Shared drives with permissions

The following Google Apps Script is designed to bulk create Google Shared drives all from data in a Google Sheet. It has been packaged into a downloadable tool that you can easily use.

• Provide the name of the Shared drive on each row in column A.
• Provide the email address(es) of the Google Account(s) under the relevant Access level column (Manager, Content Manager, Contributor, Commenter, Viewer). Use a comma and a space to separate multiple email addresses, eg: example1@example.com, example2@example.com, example3@example.com.
• Ensure you include at least one Manager - the script will check for this - so as to prevent creating a Shared drive that you are then unable to access.
  
• On the 'Config' sheet provide the column numbers - leave the default values unless you change the structure of the 'Data' sheet and move columns around.
• The 'Log' sheet should help to troubleshoot any errors you experience, but there will also be a popup message should the script fail at some point.
  

Bulk create Shared drives from a Google Sheet

Enable Drive API Service

Make sure you have followed the instructions here to enable the Drive API Service if you are creating this from scratch yourself.

Download

Bulk create Shared drives with permissions download here (please use 'File' > 'Make a copy' for your own version).

	/*
	This overall script is designed to bulk create Shared drives and add user access
	permissions.
	*/
	function getSpreadsheetData() {
	try {
	// Log starting of the script
	logEvent('Script has started');
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get TimeZone here so only do once rather than repeatedly when looping through rows
	var timeZone = ss.getSpreadsheetTimeZone();
	// get Config sheet
	var configSheet = ss.getSheetByName('Config');
	// get 'Shared drive link' column number
	var driveLinkCol = configSheet.getRange(1, 2).getValue();
	// get 'Access Added' column number
	var accessAddedCol = configSheet.getRange(3, 2).getValue();
	// get Data sheet
	var dataSheet = ss.getSheetByName('Data');
	// get all data as a 2-D array
	var data = dataSheet.getDataRange().getValues();
	// run 'shift' twice to remove first two Header rows from the data
	data.shift();
	data.shift();
	// create a name:value pair array to send the data to the next Function
	var spreadsheetData = {
	timeZone: timeZone, driveLinkCol: driveLinkCol, accessAddedCol: accessAddedCol, dataSheet: dataSheet, data: data
	};
	// run Function to create Shared drives and return success flag
	var createSharedDriveFlag = createSharedDrive(spreadsheetData);
	// check success status
	if (createSharedDriveFlag) {
	// display Toast notification
	ss.toast('Script complete', 'Finished');
	}
	else {
	// script completed with error
	// display Toast notification
	ss.toast('With errors. Please see Logs', 'Finished');
	}
	// Log starting of the script
	logEvent('Script finished');
	}
	catch (e) {
	logEvent("Problem with 'getSpreadsheetData' Function: " + e);
	// run Function to launch HTML popup
	var popupTitle = "'getSpreadsheetData' Function error";
	var popupMessage = "Problem with 'getSpreadsheetData' Function: " + e;
	htmlPopup(popupTitle, popupMessage);
	}
	}
	/*
	This Function loops through each row and initiates the creation of a Shared drive
	and the necessary permissions.
	*/
	function createSharedDrive(spreadsheetData) {
	try {
	// extract data from name:value pair array
	var timeZone = spreadsheetData['timeZone'];
	var driveLinkCol = spreadsheetData['driveLinkCol'];
	var accessAddedCol = spreadsheetData['accessAddedCol'];
	var dataSheet = spreadsheetData['dataSheet'];
	var data = spreadsheetData['data'];
	// get length of data array for below loop
	var dataLength = data.length;
	// start of loop to go through each row in turn ********************************
	for (var i = 0; i < dataLength; i++) {
	// get the current row from the loop so can be used elsewhere easily
	var currentRow = i + 3;
	// check there is a 'Manager' email before proceeding
	var managerAccEmails = data[i][1];
	if (managerAccEmails != '') {
	// extract values from row of data so easier to work with
	var sharedDriveName = data[i][0];
	var sharedDriveLink = data[i][6];
	// check Shared drive link column is empty before proceeding, so no existing drive
	if (sharedDriveLink == '') {
	// display Toast notification
	toastPopup('Creating Shared drive', sharedDriveName);
	// run Function to create Shared drive and return its Id
	var sharedDriveID = createDrive(sharedDriveName, currentRow);
	// check new Shared drive created successfully
	if (sharedDriveID) {
	// set Shared drive link cell using new Shared drive ID
	var sharedDriveUrl = "https://drive.google.com/drive/folders/" + sharedDriveID;
	var newSharedDriveLink = '=HYPERLINK("' + sharedDriveUrl + '","' + sharedDriveName + '")';
	dataSheet.getRange(currentRow, driveLinkCol).setFormula(newSharedDriveLink);
	// run Function to add Shared drive permissions
	var addPermissionsFlag = addPermissions(timeZone, i, currentRow, dataSheet, data, accessAddedCol, sharedDriveID, sharedDriveName);
	// if problem adding Permissions return for status message
	if (addPermissionsFlag == false) {
	return false;
	}
	else {
	// no problem adding permissions
	}
	// flush spreadsheet to update each row as completed
	SpreadsheetApp.flush();
	}
	else {
	// write error into 'Shared drive link' cell and return false value
	dataSheet.getRange(i + 3, driveLinkCol).setValue('Error creating Shared drive. Please see Logs');
	// new Shared drive not created successfully
	return false;
	}
	}
	else {
	// Shared drive link column not empty so do nothing
	// Log starting of the script
	logEvent('Shared drive link column not empty, skipping row: ' + currentRow);
	}
	}
	else {
	// no 'Manager' email address present in Google Sheet
	logEvent('No Manager email address found for row: ' + currentRow);
	// run Function to launch HTML popup
	var popupTitle = "No 'Manager'";
	var popupMessage = "Please make sure there is a 'Manager' for the new Shared drive on row " + currentRow + ".";
	htmlPopup(popupTitle, popupMessage);
	// return False to signal issue has occurred.
	return false;
	}
	}// end of loop to go through each row in turn **********************************
	// completed successfully
	return true;
	}
	catch (e) {
	logEvent("Problem with 'createSharedDrive' Function: " + e);
	// run Function to launch HTML popup
	var popupTitle = "'createSharedDrive' Function error";
	var popupMessage = "Problem with 'createSharedDrive' Function: " + e;
	htmlPopup(popupTitle, popupMessage);
	return false;
	}
	}
	/*
	Function to create new Shared drive and return its Id.
	*/
	function createDrive(sharedDriveName, currentRow) {
	try {
	// random request ID for creating a Shared drive
	var requestID = Utilities.getUuid();
	// name for Shared drive in suitable format
	var name = {
	name: sharedDriveName
	};
	// create Shared drive
	var newSharedDrive = Drive.Drives.insert(name, requestID);
	// Log starting of the script
	logEvent('Created Shared drive: ' + sharedDriveName);
	// get ID of new Shared drive
	var newSharedDriveID = newSharedDrive.id;
	// return Shared drive ID to Parent Function
	return newSharedDriveID;
	}
	catch (e) {
	logEvent("Problem with 'createDrive' Function Row " + currentRow + ": " + e);
	// run Function to launch HTML popup
	var popupTitle = "'createDrive' Function error Row " + currentRow;
	var popupMessage = "Problem with 'createDrive' Function: " + e;
	htmlPopup(popupTitle, popupMessage);
	return false;
	}
	}
	/*
	Function to add relevant permissions/access to Shared drive.
	Needed to separate out 'Commenter' role as cannot add to Shared drive
	in same manner as others.
	https://developers.google.com/drive/api/v2/reference/permissions/insert?hl=en
	*/
	function addPermissions(timeZone, i, currentRow, dataSheet, data, accessAddedCol, sharedDriveID, sharedDriveName) {
	try {
	// extract data from name:value pair array
	var managerAccEmails = data[i][1];
	var conManagerAccEmails = data[i][2];
	var contributorAccEmails = data[i][3];
	var commenterAccEmails = data[i][4];
	var viewerAccEmails = data[i][5];
	// run Function to sort each email access type and return an array ****************
	var managerAcc = sortEmailArrays(managerAccEmails, currentRow);
	if (managerAcc) {
	var conManagerAcc = sortEmailArrays(conManagerAccEmails, currentRow);
	if (conManagerAcc) {
	var contributorAcc = sortEmailArrays(contributorAccEmails, currentRow);
	if (contributorAcc) {
	var commenterAcc = sortEmailArrays(commenterAccEmails, currentRow);
	if (commenterAcc) {
	var viewerAcc = sortEmailArrays(viewerAccEmails, currentRow);
	}
	else {
	return false;
	}
	}
	else {
	return false;
	}
	}
	else {
	return false;
	}
	}
	else {
	return false;
	}
	// run Function to sort each email access type and return an array ****************
	if (viewerAcc) {
	// organise various roles to be added - as an array of email
	// ignore 'Commenter' for now as requires different process
	var roles = {
	organizer: managerAcc, // Manager
	fileOrganizer: conManagerAcc, // Content Manager
	writer: contributorAcc, // Contributor
	reader: viewerAcc // Viewer
	};
	// go through the above roles
	for (var key in roles) {
	// assign a key for 'organizer, fileOrganizer, ...
	var role = roles[key];
	// go through each role and create a resource for adding permissions
	role.forEach(function (email) {
	var resource = {
	role: key,
	type: "user",
	value: email,
	}
	// optional arguments to work on Shared drive
	var optionalArgs = {
	sendNotificationEmails: false,
	supportsAllDrives: true
	}
	// set Shared drive permissions
	Drive.Permissions.insert(resource, sharedDriveID, optionalArgs);
	});
	};
	// add 'Commenter' access - done separately as requires separate steps ******
	if (commenterAcc.length > 0) {
	// loop through each email address in array and add permission to Shared drive
	commenterAcc.forEach(function (email) {
	var resource = {
	role: 'reader',
	type: "user",
	value: email,
	additionalRoles: ["commenter"]
	}
	// optional arguments to work on Shared drive
	var optionalArgs = {
	sendNotificationEmails: false,
	supportsAllDrives: true
	}
	// set Shared drive permissions
	Drive.Permissions.insert(resource, sharedDriveID, optionalArgs);
	});
	// add 'Commenter' access - done separately as requires separate steps ******
	}
	else {
	// do nothing as there are no 'Commenters' to add
	}
	// Log starting of the script
	logEvent('Added permissions to Shared drive: ' + sharedDriveName);
	// write timestamp into 'Access Added' cell
	var date = new Date;
	var timeStamp = Utilities.formatDate(date, timeZone, "dd/MM/yy @ HH:mm:ss");
	dataSheet.getRange(currentRow, accessAddedCol).setValue(timeStamp);
	// return true as all permissions added successfully
	return true;
	}
	else {
	return false;
	}
	}
	catch (e) {
	// log error
	logEvent("Problem with 'addPermissions' Function Row " + currentRow + ": " + e);
	// run Function to launch HTML popup
	var popupTitle = "'addPermissions' Function error Row " + currentRow;
	var popupMessage = "Problem with 'addPermissions' Function: " + e;
	htmlPopup(popupTitle, popupMessage);
	// return False to signal issue has occurred.
	return false;
	}
	}
	/*
	Function to organise emails from cells in Sheet into arrays for adding
	Shared drive permissions.
	*/
	function sortEmailArrays(accessEmails, currentRow) {
	try {
	// create empty array to push emails into
	var emptyArray = [];
	if (accessEmails) {
	// split up email address array to be able to loop through them separately
	var emailAddresses = accessEmails.split(', ');
	// get length of array for loop
	var emailAddressesLength = emailAddresses.length;
	for (var i = 0; i < emailAddressesLength; i++) {
	// extract each email address and push into array
	var singleEmail = emailAddresses[i];
	emptyArray.push(singleEmail);
	};
	}
	else {
	// do nothing as no emails to handle
	}
	// return array
	return emptyArray;
	}
	catch (e) {
	// log error
	logEvent("Problem with 'sortEmailArrays' Function Row " + currentRow + ": " + e);
	// run Function to launch HTML popup
	var popupTitle = "'sortEmailArrays' Function error Row " + currentRow + ": " + e;
	var popupMessage = "Problem with 'sortEmailArrays' Function: " + e;
	htmlPopup(popupTitle, popupMessage);
	/*
	return false value to signal Function problem,
	*/
	return false;
	}
	}
	/*
	This Function creates a menu item to run this script.
	*/
	function onOpen() {
	SpreadsheetApp.getUi()
	.createMenu('Admin')
	.addItem('Create Shared drives', 'getSpreadsheetData') // label for menu item, name of function to run.
	.addToUi();
	}
	/*
	Function to output messages to the 'Log' sheet.
	Can be called anywhere else in script.
	*/
	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);
	}
	/*
	Function to create a Toast Popup notification with customised message.
	Created as standalone so can be called from anywhere else within Project.
	*/
	function toastPopup(msg, title) {
	// get Spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// create Toast Popup
	ss.toast(msg, title);
	}
	/*
	Display a modal dialog box with custom HtmlService content.
	Does not suspend the script.
	Message needs to include '<p></p>' tags.
	*/
	function htmlPopup(popupTitle, popupMessage) {
	var htmlOutput = HtmlService
	.createHtmlOutput(popupMessage)
	.setWidth(360)
	.setHeight(180);
	SpreadsheetApp.getUi().showModalDialog(htmlOutput, popupTitle);
	}

view raw Bulk create Shared drives with permissions.gs hosted with ❤ by GitHub