Use data from HTML Interface in Google Sheets

Question

I would like to create a HTML File / JS File within Google Apps Script that allows the user to input some data and then the data should be processed.

I created already a basic HTML Interface however I dont quite get how I make this data usable on submit in the further Google Script. Can someone explain how to continue from here?

function doGet(htmlfile, title) {
var html = HtmlService.createHtmlOutputFromFile(htmlfile);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .showModalDialog(html, title);
}

function run_html_interface(){
doGet("Interface","Adding item to a themepage category");
}

function success_input(form_output){
  SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/12WYgdCpaczfmrEI_o-s75ae9Zf8fB4AURZScScPdTvw/edit#gid=0").getSheetByName("Input").getRange("D5").setValue("Hello")
  Logger.log(form_output)
  var category = form_output.cat3;
  Logger.log(category)
}

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


      function handleFormSubmit(formObject) {
        google.script.run.withSuccessHandler(google.script.host.close()).success_input(formObject);
      }    
      
    </script>
  </head>
  
  <body>
    <form id="myForm" onsubmit="event.preventDefault(); handleFormSubmit(this)">
    <div>
      <label for="cat3">Category Name</label>
      <input type="text" placeholder="Category Name (e.g. Nie wieder schleppen)" value="" id="cat3">
    </div>
    <div>
      <label for="articleid">Article ID</label>
      <input type="text" placeholder="Article ID" value="" id="articleid">
    </div>
    <div>
      <label for="position">Position to set the Article ID</label>
      <input type="text" placeholder="Position Number (e.g. 1,2,3,..)" value="" id="position">
    </div>
    <input class="btn btn-light" type="submit" value="Submit" />
    </form>
  </body>
  
</html>

score 1 · Answer 1 · answered Mar 29 '21 at 10:20

From what I understand you want to forward data from HTML to Google Script. Google has documentation for this exact situation: https://developers.google.com/apps-script/guides/html/communication#index.html_4

Summarizing that documentation, you can call the function in your Google Script after the success or failure handler, so inside your HTML section it becomes:

google.script.run.withSuccessHandler(google.script.host.close).processFunction(formObject)

And later inside your Google Script you can access the fields using the ID:

function processFunction(formObject) {
    if (formObject.cat3 == "Hi") {
        ....
    }
}

Hi Jack, I updated the code so far, so if I understood correctly as soon as I submit, the function success_input should run and var category should be assigned the id "cat3" from the HTML input. I inserted this check that I'd like to get "Hello" pasted into the sheet as soon as the function "success_input" is started, however I dont see any changes. Am I doing something structurally wrong or why is that not working so far? Thanks in advance — Marcel Brauner, Mar 29 '21 at 11:33

Rubén · Answer 2 · 2021-03-29T23:05:33.837

In order to be able to use the form object, the input tags should have a name attribute.

I.E. replace

 <input type="text" placeholder="Category Name (e.g. Nie wieder schleppen)" value="" id="cat3">

by

 <input type="text" placeholder="Category Name (e.g. Nie wieder schleppen)" value="" id="cat3" name="cat3">

Please bear in mind that this, add a name attribute with the corresponde value, should also be done to all the data entry elements in the form.

doGet is a reserved function used when publishing a web application that will respond to HTTP GET calls, i.e by opening the web app URL from a web browser, so it's not a good practice to use this function name to call SpreadsheetApp.getUi() as it will throw an error.

Considering the above, replace

function doGet(htmlfile, title) {
var html = HtmlService.createHtmlOutputFromFile(htmlfile);
SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
  .showModalDialog(html, title);
}

function run_html_interface(){
doGet("Interface","Adding item to a themepage category");
}

by

function run_html_interface(){
  const htmlFile = "Interface";
  const title = "Adding item to a themepage category");
  var html = HtmlService.createHtmlOutputFromFile(htmlfile);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showModalDialog(html, title);
}

Thanks for clarifying. I tried around again for quite some time and also figured how to "debug" - it says processFunction is not defined. I both renamed it in the HTML and in the GS so there is guaranteed no spelling mistake what so ever. — Marcel Brauner, Mar 30 '21 at 08:45
The code in the question doesn't include `processFunction`. P.S. Follow up questions should be posted as new questions. — Rubén, Mar 30 '21 at 15:50

Use data from HTML Interface in Google Sheets

2 Answers2