Google Apps Script to access Google Sheet in a folder via Google Apps Script

Question

Suppose I have a drive with the following structure:

Under My drive:

google_sheet_E
Main_Folder
- Folder_A
- Folder_B
  - google_sheet_C (in google sheet format)
  - MS_excel_D.xls (in excel format, upload to drive directly)

Note: google_sheet_E is located on the top layer of the drive, whereas, the C, and D, are located few levels below.

I would like to reference some data from google_sheet_C and MS_excel_D.xls to google_sheet_E.

In Google Apps Script, my current method is to get by ID, but I find this method inconvenient, since I have to get ID every time, therefore I am planning to use the file name instead. Is there any method/function to access a sheet similar to the path system in Windows? (i.e. Traverse the folders.)

Another question: Is there any good method to let users share from their drive to the spreadsheet automatically under a specific path?

You can use DriveApp to access files using folders and filenames: see this answer, for example. — user3717023

Mogsdad Mogsdad · Accepted Answer · 2016-06-12T15:00:10

Is there any method/function to access a sheet similar to the path system in Windows?

Bruce MacPherson has published just such a function:

/**
 * Returns a DriveApp folder object corresponding to the given path.
 *
 * From: http://ramblings.mcpher.com/Home/excelquirks/gooscript/driveapppathfolder
 */
function getDriveFolderFromPath (path) {
  return (path || "/").split("/").reduce ( function(prev,current) {
    if (prev && current) {
      var fldrs = prev.getFoldersByName(current);
      return fldrs.hasNext() ? fldrs.next() : null;
    }
    else { 
      return current ? null : prev; 
    }
  },DriveApp.getRootFolder()); 
}

You could use it like this:

// Get handle for folder
var folder = getDriveFolderFromPath("Main_Folder/Folder_B");

// Find file by name within folder. (Assumes just one match.)
var fileId = folder.getFilesByName("google_sheet_C").next().getId();

// Open spreadsheet using fileId obtained above.
var spreadsheet = SpreadsheetApp.openById(fileId);
...

Google Apps Script to access Google Sheet in a folder via Google Apps Script

1 Answers