I manage to get attach and upload on google sheet, not working....i know its either the script or the onclick= but stuck any help will be appreicated

Question

I created a Google sheet for my team and we needed to attach files on it. I found a code which i thought worked well. I haven't coded for sometime nor used google script before. But from looking at it not working it must be the script or the onclick= on submit?

The google drive ID is fine , and i checked for all the <> and ;.....but cant seem get to my head around it to upload the file and run the functions.....

I am using the code from https://blackstormdesign.com/how-to-upload-a-file-to-google-drive-through-google-sheets/

Script: Upload_files

Folder_Id = '1dsUl22eeCPSE7-Lpa7VGkR3Jx5TaLc5z'
function onOpen(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var menuEntries = [];
  menuEntries.push({name: "File", functionName: "doGet"});
  ss.addMenu("Attach", menuEntries);
}
function upload(obj) {
  var file = DriveApp.getFolderById(Folder_Id).createFile(obj.upload);
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var File_name = file.getName()
  var value = 'hyperlink("' + file.getUrl() + '";"' + File_name + '")'
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var selection = activeSheet.getSelection();
  var cell = selection.getCurrentCell()
  cell.setFormula(value)
  return {
    fileId: file.getId(),
    mimeType: file.getMimeType(),
    fileName: file.getName(),
  };
}
function doGet(e) {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var selection = activeSheet.getSelection();
  var cell = selection.getCurrentCell();
  var html = HtmlService.createHtmlOutputFromFile('upload');
  SpreadsheetApp.getUi().showModalDialog(html, 'Upload File');
}


html file with it


<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<form> <!-- Modified -->
<div id="progress" ></div>
<input type="file" name="upload" id="file">
<input type="button" value="Submit" class="action" onclick="form_data(this.parentNode)" >
<input type="button" value="Close" onclick="google.script.host.close()" />
</form>
<script>
function form_data(obj){ // Modified
google.script.run.withSuccessHandler(closeIt).upload(obj);
};
function closeIt(e){ // Modified
console.log(e);
google.script.host.close();
};
</script>
</body>
</html>

once again thank you so much!

What is the error that your getting and what line in what function is causing the error? — Cooper, May 03 '21 at 20:25
I am wondering the same thing too....I added the script on the google sheet scripts....at the start since its been 5 years since i touched coding there was nothing showing up. I spent the whole day solving , rereading with my best ability then got it to run on google sheet. The current code runs and the "attach button" shows up on the menu. When I click it it triggers perfectly fine. But the issue is when I try to upload any doc , file , img and press submit. I don't think the onclick="form_data(this.parentNode) is working or making the function initiate. — Sizjal Grg, May 03 '21 at 20:52
@TJ Execution started and then Execution Completed......no error — Sizjal Grg, May 03 '21 at 21:36
@MetaMan The code runs fine when I press run no err. I refresh the sheet and the "Attach" button is showing now as I wanted. When I press the Attach button another pop up comes allowing me to upload files, doc , img from my desktop. The problem is that I can't seem to confirm/upload the file when I press "Submit" button. It's suppose trigger onclick="form_data(this.parentNode)" <------ however I don't think this is working either its not initiated , or the function is wrong on my script. I believe the (obj) data is passed to the function and run function upload(obj) . Sorry i might be wrong... — Sizjal Grg, May 03 '21 at 21:42
I believe that the code you're using was developed on Rhino and will not run on V8. See if [this](https://stackoverflow.com/questions/60742695/moving-google-apps-script-to-v8-file-upload-stopped-working-from-sidebar) will solve your problem. — Cooper, May 04 '21 at 01:12

Cooper · Accepted Answer · 2021-05-04T15:21:30.303

Sorry I was having trouble understanding the issue because I was looking at a piece of old code that was still running under Rhino. I know... stupid but anyway I finally looked at the old editor and saw the error or my ways.

This is what I ended up with after a lot of screwing around.

HTML: (file: ah1.html)

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div id="formdiv">
      <form id="myForm">
         <input type="file" name="myfile" />
         <input type="button" value="UpLoad" id="upl" />   
      </form>
      <input type="button" value="Close" id="close" />
    </div>
    <script>
      window.onload = function() {
        document.getElementById("upl").addEventListener('click', processFormJS,false);
        document.getElementById("close").addEventListener('click',function(){google.script.host.close();},false)
      }// I added this because I want to start using listeners
      function processFormJS() {
        const formData = document.getElementById("myForm");
        console.log(formData);
        const file = formData.myfile.files[0];
        const fr = new FileReader();//this is the key change to this side
          fr.onload = function(e) {
            const obj = {filename: file.name,mimeType:file.type,bytes:[...new Int8Array(e.target.result)]};
            google.script.run
            .withSuccessHandler(function(){google.script.host.close();})
            .processFormX(obj);
            console.log(obj);
          };
          fr.readAsArrayBuffer(file);
      }
      console.log('MyCode');
    </script>
  </body>
</html>

GS:

function processFormX(obj) {
  const fileBlob = Utilities.newBlob(obj.bytes, obj.mimeType, obj.filename);//this is the key change to this side
  const folder = DriveApp.getFolderById('folderId');/edit folderid
  folder.createFile(fileBlob);
  return;
}

function launchmydialog() {
  var ss = SpreadsheetApp.getActive();
  var html = HtmlService.createHtmlOutputFromFile('ah1');//edit filename
  SpreadsheetApp.getUi().showModelessDialog(html, 'Upload File');
}

Oh wow thanks for this.......I replaced the HTML file and the GS file with these code but unfortunately it's not working. Did i do something wrong to get it work? Thanks so much — Sizjal Grg, May 04 '21 at 09:27
When I try executing the code I get this error now: TypeError: Cannot read property 'bytes' of undefined processFormX @ Script1.gs:4..... The obj variable not parsing? — Sizjal Grg, May 04 '21 at 09:30
I'll look into it today. I'm going to be updating all of my upload questions over the next couple of days. But I just loaded the entire script into my account and after updating the folderId it worked. — Cooper, May 04 '21 at 15:22

score 0 · Answer 2 · answered May 04 '21 at 13:35

0

Seems like the its due to the new version of Google Script V8

https://developers.google.com/apps-script/guides/v8-runtime/migration

many things are not the same anymore......can't use obj

answered May 04 '21 at 13:35

Sizjal Grg

3
1

I manage to get attach and upload on google sheet, not working....i know its either the script or the onclick= but stuck any help will be appreicated

2 Answers2