CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
jasond
jasond

Reputation: 17

Unable to Pull Parent Folder Name Using getParents

I am trying to get the name of the Parent Folder for a spreadsheet that is determined by url and then place the name in a specified cell. Debugging the script below tells me "TypeError: Cannot find function getParents in object Spreadsheet." I have tried every tweak I can think of and read multiple similar articles that may answer my question, but I am unable to understand them. (Forgive the newbie?) Can somebody tell me what I am doing wrong?

function getParent(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("VALUES");
var ll = s.getRange("B11");
var url = ll.getValue();
var driveFile = SpreadsheetApp.openByUrl(url); 
var parentFolder = driveFile.getParents();
while (parentFolder.hasNext()) {
var folder = parentFolder.next();
var title = folder.getName();
}
s.getRange("B5").setValue(title);
}

Upvotes: 0

Views: 773

Answers (2)

Cooper
Cooper

Reputation: 64032

Try this:

function getParent(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("VALUES");
  var url=sh.getRange("B11").getValue();
  var file=DriveApp.getFileById(url.split('/')[5]);//gets the id out of the url 
  var parentFolder=file.getParents();
  while (parentFolder.hasNext()) {
    var folder = parentFolder.next();
    var title = folder.getName();
  }
  sh.getRange("B5").setValue(title);
}

Upvotes: 0

Maxime Munger
Maxime Munger

Reputation: 86

It seems like this code has two small issues which we'll fix to get you up and running on this.

  1. If possible, it is generally a better practice to work with document IDs rather than URLs. Also, for a reason I'm not aware of, DriveApp does not have a getFileByURL method. So I have changed your "url" variable for "fileId", and changed the contents of cell B11 to the Id of the file, which is part of the URL itself.

  2. In order to return a Drive File, you should call getFileByID() not on your Spreadsheet itself, but on the DriveApp service.

I've tested the following code and I believe it does what you expect:

function getParent(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("VALUES");
  var ll = s.getRange("B11");
  var fileId = ll.getValue();
  var driveFile = DriveApp.getFileById(fileId);
  var parentFolder = driveFile.getParents();
  while (parentFolder.hasNext()) {
    var folder = parentFolder.next();
    var title = folder.getName();
  }
  s.getRange("B5").setValue(title);
}

Upvotes: 2

Related Questions