0

In Excel I can run a VBA script with application.getopenfilename and am able to place the filepath of the items selected into that cell. I'm trying to convert my VBA 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

  If Not Application.Intersect(Target, Range("AG4:AG910")) Is Nothing Then
  Dim FileNames As Variant
  Dim Msg As String
  Dim i As Integer
  FileNames = Application.GetOpenFilename(MultiSelect:=True)
  If IsArray(FileNames) Then
      For i = LBound(FileNames) To UBound(FileNames)
          Msg = Msg & FileNames(i) & vbNewLine
      Next i
      Target = Msg
          Else
      MsgBox "No files were selected."
End If

End If

Basically I want to be be able to select images and then get the filenames. I only need the filenames because the clients send me the product pics which I have to then optimize before uploading.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
Brian Ellis
  • 2,271
  • 2
  • 17
  • 24
  • Yeah, all I see for info is the GetName function in the Google Scripts Docs, but this only gives me a name reference for a sheet. Unfortunately I need the filename extracted from my local computer and placed into the Google Sheets active cell. I did see this http://stackoverflow.com/questions/21593249/uploading-images-to-a-google-spreadsheet-using-apps-script but it uses an outside form so if theres a way to link a form option to a cell that'd be fine also – Brian Ellis Mar 04 '15 at 19:16
  • 1
    So, if you had a file picker, that you could use to select a file from your hard drive, and then get the file name without uploading the file, would that work for you? (And have the file name get put into the Spreadsheet) – Alan Wells Mar 04 '15 at 20:03
  • That's exactly what I'm trying to do. In Excel the GetOpenFilename allows the file dialog box to execute and then the filename that is selected is placed in the cell – Brian Ellis Mar 04 '15 at 20:08
  • 1
    You'd need a custom menu, that then opened a Dialog Box or Sidebar with HTML in it. Then the HTML would have a file picker. That would get the name, and insert it into your Spreadsheet. If that sounds okay, I can give an answer with how to do that. – Alan Wells Mar 04 '15 at 20:20

1 Answers1

1

This explanation shows:

  • Automatic creation of Custom Menu Item
  • Code to open Dialog box
  • HTML and <script> tag in the HTML for the dialog box
  • Code to put the result into the spreadsheet

Code.gs - onOpen() - Create Custom Menu

function onOpen() {

  SpreadsheetApp.getUi()
      .createMenu('Custom Menu')
      .addItem('Show Upload Dialog', 'showUploadBox')
      .addToUi();
};

gs Code - Open Dialog

function showUploadBox() {

  var htmlOutput = HtmlService.createHtmlOutputFromFile('Dialog')
     .setWidth(500)
     .setHeight(500);

  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Title of Dialog');
};

Dialog Upload.html

Create a new file with this HTML

<div id="formDiv">

<form id="myForm">

    <input id="fileName" name="picToLoad" type="file" />
    <br>
    <br/>
    <input type="button" value="Submit" onclick="fncGetFileName()" />
  <br>
  <br>
  <br>
    <input id="idBtnClose" type="button" value="Close" style="display: none" onclick="google.script.host.close()" />
</form>
</div>

<br>
<br>

<div id="status" style="display: none">
  <!-- div will be filled with innerHTML after form submission. -->
  Working. Please wait...
</div>


<script>

function fncGetFileName(frmData) {
  console.log('fncGetFileName ran!');

  var theFileName = document.getElementById('fileName').value;
  theFileName = theFileName.slice(12);

  console.log('theFileName: ' + theFileName);

  document.getElementById('status').style.display = 'inline'; //Display msg

  google.script.run
    .withSuccessHandler(updateOutput)
    .processForm(theFileName)
};
  // Javascript function called by "submit" button handler,
  // to show results.

  function updateOutput() {

    var outputDiv = document.getElementById('status');
    outputDiv.innerHTML = "The File Name was Written!";

    document.getElementById('idBtnClose').style.display = 'inline'; //Display msg
  }

</script>

gs Code to Save File Name to Sheet

function processForm(argFileName) {
  Logger.log('argFileName: ' + argFileName);

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var theSheet = ss.getActiveSheet();

  var theRange = theSheet.getRange("B4");
  theRange.setValue(argFileName);
};
Alan Wells
  • 30,746
  • 15
  • 104
  • 152