CODEWITHSUNDEEP
google-apps-script
Databoy2k
Databoy2k

Reputation: 123

Link to Google Drive from Content of Google Sheets

I'm absolutely swimming in code issues and can't seem to get this working. I'm hoping that this group of wise Google Apps Script-geniuses can help.

First: My Google Drive uses the following folder structure (with first subfolders for each letter of the alphabet and then second subfolders for each matter, e.g. 

/Clients
 /A
   /Albert, Bob (1-15-0003) Re Matter
 /B
  /Bork, Mat (1-54-0003) Re Other Matter

I have a Google Sheets document with various sheets that each refer to a different matter number in their respective cell G2. I would like a script that automatically searches my Google Drive for those second subfolders and creates a hyperlink to the folder.

My code is as follows, but sadly it doesn't work. It seems that it can't find the subfolders.

function SearchFolder() {
// Searches Google Drive for the folder for the Active Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var searchTerm = ss.getRange('G2').getValue();
  var Folders = DriveApp.searchFolders("title contains '"+searchTerm.replace("'","\'")+"' and trashed = false and hidden = false");
  var Folder = Folders.next();
sheet.getRange('G2').setFormula("=HYPERLINK(\""+Folder.getUrl()+"\",\""+searchTerm+"\")");
}

Expected result would be, if G2 of my current sheet reads 1-15-0003 for that G2 to be replaced with a hyperlink with the Google Drive URL pointing to the folder ID for /A/Albert, Bob (1-15-0003) Re Matter.

Thanks for any help.

Upvotes: 1

Views: 724

Answers (2)

Databoy2k
Databoy2k

Reputation: 123

As a followup, I think my mistake was in the getvalue command. My code that is now working (obviously having changed the relevant cell, and assuming the sheet name to be included in the name of the folder) is:

function SearchFolder() {
// Searches Google Drive for the folder for the Active Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var searchTerm = ss.getSheetName();
  var Folders = DriveApp.searchFolders("title contains '"+searchTerm.replace("'","\'")+"' and trashed = false and hidden = false");
  var Folder = Folders.next();
  sheet.getRange('K6').setFormula("=HYPERLINK(\""+Folder.getUrl()+"\",\""+searchTerm+"\")");
}

Upvotes: 0

Cooper
Cooper

Reputation: 64032

An Interactive Directory Tree

These functions work together with the JStree JQuery plugin to produce a directory interactive tree. There's a couple of Id's you'll have to add namely the ID of the DataStorage Folder and the ID of the Root Directory in your tree. There is also a doGet function already done in here in case you want a webapp. There are several files that I use for html as well as some html code that is integrated into the traverse code. It was a fairly complicated project for me. There are probably better ways to do 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();" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <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();" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="button" value="Delete Cache & Exit" onClick="google.script.run.clearDirlist();google.script.host.close();" /></div><br /></body></html>

DataStorage Folder: Also contains file: CurrentDirectoryListing

This is 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 + ' was removed from: ' + fldr.getName() + '/' + target);
    }
  }
  return targetFound;
}

function doGet()
{
  var output=HtmlService.createHtmlOutput(getStoredDirectory());
  return output.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

This is traverse.gs:

function runtraverseFolder()
{
  var dirlist = loadDirlist();
  var controls = '<div class="cntrls"><input type="button" value="Exit" onClick="google.script.host.close()" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\
  <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 get 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 get 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 to 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 was updated using: ' + target);    }
  }
  if(!targetFound)
  {
    var created = fldr.createFile('CurrentDirectoryListing',timeStamp + GStorage);
    if(created)
    {
      //SpreadsheetApp.getUi().alert( 'Directory Listing was stored here: ' + fldr.getName() + '/' + created.getName());
    }
    else
    {
      SpreadsheetApp.getUi().alert('Unknown Error: Directory List was 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 was 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 + ' was removed from: ' + fldr.getName() + '/' + target);
    }
  }
  return targetFound;
}

This is utility.gs:

function dispStatus(title,html,width,height)
{
// Display a modeless dialog box with 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);
}

Upvotes: 1

Related Questions