How to Browse and Select Local Files in Google Sheets - Alternative to Excel's File Dialog

Other Questions Google Sheets
1

I’m working on migrating some Excel functionality to Google Sheets and I’m stuck on one particular feature. In Excel, I have this VBA macro that lets me double-click on certain cells to open a file browser dialog. The user can select multiple files and then the full file paths get inserted into the cell.

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var clickedCell = e.range;
  
  if (clickedCell.getColumn() == 25 && clickedCell.getRow() >= 5) {
    var selectedFiles = openFileBrowser();
    var pathList = "";
    
    if (selectedFiles && selectedFiles.length > 0) {
      for (var j = 0; j < selectedFiles.length; j++) {
        pathList += selectedFiles[j] + "\n";
      }
      clickedCell.setValue(pathList);
    } else {
      Browser.msgBox("No files chosen.");
    }
  }
}

My workflow involves clients sending me product images that I need to process and optimize before uploading them. I just need to grab the file names from my local drive and put them in the spreadsheet. Is there a way to replicate this file selection behavior in Google Sheets using Apps Script or any other method?

2

Google Sheets runs in the cloud, so Apps Script can’t directly access your local files. Web apps aren’t allowed to browse local directories without the user explicitly choosing files. Here’s what works instead: create an HTML dialog with an input element using the multiple attribute. Users can select multiple files at once, and JavaScript can grab just the filenames (not full paths). Use google.script.run to send those filenames back to your Apps Script function. I’ve built something like this for batch processing. It won’t give you exact file paths like Excel VBA does, but you’ll get the filenames, which should work for tracking your image processing. Different user experience, but it gets the job done.

3

I encountered a similar challenge when transitioning from Excel to Google Sheets for managing assets. Due to the browser’s security settings, Google Sheets does not have direct access to your file system. A solution that worked for me was utilizing Google Drive integration. By leveraging the Drive API within Apps Script, I was able to pull specific files from cloud folders and present them in a custom dialog for user selection. Although this necessitates uploading images to Drive first, it streamlines the workflow. Alternatively, you could also create an HTML form that includes a file input for selecting multiple files. While you won’t receive complete file paths, you can obtain filenames and some basic metadata, which should suffice for your image processing tasks.