CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
anna liem
anna liem

Reputation: 11

Need Google App Script to show pop up dialog for insert image in cell

I need code to do as follow: When user click add image button on my sheet, there will be pop up a dialog that allow user to browse for their image file. After they choose their image file, the image will be placed in a cell next to the button. The image cell location is fixed.

Please see the illustration for better reference. The Interface Illustration

There are codes to insert image to cell using google app script, but it is static. I do not want my user have to modify code.

So is there a code to bring the google already had pop up dialog to choose image?

I have tried to record macro, but it only detects my cell selection.

In VBA, the code is as follow:

Option Explicit
Private Sub CommandButton1_Click()

  Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
  Dim fd As Office.FileDialog

  Set fd = Application.FileDialog(msoFileDialogFilePicker)

  With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls?"

    If .Show = True Then
      fileName = Dir(.SelectedItems(1))

    End If
  End With

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Workbooks.Open (fileName)
    
   End sub

Many thanks in advance for your response.

Upvotes: -2

Views: 57

Answers (1)

Patsytalk
Patsytalk

Reputation: 913

Insert image inside Google Spreadsheet cell using Google Apps Script

After searching for the applicable link that can view images inside a cell you may try this script using the converted link which provides direct access to the image. https://lh3.googleusercontent.com/d/FILE_ID see this thread.

for the functions uploadImage1 to 3 where you can use this to open a dialog where you can upload an image. the FileUpload.html is the dialog where you can upload the image from your file other functions is to handle the convertion of the link and inserted inside the cell.

Script Used

Code.gs

function uploadImage1() {
  var html = HtmlService.createHtmlOutputFromFile('FileUpload')
    .setWidth(400)
    .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'Upload Image 1');
  PropertiesService.getScriptProperties().setProperty('targetCell', 'B2');
}

function uploadImage2() {
  var html = HtmlService.createHtmlOutputFromFile('FileUpload')
    .setWidth(400)
    .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'Upload Image 2');
  PropertiesService.getScriptProperties().setProperty('targetCell', 'B3');
}

function uploadImage3() {
  var html = HtmlService.createHtmlOutputFromFile('FileUpload')
    .setWidth(400)
    .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'Upload Image 3');

  PropertiesService.getScriptProperties().setProperty('targetCell', 'B4');
}

function uploadToDrive(base64, filename) {
  var folder = DriveApp.getFolderById("Folder_ID");
  var content = Utilities.base64Decode(base64.split(',')[1]);
  var blob = Utilities.newBlob(content, 'image/png', filename);
  var file = folder.createFile(blob);

  return "https://lh3.googleusercontent.com/d/" + file.getId() + "=s500";
}


function insertImageUrl(url) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var targetCell = PropertiesService.getScriptProperties().getProperty('targetCell');

  if (targetCell) {
    var cell = sheet.getRange(targetCell);
    cell.setFormula(`=IMAGE("${url}")`);
  } else {
    SpreadsheetApp.getUi().alert("Target cell not set. Please try again.");
  }
}

FileUpload.html

<!DOCTYPE html>
<html>

<head>
  <script>
    function uploadImage() {
        var fileInput = document.getElementById("fileInput");
        var file = fileInput.files[0];

        if (!file) {
          alert("Please select a file.");
          return;
        }

        var reader = new FileReader();
        reader.onloadend = function () {
          var base64String = reader.result;
          google.script.run
            .withSuccessHandler(function (url) {
              document.getElementById("status").innerHTML = 
                "<p style='color: green; font-family: Century Gothic;'>Image uploaded successfully!</p>";
              google.script.run.insertImageUrl(url);

              setTimeout(function() {
                google.script.host.close();
              }, 2000);
            })
            .uploadToDrive(base64String, file.name);
        };

        reader.readAsDataURL(file);
      }
  </script>
</head>

<body>
  <br>
  <input type="file" id="fileInput">
  <button onclick="uploadImage()">Upload</button>
  <div id="status"></div>
</body>

</html>

Sample Output

From this - I used 3 Columns so that you can see the buttons and uploaded Image

Sample

To this - Upload Successfully

sample2

sample3

Also it directly uploaded into your Google drive

sample 4

Note: I did not use the markdown table to show that the image can be viewed inside the cell.

References

Upvotes: 0

Related Questions