Link to Google Drive from Content of Google Sheets -
i'm absolutely swimming in code issues , can't seem working. i'm hoping group of wise google apps script-geniuses can help.
first: google drive uses following folder structure (with first subfolders each letter of alphabet , second subfolders each matter, e.g.
/clients /a /albert, bob (1-15-0003) re matter /b /bork, mat (1-54-0003) re other matter i have google sheets document various sheets each refer different matter number in respective cell g2. script automatically searches google drive second subfolders , creates hyperlink folder.
my code follows, sadly doesn't work. seems can't find subfolders.
function searchfolder() { // searches google drive folder active sheet var ss = spreadsheetapp.getactivespreadsheet(); var sheet = ss.getactivesheet(); var searchterm = ss.getrange('g2').getvalue(); var folders = driveapp.searchfolders("title contains '"+searchterm.replace("'","\'")+"' , trashed = false , hidden = false"); var folder = folders.next(); sheet.getrange('g2').setformula("=hyperlink(\""+folder.geturl()+"\",\""+searchterm+"\")"); } expected result be, if g2 of current sheet reads 1-15-0003 g2 replaced hyperlink google drive url pointing folder id /a/albert, bob (1-15-0003) re matter.
thanks help.
an interactive directory tree
these functions work jstree jquery plugin produce directory interactive tree. there's couple of id's you'll have add namely id of datastorage folder , id of root directory in tree. there doget function done in here in case want webapp. there several files use html html code integrated traverse code. complicated project me. there better ways this.
file in datastorage folder: toptobodyweb
<html> <head> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jstree/3.2.1/themes/default/style.min.css" /> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jstree/3.2.1/jstree.min.js"></script> <style>.file{color:#d81840;}.contrls{padding:10px 10px 10px 10px;}.timestmp {width: 200px;margin-left:5px;padding:10px 0px 5px 50px;background-color:rgba(121, 25, 0, 0.36);color:white;}i.jstree-icon.jstree-themeicon{display:none;}</style> <script type="text/javascript"> $(function () { $('#selector').jstree({"core" : {"themes":{"variant":"large"}},"plugins" : [ "wholerow"]}); }); </script> </head> <body><div id="selector"><ul> file in datastorage folder: toptobody
<html> <head> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jstree/3.2.1/themes/default/style.min.css" /> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/jstree/3.2.1/jstree.min.js"></script> <style>.file{color:#d81840;}.contrls{padding:10px 10px 10px 10px;}.timestmp {width: 200px;margin-left:5px;padding:10px 0px 5px 50px;background-color:rgba(121, 25, 0, 0.36);color:white;}i.jstree-icon.jstree-themeicon{display:none;}</style> <script type="text/javascript"> $(function () { $('#selector').jstree({"core" : {"themes":{"variant":"large"}},"plugins" : [ "wholerow"]}); }); </script> </head> <body><div class="cntrls"><input type="button" value="exit" onclick="google.script.host.close();" /> <input type="button" value="delete cache & exit" onclick="google.script.run.cleardirlist();google.script.host.close();" /></div><div id="selector"><ul> file in datastorage folder: bodytoendweb
</ul></div></body></html> file in datastorage folder: bodytoend
</ul></div><div class="cntrls"><input type="button" value="exit" onclick="google.script.host.close();" /> <input type="button" value="delete cache & exit" onclick="google.script.run.cleardirlist();google.script.host.close();" /></div><br /></body></html> datastorage folder: contains file: currentdirectorylisting
this code.gs:
var gstorage = ''; var folderid = "theidofdatastoragefoldergoeshere"; var guli = 0; var glii = 0; var glevel = 0; function onopen() { var ui = spreadsheetapp.getui(); ui.createmenu('my tools') .additem('dir mydrive','runtraversefolder') .addtoui(); } function runfile() { var file = loadfile(); if(file) { dispstatus('stored mydrive directory list', file, 1000, 600); } else { spreadsheetapp.getui().alter('file not found in function runfile'); } } function loadfile(filename) { var filename = (typeof(filename) !== 'undefined')? filename : 'uploadfile'; var fldr = driveapp.getfolderbyid(folderid); var file = fldr.getfilesbyname(filename); var s = ''; while(file.hasnext()) { var fi = file.next(); var target = fi.getname(); if(target == filename) { s = fi.getblob().getdataasstring(); } } return s; } function delfile(filename) { var filename = (typeof(filename) !== 'undefined')? filename : 'uploadfile'; var fldr = driveapp.getfolderbyid(folderid) var file = fldr.getfilesbyname(filename); var targetfound = false; while(file.hasnext()) { var fi = file.next(); var target = fi.getname(); if(target == filename) { targetfound = true; fldr.removefile(fi); spreadsheetapp.getui().alert('file: ' + filename + ' removed from: ' + fldr.getname() + '/' + target); } } return targetfound; } function doget() { var output=htmlservice.createhtmloutput(getstoreddirectory()); return output.setxframeoptionsmode(htmlservice.xframeoptionsmode.allowall); } this traverse.gs:
function runtraversefolder() { var dirlist = loaddirlist(); var controls = '<div class="cntrls"><input type="button" value="exit" onclick="google.script.host.close()" /> \ <input type="button" value="delete cached listing & start over" onclick="google.script.run.cleardirlist();" /></div><br />'; var toptobody = loadfile('toptobody'); var bodytoend = loadfile('bodytoend'); if(dirlist) { dispstatus('stored mydrive directory list', toptobody + gstorage + bodytoend, 600, 500); } else { gstorage =''; gflag = 0; traversefolder(driveapp.getfolderbyid('thisistheidoftheroottraversefolder')); savedirlist(); dispstatus('current mydrive directory listing', toptobody + gstorage + bodytoend, 600, 500); } } function getstoreddirectory() { loaddirlist(); var toptobody = loadfile('toptobodyweb'); var bodytoend = loadfile('bodytoendweb'); return toptobody + gstorage + bodytoend; } function traversefolder(folderobj) { glevel++; if(glevel < 2) { gstorage += '<li class="fldr" id="fd' + glii++ + '"' + 'data-jstree=\'{ "selected" : true, "opened" : true }\'' + '><a href="https://drive.google.com/open?id='+ folderobj.getid() +'" target="_blank" >folder: ' + folderobj.getname() + '</a></span>'; //gstorage += '<li class="fldr" id="fd' + glii++ + '"' + 'data-jstree=\'{ "selected" : true, "opened" : true }\'' + '><a href="#" target="_blank" title="dummy link can not file listing" >folder: ' + folderobj.getname() + '</a></span>'; } else { gstorage += '<li class="fldr" id="fd' + glii++ + '"><a href="https://drive.google.com/open?id=' + folderobj.getid() +'" target="_blank" >folder: ' + folderobj.getname() + '</a></span>'; //gstorage += '<li class="fldr" id="fd' + glii++ + '"><a href="#" target="_blank" title="dummy link can not file listing." >folder: ' + folderobj.getname() + '</a></span>'; } gstorage += '<ul id="ul'+ guli++ +'">'; var subs = folderobj.getfolders(); var files = folderobj.getfiles(); if(files) { //gstorage += '<ul id="ul'+ guli++ +'">'; while(files.hasnext()) { var fi = files.next();; gstorage += '<li class="file" id="fi' + glii++ + '"><a href="https://drive.google.com/open?id='+ fi.getid() +'" target="_blank" title="right click open file in new tab." >file: ' + fi.getname() + '</a></span>'; } //gstorage += '</ul>'; } while (subs.hasnext()) { traversefolder(subs.next()); } gstorage += '</ul></li>'; glevel--; } function savedirlist() { var filename = 'currentdirectorylisting'; var fldr = driveapp.getfolderbyid(folderid); var fldrname = fldr.getname(); var file = fldr.getfilesbyname(filename); var targetfound = false; var timestamp = '<div class="timestmp">' + utilities.formatdate(new date(), "gmt-7", "yyyy-mm-dd hh:mm:ss") + '</div>'; while(file.hasnext()) { var fi = file.next(); var target = fi.getname(); if(target == filename) { targetfound = true; fi.setcontent(timestamp + gstorage); spreadsheetapp.getui().alert('directory listing updated using: ' + target); } } if(!targetfound) { var created = fldr.createfile('currentdirectorylisting',timestamp + gstorage); if(created) { //spreadsheetapp.getui().alert( 'directory listing stored here: ' + fldr.getname() + '/' + created.getname()); } else { spreadsheetapp.getui().alert('unknown error: directory list not stored'); } } } function loaddirlist() { var filename = 'currentdirectorylisting'; var fldr = driveapp.getfolderbyid(folderid); var file = fldr.getfilesbyname(filename); var targetfound = false; while(file.hasnext()) { var fi = file.next(); var target = fi.getname(); if(target == filename) { targetfound = true; gstorage = fi.getblob().getdataasstring(); //spreadsheetapp.getui().alert('directory listing retreived from: ' + fldr.getname() + '/' + target); } } return targetfound; } function cleardirlist() { var filename = 'currentdirectorylisting'; var fldr = driveapp.getfolderbyid(folderid) var file = fldr.getfilesbyname(filename); var targetfound = false; while(file.hasnext()) { var fi = file.next(); var target = fi.getname(); if(target == filename) { targetfound = true; fldr.removefile(fi); //spreadsheetapp.getui().alert('file: ' + filename + ' removed from: ' + fldr.getname() + '/' + target); } } return targetfound; } utility.gs: function dispstatus(title,html,width,height) { // display modeless dialog box custom htmlservice content. var title = typeof(title) !== 'undefined' ? title : 'no title provided'; var width = typeof(width) !== 'undefined' ? width : 250; var height = typeof(height) !== 'undefined' ? height : 300; var html = typeof(html) !== 'undefined' ? html : '<p>no html provided.</p>'; var htmloutput = htmlservice .createhtmloutput(html) .setwidth(width) .setheight(height); spreadsheetapp.getui().showmodelessdialog(htmloutput, title); } 
Comments
Post a Comment