Send HTML table data back to Google Sheets using Scripts

Question

I'm using an example from this website

I'm trying to add values from HTML table to a1 cells of my active spreadsheet. I have a feeling, that the function runsies(values) is not even called.

The problem is, that I have got nothing in logs. The ideal solution will be result of user choice in separated cells. For example: Orange 1, Blue 2 and etc in A1, A2 and etc

What I'm doing wrong?

Code.js

//--GLOBALS--

var ui = SpreadsheetApp.getUi();


function onOpen(e){
  // Create menu options
  ui.createAddonMenu()
    .addSubMenu(ui.createMenu("Admin")
      .addItem("Test","test"))
    .addToUi();
};

function test(){ 
  //Call the HTML file and set the width and height
  var html = HtmlService.createHtmlOutputFromFile("testUI")
    .setWidth(450)
    .setHeight(300);

  //Display the dialog
  var dialog = ui.showModalDialog(html, "Select the relevant module and unit");


};
//--GLOBALS--

var ui = SpreadsheetApp.getUi();


function onOpen(e){
  // Create menu options
  ui.createAddonMenu()
    .addSubMenu(ui.createMenu("Admin")
      .addItem("Test","test"))
    .addToUi();
};

function test(){ 
  //Call the HTML file and set the width and height
  var html = HtmlService.createHtmlOutputFromFile("testUI")
    .setWidth(450)
    .setHeight(300);

  //Display the dialog
  var dialog = ui.showModalDialog(html, "Select the relevant module and unit");

};

function runsies(values){
  //Display the values submitted from the dialog box in the Logger and in the A1 cell of active sheet. 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.getRange(1,1).setValue([values])
  Logger.log(values);
};

HTML

<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <body>
    <div>

        <table>
          <col width="60">
          <col width="50">
          <col width="50">
          <col width="50">
          <col width="50">
          <col width="50">
          <col width="50">
          <col width="50">
          <col width="50">
          <col width="50">
          <tr>
            <th></th><th><strong>Unit:</strong></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th>
          </tr>
          <tr>
            <th><strong>Module</strong></th>
            <th><strong>n/a</strong></th>
            <th><strong>1</strong></th>
            <th><strong>2</strong></th>
            <th><strong>3</strong></th>
            <th><strong>4</strong></th>
            <th><strong>5</strong></th>
            <th><strong>6</strong></th>
            <th><strong>7</strong></th>
            <th><strong>8</strong></th>
          </tr>
          <tr>
            <td>Orange </td>
            <td><input type="radio" name="orange" value="na" checked></td>
            <td><input type="radio" name="orange" value="1"></td>
            <td><input type="radio" name="orange" value="2"></td>
            <td><input type="radio" name="orange" value="3"></td>
            <td><input type="radio" name="orange" value="4"></td>
            <td><input type="radio" name="orange" value="5"></td>
            <td><input type="radio" name="orange" value="6"></td>
            <td><input type="radio" name="orange" value="7"></td>
            <td><input type="radio" name="orange" value="8"></td>
          </tr>
          <tr>
            <td>Blue </td>
            <td><input type="radio" name="blue" value="na" checked></td>
            <td><input type="radio" name="blue" value="1"></td>
            <td><input type="radio" name="blue" value="2"></td>
            <td><input type="radio" name="blue" value="3"></td>
            <td><input type="radio" name="blue" value="4"></td>
            <td><input type="radio" name="blue" value="5"></td>
            <td><input type="radio" name="blue" value="6"></td>
            <td><input type="radio" name="blue" value="7"></td>
            <td><input type="radio" name="blue" value="8"></td>
          </tr>
          <tr>
            <td>Green </td>
            <td><input type="radio" name="green" value="na" checked></td>
            <td><input type="radio" name="green" value="1"></td>
            <td><input type="radio" name="green" value="2"></td>
            <td><input type="radio" name="green" value="3"></td>
            <td><input type="radio" name="green" value="4"></td>
            <td><input type="radio" name="green" value="5"></td>
            <td><input type="radio" name="green" value="6"></td>
            <td><input type="radio" name="green" value="7"></td>
            <td><input type="radio" name="green" value="8"></td>
          </tr>
          <tr>
            <td>Purple </td>
            <td><input type="radio" name="purple" value="na" checked></td>
            <td><input type="radio" name="purple" value="1"></td>
            <td><input type="radio" name="purple" value="2"></td>
            <td><input type="radio" name="purple" value="3"></td>
            <td><input type="radio" name="purple" value="4"></td>
            <td><input type="radio" name="purple" value="5"></td>
            <td><input type="radio" name="purple" value="6"></td>
            <td><input type="radio" name="purple" value="7"></td>
            <td><input type="radio" name="purple" value="8"></td> 
          </tr>
        </table>
        <input type="submit" value="Submit" class="action" onclick="form_data()" >
        <input type="button" value="Close" onclick="google.script.host.close()" />


    </div>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
    </script>
    <script>
      function form_data(){
        var values = [{
          "orange":$("input[name=orange]:checked").val(),
          "blue":$("input[name=blue]:checked").val(),
          "green":$("input[name=green]:checked").val(),
          "purple":$("input[name=purple]:checked").val()
        }];
        google.script.run.withSuccessHandler(closeIt()).runsies(values);
        closeIt()
      };
      function closeIt(){
        google.script.host.close()
      };

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

=======================================================

UPDATE

Based on @Cooper answer. I changed This google.script.run.withSuccessHandler(closeIt()).runsies(values); to this google.script.run.withSuccessHandler(closeIt).runsies(values); This helped and now I'm getting {orange=1, purple=1, blue=1, green=1} in A1

with that code sheet.getRange(1,1).setValue([values])

How to separate this data in different cells?

For example: In cell A1 get 1 not {orange=1, purple=1, blue=1, green=1}

I think, I have the dictionary type data (in python its called that way) with this code.

var values = {
          "orange":$("input[name=orange]:checked").val(),
          "blue":$("input[name=blue]:checked").val(),
          "green":$("input[name=green]:checked").val(),
          "purple":$("input[name=purple]:checked").val()
        };

How to obtain only orange value?

Dictionary code had a mistake:

var values = {
          "orange":$("input[name=orange]:checked").val(),
          "blue":$("input[name=blue]:checked").val(),
          "green":$("input[name=green]:checked").val(),
          "purple":$("input[name=purple]:checked").val()
        };

I was looking for that sheet.getRange(1,1).setValue([values["orange"]])

Do you actually have two functions named onOpen and and test(). That's a problem every function must have a unique name. — Cooper
– Cooper, Commented Apr 16, 2020 at 20:55
Take a look at this example: stackoverflow.com/a/54261174/7215091 — Cooper
– Cooper, Commented Apr 16, 2020 at 20:59
@Cooper thx, but that didnt change a thing. Can u look if the function 'runsies(values)'? Is that function even called? — Andry
– Andry, Commented Apr 16, 2020 at 21:28
This google.script.run.withSuccessHandler(closeIt()).runsies(values); should be google.script.run.withSuccessHandler(closeIt).runsies(values); and by the way google.script.host.close() doesn't work on webapps. — Cooper
– Cooper, Commented Apr 16, 2020 at 21:37
You would probably have been able to see that error in the Browser console. — Cooper
– Cooper, Commented Apr 16, 2020 at 21:39

Cooper · Accepted Answer · 2020-04-16 23:28:51Z

2

try this:

function runsies(values){
  var sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.getRange(1,1,4,1).setValues([[values.orange],[values.blue],[values.purple],[values.green]]);
  Logger.log(values);

};

answered Apr 16, 2020 at 23:28

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Send HTML table data back to Google Sheets using Scripts

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related