Google Sheets Script - Loading a File from a Drive Folder

Question

I have a spreadsheet that is a template. I have a submit button that saves the data in a file (by date and customer name) into a Google Drive Folder.

I want to edit an already submitted template, so I created a dropdown menu that pulls the file name from the folder using this code:

function onOpen() {
list_all_files_inside_one_folder_without_subfolders() }


function list_all_files_inside_one_folder_without_subfolders() {
  var sh = SpreadsheetApp.getActiveSheet();
  var folder = DriveApp.getFolderById('0B8xnkPYxGFbUMktOWm14TVA3Yjg'); // I change the folder ID  here 
  var list = [];
  list.push(['Name']); //,'ID','Size']);
  var files = folder.getFiles();
  while (files.hasNext()){
    file = files.next();
    var row = []
    row.push(file.getName()) //,file.getId(),file.getSize())
    list.push(row);
  }
   sh.getRange(2,2,list.length,list[0].length).setValues(list);
}

function onEdit(e) {


  openFile() }



function openFile(e) {
  var sheet = "Sheet1"; // Please set the sheet with the dropdown list.
  var range = "A1"; // Please set the range of dropdown list.
  if (e.source.getSheetName() == sheet && e.range.getA1Notation() == range) {
    var file = DriveApp.getFilesByName(e.value);
    if (file.hasNext()) {
      var f = file.next();
      var url = f.getUrl();
      var script = "<script>window.open('" + url + "', '_blank').focus();google.script.host.close()</script>";
  var html = HtmlService.createHtmlOutput(script);
  SpreadsheetApp.getUi().showModalDialog(html, 'Open ' + f.getName());
    }
  }
}

I then created a Dropdown of a list of these files by name. I want to create an OnEdit Script that can open a file (by selecting from the Dropdown menu the name of the file).

Any pointers on how to pursue this?

Answer 1

• You have already had the dropdown list.
• When you select one of filename from the dropdown list, you want to open the file as a new tab of the browser.

I understood about your question like above. How about this sample script? The flow of this sample script is as follows.

1. Select a filename at the dropdown list.
2. When the filename is existing, open a dialog for running Javascript and the selected file is opened to the new tab of the browser.
3. When the new tab is opened, the focus is moved to the new tab.
4. After the new tab was opened, the dialog of Spreadsheet is closed.

Sample script:

When you use this sample script, please do the following flow.

1. Please copy and paste the following sample script to the bound script of the Spreadsheet which has the dropdown list.
2. This script uses OnEdit event. So please install OnEdit trigger to openFile().
3. Please modify Sheet1 of var sheet = "Sheet1". This sample supposes that the dropdown list is put in "Sheet1".
4. Please modify A1 of var range = "A1". This sample supposes that the dropdown list is put in a cell "A1".

function openFile(e) {
  var sheet = "Sheet1"; // Please set the sheet with the dropdown list.
  var range = "A1"; // Please set the range of dropdown list.
  if (e.source.getSheetName() == sheet && e.range.getA1Notation() == range) {
    var file = DriveApp.getFilesByName(e.value);
    if (file.hasNext()) {
      var f = file.next();
      var url = f.getUrl();
      var script = "<script>window.open('" + url + "', '_blank').focus();google.script.host.close()</script>";
      var html = HtmlService.createHtmlOutput(script);
      SpreadsheetApp.getUi().showModalDialog(html, 'Open ' + f.getName());
    }
  }
}

Note:

• After you set above, when you select one of filenames at the dropdown list, the file is opened as new tab.
• This script is a simple sample. So please modify it for your situation.
• In this sample script, when there are several files with the same filename, the first searched file is opened. If you want to modify it, please modify the script for your situation.

References:

• Installable Triggers
• Class Ui

Edit:

The reason of the issue is function onEdit(e) {openFile()}. e of onEdit(e) is not given to openFile(). By this, such error occurs. So please install the OnEdit trigger to the function openFile(). So please do as follows.

1. Remove the function of onEdit().
2. Install openFile() as the OnEdit trigger.
   • By this, when you select the filename at the dropdown list, openFile() is automatically run by the installed trigger. At that time, e of the event object is given.

How to install OnEdit trigger:

1. Open the script editor.
2. Edit -> Current project's triggers.
3. Click "Add Trigger".
4. Set "openFile" for "Choose which function to run".
5. Set "From spreadsheet" for "Select event source".
6. Set "On edit" for "Select event type".