Created
June 29, 2016 19:54
-
-
Save takvol/191c64a56fd0ea3a634bb2e038ff75ee to your computer and use it in GitHub Desktop.
Create directories in google drive from folder structure saved in spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//Script limit is 6 min = 360 sec and folder creation operation lasts for half of a second. | |
//So max folders created in one run is around 360sec * 2 = 720 | |
//To prevent time limit error was used workaround from https://ctrlq.org/code/20016-maximum-execution-time-limit | |
function onOpen(e) { | |
SpreadsheetApp.getUi() | |
.createMenu('Create folders') | |
.addItem('Create in current folder', 'createFolders') | |
.addItem('Create in current folder, starting from row', 'createAtRow') | |
.addItem('Create in root', 'createAtRoot') | |
.addItem('Create in root, starting from row', 'createAtRootAtRow') | |
.addToUi(); | |
} | |
function onInstall(e) { | |
//run once to grant access rights to the script | |
} | |
function createAtRoot() { | |
var folder = DriveApp.getRootFolder(); | |
createFolders(0, folder); | |
} | |
function createAtRootAtRow() { | |
var folder = DriveApp.getRootFolder(); | |
createAtRow(folder); | |
} | |
function createAtRow(folder) { | |
var row = Browser.inputBox("Enter starting row"); | |
while(isNaN(row) || row % 1 !== 0 || row < 1) { | |
if(row == "cancel") { | |
return; | |
} | |
row = Browser.inputBox("Invalid parameter '" + row + "'. Enter starting row"); | |
} | |
createFolders(row - 1, folder); | |
} | |
function isTimeUp(start) { | |
var now = new Date(); | |
return now.getTime() - start.getTime() > 300000; // 5 minutes | |
} | |
function createFolders(startRow, folder) { | |
try { | |
var msg; | |
var start = new Date(); | |
var sheetName = "Sheet1"; | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); | |
var values = sheet.getDataRange().getValues(); | |
var valuesLength = values.length; | |
var currentFolder = folder || DriveApp.getFolderById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next(); | |
for(var i = startRow || 0 ; i < valuesLength; i++) { | |
if (isTimeUp(start)) { | |
msg = ("Time out exception. Ended on row " + i); | |
break; | |
} | |
var row = values[i]; | |
var rowLength = row.length; | |
var tempFolder = currentFolder; | |
for(var j = 0; j < rowLength; j++) { | |
if(row[j] != "") { | |
if(tempFolder.getFoldersByName(row[j]).hasNext()) { | |
tempFolder = tempFolder.getFoldersByName(row[j]).next(); | |
} else { | |
tempFolder = tempFolder.createFolder(row[j]); | |
} | |
} | |
} | |
} | |
if(msg) { | |
Browser.msgBox(msg); | |
} | |
} catch(e) { | |
throw e; | |
} | |
} |
Author
takvol
commented
Jun 29, 2016
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment