Upload Image in google sheet cell using google apps script?-CodePudding

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"  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

CodePudding user response：

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);
}

Issue and workaround:

Google Apps Script side: Code.gs

HTML & Javascript side: Index.html

Google Apps Script side: `Code.gs`

HTML & Javascript side: `Index.html`