Skip to content

Instantly share code, notes, and snippets.

@takvol
Created June 29, 2016 19:54
Show Gist options
  • Save takvol/191c64a56fd0ea3a634bb2e038ff75ee to your computer and use it in GitHub Desktop.
Save takvol/191c64a56fd0ea3a634bb2e038ff75ee to your computer and use it in GitHub Desktop.
Create directories in google drive from folder structure saved in spreadsheet
//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;
}
}
@takvol
Copy link
Author

takvol commented Jun 29, 2016

script

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment