Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-apps-script-addon

Send HTML table data back to Google Sheets using Scripts

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>

Htm table look like that

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

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

Solution

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

    };