How to send values from the spreadsheet to a user interface created using HtmlService

Question

Good Morning,

I created a html form to change values from a google spreadsheet. I was able to do the trick, but I want the form to be pre-filed with some values from the spreadsheet. I have no idea on how to do that. I don't have much experience in programming, so I would be very grateful if someone could help me.

Here is the code I'm using

GS file:

    function editDadosPessoais() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(),
        html = HtmlService.createHtmlOutputFromFile('DadosPessoais')
    ss.show(html);
}

HTML file:

 <html>
     <head>
      <meta content="Sheets" name="generator" />
       <style type="text/css"><!--br {mso-data-placement:same-cell;}
       .auto-style3 {
        font-family: Calibri;
       }
       .auto-style4 {
        font-family: Calibri;
        text-decoration: underline;
       }
       .auto-style5 {
        text-decoration: underline;
       }
       --></style>
      <meta content="pt-br" http-equiv="Content-Language" />
      <meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
      <title>Untitled 1</title>
     </head>
     <body>
      <form name="DadosPessoais" action="" method="post" style="width: 473px; height: 454px" onsubmit="formSubmit(this)">
        <strong><span class="auto-style4">NOME:</span></strong><span class="auto-style3"><strong>&nbsp;
        </strong></span><strong>
        <input class="auto-style3" name="Name1" id="Name1" style="width: 382px" type="text"/><br />
        <br class="auto-style3" />
        <span class="auto-style3"><span class="auto-style5">CÓDIGO DEPENDENTE:</span>
        </span><select class="auto-style3" name="Dep1" id="Dep1" style="width: 76px">
        <option>Titular</option>
        <option>Esposo(a)</option>
        <option>Filho(a)</option>
        <option>Outro(a)</option>
        </select><br />
        <br class="auto-style3" />
        <span class="auto-style3"><span class="auto-style5">TELEFONES:</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <span class="auto-style5">SITUAÇÃO: </span></span>
        <br class="auto-style3" />
        <input class="auto-style3" name="Tel1" id="Tel1" type="text"/><span class="auto-style3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        </span><select class="auto-style3" name="SitTel1" id="SitTel1" style="width: 97px">
        <option></option>
        <option>CORRETO</option>
        <option>ERRADO</option>
        </select><br class="auto-style3" />
        <input class="auto-style3" name="Tel2" id="Tel2" type="text"/><span class="auto-style3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        </span><select class="auto-style3" name="SitTel2" id="SitTel2" style="width: 97px">
        <option></option>
        <option>CORRETO</option>
        <option>ERRADO</option>
        </select><br class="auto-style3" />
        <input class="auto-style3" name="Tel3" id="Tel3" type="text" /><span class="auto-style3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        </span><select class="auto-style3" name="SitTel3" id="SitTel3" style="width: 97px">
        <option></option>
        <option>CORRETO</option>
        <option>ERRADO</option>
        </select><br />
        <br class="auto-style3" />
        <span class="auto-style3"><span class="auto-style5">EMAIL:</span> </span>
        <input class="auto-style3" name="Email1" id="Email1" style="width: 329px" type="text" /><br />
        <br class="auto-style3" />
        <span class="auto-style4">ENDEREÇO:<br />
        </span><br class="auto-style4" />
        <span class="auto-style3">Logradouro: </span>
        <input class="auto-style3" name="Log1" id="Log1" style="width: 271px" type="text" /><span class="auto-style3">&nbsp;&nbsp;&nbsp; 
        nº: </span>
        <input class="auto-style3" name="Number1" id="Number1" style="width: 59px" type="text" /><span class="auto-style3"><br />
    &nbsp;&nbsp;&nbsp; <br />
        Complemento: </span>
        <input class="auto-style3" name="Comp1" id="Comp1" style="width: 91px" type="text" />&nbsp;&nbsp;&nbsp;
        <span class="auto-style3">Bairro: </span>
        <input class="auto-style3" name="Bairro1" id="Bairro1" style="width: 197px" type="text" /><span class="auto-style3">
        <br />&nbsp;&nbsp; <br />
        Cidade: </span>
        <input class="auto-style3" name="Cid1" id="Cid1" style="width: 276px" type="text" /><span class="auto-style3">&nbsp;&nbsp;&nbsp; 
        Estado: </span>
        <select class="auto-style3" name="Estado1" id="Estado1" style="width: 55px">
        <option></option><option>AC</option><option>AL</option><option>AP</option><option>AM</option><option>BA</option><option>CE</option><option>DF</option><option>ES</option><option>GO</option><option>MA</option><option>MG</option><option>MS</option><option>MT</option><option>PA</option><option>PB</option><option>PE</option><option>PI</option><option>PR</option><option>RJ</option><option>RN</option><option>RO</option><option>RR</option><option>RS</option><option>SC</option><option>SE</option><option>SP</option><option>TO</option>
        </select><br class="auto-style3" />
        <br class="auto-style3" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
       <div>
       <input type="submit" class="button redButton" value="Submit">
       </div>
      </form>
      <script type="text/javascript">
       function formSubmit(argTheFormElement) {
        google.script.run
         .withFailureHandler(myFailureFunction)
         .withSuccessHandler(google.script.host.close)
         .getValuesFromForm(argTheFormElement);
        }
       function myFailureFunction(argError) {
        alert("There was an error: " + argError.message);
        google.script.host.close();
        }
      </script>
     </body>
    </html>

Documentation: Serving templated HTML. One potential pitfall is that the getActiveSpreadsheet method won't work in the doGet function serving the HTML; you'll need to access the spreadsheet with openById or openByUrl method. — user3717023
– user3717023, Commented Jul 1, 2016 at 15:33
Is your form in a sidebar, or a dialog box, or a web app? Or even something that is not a Google product? — Alan Wells
– Alan Wells, Commented Jul 1, 2016 at 19:28

Alan Wells · Accepted Answer · 2016-07-02 18:15:35Z

2

Put a scriptlet into the value attribute setting of the input field.

Currently:

<input class="auto-style3" name="Name1" id="Name1" style="width: 382px" type="text"/>

Should be:

<input class="auto-style3" name="Name1" id="Name1" 
  style="width: 382px" type="text" value="<?!= theFunctionNameHere() ?>"/>

Server Code:

function theFunctionNameHere() {
  return "testing";
};

The function to display the dialog box must have createTemplateFromFile().evaluate()

function editDadosPessoais() {
  var html = HtmlService.createTemplateFromFile('the_HTML_file_name')
    .evaluate()
    .setWidth(640).setHeight(460)
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  SpreadsheetApp.getUi().showModalDialog(html, 'Input Form');
};

edited Jul 2, 2016 at 18:15

answered Jul 1, 2016 at 15:58

Alan Wells

31.4k16 gold badges113 silver badges164 bronze badges

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

2 Comments

Lucas Lau Over a year ago

As I said I don't have much experience in programming. I tried it, but it didn't work. The pre filled value of the form became <?!= theFunctionNameHere() ?>. Other problem is the doGet() function, When I tried to use that the form didn't appear anymore. It shows Finished Script without showing the form.

Alan Wells Over a year ago

You don't need a doGet() function, that's for a Web App. I updated the answer for a dialog box. Let me know what happens.

EvSunWoodard · Accepted Answer · 2016-07-01 16:01:21Z

0

In order to do this you want to do a couple simple steps.

A) Make your HTML Page with an empty tag with an id of your choice.

<div id='custom> </div>

B) In your script tag, make a function that triggers when it opens, AKA don't wrap it as a function.

<script>
  google.script.run ...
</script>

This will launch automatically, because it has no function container.

C) Then make your google.script.run function in your .gs file to return the default values you want. And run it in your script with a success handler, that points the returned data to your html tag.

<script>
  google.script.run
    .withSuccessHandler( function(returnedData) {
       document.getElementById('customId').innerHTML = returnedData;
    })
  .yourCustomGSFunction();
</script>

D) all done.

answered Jul 1, 2016 at 16:01

EvSunWoodard

1,2681 gold badge10 silver badges28 bronze badges

1 Comment

EvSunWoodard Over a year ago

Worth mentioning, this is for a Spreadsheet Add-on, while Sandy's answer is for a standalone script.

Collectives™ on Stack Overflow

How to send values from the spreadsheet to a user interface created using HtmlService

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related