0

I am looking to upload an image into google sheet cell using a google apps script, I found a script that uploads image into Google Drive folder and then gets the image url into sheet that can be manipulated to get the image:

Here is the first function:

Code.gs

function addImage() { 

  var filename = 'Row';
  var htmlTemp = HtmlService.createTemplateFromFile('Index');
  htmlTemp.fName = filename;
  htmlTemp.position = 2;
  var html = htmlTemp.evaluate().setHeight(96).setWidth(415);
  var ui = SpreadsheetApp.getUi();
  ui.showModalDialog(html, 'Upload');
}

Following is the return function:

Code.gs

function upload(obj) {
  //Retrieve the input data of the Form object.
  var newFileName = obj.fname;
  var rowNum = obj.position;
  var blob = obj.file;

  var upFile = DriveApp.getFolderById('[folderid]').createFile(blob).setName(newFileName);
  var fileUrl = upFile.getUrl();

  var urlCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(rowNum,5);
  urlCell.setValue('=HYPERLINK("' + fileUrl + '","View image")');

}

This is the html part:

Index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_center">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
        <script src="https://code.jquery.com/jquery-3.4.1.js" integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU=" crossorigin="anonymous"></script>
  </head>
  <body>
  <form id="myForm">
      Please upload image below.<br /><br />
    <input type="hidden" name="fname" id="fname" value="<?= fName ?>"/>
    <input type="hidden" name="position" id="position" value="<?= position ?>"/>
    <input type="file" name="file" id="file" accept="image/jpeg,.pdf" />
    <input type="button" value="Submit" class="action" onclick="formData(this.parentNode)" />
    <input type="button" value="Close" onclick="google.script.host.close()" />
  </form>
  <script>
  //Disable the default submit action  using “func1”
   window.onload=func1;
   function func1() {
      document.getElementById('myForm').addEventListener('submit', function(event) {
            event.preventDefault();
          });  
   }

   function formData(obj){
       google.script.run.withSuccessHandler(closeIt).upload(obj);
   }   

  function closeIt(e){
      console.log(e);
      google.script.host.close();
  };   

    </script>
</body>
</html>

When I ran the addImage() function, a dialog box popped up in which I uploaded a jpeg image, but when I clicked on submit button, it did not do anything and stuck there, any help would be much appreciated. Thanks

1 Answers1

1

Issue and workaround:

From [Fixed] Google Apps Script Web App HTML form file-input fields not in blob compatible format, in the current stage, when Web Apps is used, the file object in the form object can be parsed by google.script.run. But, unfortunately, it seems that when a dialog and sidebar are used, this cannot be parsed. So, in the current stage, as the current workaround, it is required to parse the file object on the Javascript side. When this is reflected in your script, how about the following modification?

Google Apps Script side: Code.gs

Please `upload as follows.

function upload(obj, rowNum) {
  var newFileName = obj[2];
  var blob = Utilities.newBlob(...obj);
  var upFile = DriveApp.getFolderById('[folderid]').createFile(blob).setName(newFileName);
  var fileUrl = upFile.getUrl();
  var urlCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(rowNum, 5);
  urlCell.setValue('=HYPERLINK("' + fileUrl + '","View image")');
  return "Done.";
}

HTML & Javascript side: Index.html

Please formData as follows.

function formData(obj) {
  const file = obj.file.files[0];
  const fr = new FileReader();
  fr.readAsArrayBuffer(file);
  fr.onload = f =>
    google.script.run.withSuccessHandler(closeIt).upload([[...new Int8Array(f.target.result)], file.type, obj.fname.value], obj.position.value);
}
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • thank you for the answer, it works perfectly, can we operate this from the `Google sheets app` also? I tried but it did not work. –  Oct 23 '22 at 05:53
  • @Roomi Thank you for replying. I'm glad your issue was resolved. About your new question of `can we operate this from the Google sheets app also? I tried but it did not work`, I would like to support you. But, I have to apologize for my poor English skill. Unfortunately, I cannot understand your new question. Can I ask you the detail of your new question? By this, I would like to try to understand it. – Tanaike Oct 23 '22 at 07:41
  • yeah, thank you. the script upload image in browser alright but I have google sheets app in android phone, so I want to operate this script in google sheets app, so that dialog box pop up in google sheets app and I would upload the image there –  Oct 23 '22 at 09:30
  • @Roomi Thank you for replying. About `the script upload image in browser alright but I have google sheets app in android phone, so I want to operate this script in google sheets app, so that dialog box pop up in google sheets app and I would upload the image there`, in this case, I would like to recommend to post it a new question. If you can cooperate to resolve your new issue, I'm glad. Can you cooperate to resolve your new question? – Tanaike Oct 23 '22 at 11:09