Google app script: upload a csv file from html service

Question

I'm trying to upload a csv file from an html service in google sheets, and after some research I found some code which seemed to be working at the time:

html service call:

function importAnalysis() {
  var html = HtmlService.createHtmlOutputFromFile('Import')
      .setWidth(1524)
      .setHeight(800);
  SpreadsheetApp.getUi()
      .showModalDialog(html, 'Import d\'analyse');
}

html template:

<!DOCTYPE html>
  <html>
    <body>
      <form>
        <input type="file" name="analysisCsv" accept=".csv">
        <input type="button" onclick="google.script.run.processForm(this.parentNode);">
      </form>
    </body>
  </html>

gs file (I commented further code to isolate the source of the problem):

function processForm(form) {
  let fileBlob=form.analysisCsv;
//  sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Table_Analysis");
//  let lastRow = sheet.getLastRow();
//  let values = []
//  let rows = fileBlob.contents.split('\n');
//  for(let r=0; r<rows.length; ++r){
//    values.push( rows[r].split(';') );
//  }
//  for (var i = 0; i < values.length; i++) {
//    sheet.getRange(lastRow+i+1, 1, 1, values[i].length).setValues(new Array(values[i]));
//  }  
}

Problem is I get an error 400 on processForm function: "Failed to load resource: the server responded with a status of 400 ()"

Do you know what's wrong with my code, or another way to upload csv content in my sheet?

Could you try renaming the function name to something like receiveForm? — TheMaster
– TheMaster, Commented Feb 26, 2020 at 14:57
Does this answer your question? Uploading a file using an HtmlService form in Google Apps always causes "server error" and a stack trace — TheMaster
– TheMaster, Commented Feb 26, 2020 at 15:05
Try using something like google.script.run.proccessForm(this.parentNode) — Cooper
– Cooper, Commented Feb 26, 2020 at 15:17
Could you please try to use the STABLE version of Apps Script instead of V8 just in case that is the problem and let me know how did it go with that? To achieve this follow these steps: Open the Script Editor. In the top menu, select View > Show manifest file. In the files list, open appsscript.json. Replace "runtimeVersion": "V8" with "runtimeVersion": "STABLE" Save. — Mateo Randwolf
– Mateo Randwolf, Commented Feb 26, 2020 at 15:59
When I change "V8" for "STABLE", I get an error "Missing ; sign before instruction" and I can't track where it comes from, html doesn't show at all. — Raziel
– Raziel, Commented Feb 27, 2020 at 8:57

Raziel · Accepted Answer · 2020-03-02 10:15:25Z

1

So to sum it up, I had to force Legacy runtime: Select Run > Disable new Apps Script runtime powered by V8. Then edit all the V8 syntax:replace "let" by "var" mainly. Now it's working, thanks to all.

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

answered Mar 2, 2020 at 10:15

Raziel

578 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cooper · Accepted Answer · 2020-02-28 17:44:15Z

0

You can't provide doPost() with a parameter. From the documentation:

When a user visits an app or a program sends the app an HTTP GET request, Apps Script runs the function doGet(e). When a program sends the app an HTTP POST request, Apps Script runs doPost(e) instead. In both cases, the e argument represents an event parameter that can contain information about any request parameters. The structure of the event object is shown in the table below: And here's a link to that

As I stated in my comment above a simple way of passing a form to the server is:

google.script.run.proccessForm(this.parentNode)

And this is explained here and partly here

Well I finally ran your code myself and sure enough it's not working in V8.

So I moved to the old version and this is the code I used:

GS:

function runThree() {
  var html = HtmlService.createHtmlOutputFromFile('ah3').setWidth(400).setHeight(400);
  SpreadsheetApp.getUi().showModelessDialog(html, 'Title');
}

function processForm(form) {
  Logger.log(JSON.stringify(form));
  var fileBlob=form.analysisCsv;
  var folder=DriveApp.getFolderById(getGlobal('TestFolderId'));
  var file=folder.createFile(fileBlob);
  var csv=Utilities.parseCsv(file.getBlob().getDataAsString());
  var sh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet10");
  var rg=sh.getRange(sh.getLastRow()+1,1,csv.length,csv[0].length).setValues(csv);
  return "I've got it";
}

HTML:

<!DOCTYPE html>
  <html>
    <head>
    <base target="_top">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    </head>
    <body>
      <form>
        <input type="file" name="analysisCsv" accept=".csv">
        <input type="button" value="Save" onclick="intermediate(this.parentNode);">
        <div id="msg"></div>
      </form>
      <script>
        function intermediate(obj) {
          google.script.run
          .withSuccessHandler(function(msg){$('#msg').html(msg);google.script.host.close();})
          .processForm(obj);
        }
        console.log('My Code');
      </script>
    </body>
  </html>

I just created a csv file and upload to a folder and then using Utilites.parseCsv() which converts it to a 2D array that can be inserted into a spreadsheet with setValues();

edited Feb 28, 2020 at 17:44

answered Feb 26, 2020 at 18:59

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

8 Comments

Raziel Over a year ago

Thank you, I changed the function name but the error is still there.

Cooper Over a year ago

Here's an example you can take a look at: stackoverflow.com/a/57581534/7215091

Raziel Over a year ago

It's basically the same code, except the server function is called in script instead of body. I tried it but still got the same error.

Cooper Over a year ago

Just curious but why did you comment out your code?

Raziel Over a year ago

I just wanted to be sure the error wasn't caused by further code, but it happens on line 1 (function call).

|

abernier · Accepted Answer · 2020-05-30 10:44:20Z

Here is my solution using client-side FileReader API:

code.gs:

function doSomething(csvstr) {
  Logger.log('doSomething', csvstr);

  const csvarr = Utilities.parseCsv(csvstr);

  return csvarr; // now an array !
}

index.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

  </head>
  <body>
    <input type="file">

    <script>
    const $input = document.querySelector('input')

    $input.onchange = function (e) {
      // File
      const file = e.target.files[0]

      //
      // FileReader
      //
      // see: https://web.dev/read-files/#read-content
      //

      const reader = new FileReader();

      reader.onerror = event => {
        console.error('error while reading CSV file', event)
        reader.abort()
      }

      reader.onload = event => {
        const content = event.target.result // result
        console.log(content)

        // https://developers.google.com/apps-script/guides/html/reference/run
        google.script.run
          .withFailureHandler(err => {
            console.error('oh noes', err)
          })
          .withSuccessHandler(val => {
            console.log('yay!', val)

            google.script.host.close() // https://developers.google.com/apps-script/guides/html/reference/host#close
          })
          .doSomething(content)
      }
      reader.readAsText(file);
    }
    </script>
  </body>
</html>

Collectives™ on Stack Overflow

Google app script: upload a csv file from html service

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related