add link to google sheet from folder name in drive

Question

I am trying to find a way to add a new folder to google drive with name coming from the cell input in Column B in google sheets.

Or if easier add folder manually to drive and have script in google sheets add that folder name to a new row in google sheets column B.

Also either way i do it, i need the cell in column B to hyperlink to that new folder.

eg Jobs Parent folder job45846 job folder add link to this folder into cell in google sheets job45893 job folder add link to this folder into new row cell in google sheets

IMHO, your second option is easier. You may want to check this SO post wherein it was stated that you just have to share created folder as public, copy the folder id from its URL and make a new url googledrive.com/host/folderid/a.jpg. Sample code which creates a new Spreadsheet with all Folder names and all corresponding URLS was also given. — Teyam
Thanks Teyam i had found that before i posted but that script lists the files inside a folder with a link in the column next to the filename. I need the folders to be listed with the link clickable to the drive folder on the folder name in the same column. i will have a bit more of a play around with the script see if i can get it working. — Brad

Brad Brad · Accepted Answer · 2017-02-24T12:03:37

function createAndHyperlink() {
var ss, sh, parent, r, v, thisCell, folder
ss = SpreadsheetApp.getActive()
sh = ss.getSheetByName('Sheet1')
parent = DriveApp.getFolderById('folderid');
r = sh.getRange('A1:A')
v = r.getValues()
for (var i = 0, l = v.length; i < l; i++) {
    thisCell = sh.getRange(i + 1, 1)
    if (v[i][0] && !thisCell.getFormula()) {
        folder = parent.createFolder(v[i][0]);
        thisCell.setFormula('=HYPERLINK("' + folder.getUrl() + '"; "' + v[i][0] + '")');
    }
}

}

add link to google sheet from folder name in drive

1 Answers