Web App Development with Google Apps Script/Data to spreadsheets

Basic gist

If you collect some data from a user (maybe from an input tag or something) that you want to save to your spreadsheet, you have to send it to the server-side, even though you're using the client-side for all the user interface.

The steps are:

1. Grab data from the html element that the user adjusts
2. Possibly massage the data a little (trim any trailing or leading spaces, for example)
3. use the magic sauce: google.script.run!
4. Deal with any response from the server

Get data from input

Here's a simply way to get the data from a textarea element when the user hits "send":

<textarea id="mytextarea"></textarea>
<button type="button" onClick="grabtext()">send</button>

<script>
function grabtext(){
   var text = document.getElementById('mytextarea').value; // note you don't use innerHTML
   // here's where'd you do something with that text
   }

</script>

Sending to the server

Once you have some information to send to the server, you have to somehow get it there. That's where the magical google.script.run comes in!

Basically you can send some data to the server by calling a function **on the server** that can do stuff there and then return some response.

Here's an example that builds on the one from above. We grab whatever the user enters and we add it to a sheet on the (attached) spreadsheet:

<textarea id="mytextarea"></textarea>
<button type="button" onClick="grabtext()">send</button>

<script>
function grabtext(){
   var text = document.getElementById('mytextarea').value; // note you don't use innerHTML
   google.script.run.addRowToSheet(text);
   }

</script>

Meanwhile on the server (so in code.gs instead of javascript added to main.html):

function addRowToSheet(s) { // you can call what's passed anything you want. You don't have to call it "text"
   var ss = SpreadsheetApp.getActive(); // grabs the associated spreadsheet
   var sheet = ss.getSheetByName("Sheet1");
   sheet.appendRow([s]); // appendRow needs an array like ["first column info", "second column info", ...] 
   }

Hearing back from the server

Often you'll want to hear something back from the server. That's what withSuccessHandler is all about. Basically you tell javascript both the server function you want to run **and** the client-side function that should run when something comes back. The syntax is weird, but it works.

Here's an example that augments the last one letting the user know the data has been saved in the spreadsheet:

<textarea id="mytextarea"></textarea>
<button type="button" onClick="grabtext()">send</button>
<div id="callthiswhateveryouwant"></div>

<script>
function grabtext(){
   var text = document.getElementById('mytextarea').value; // note you don't use innerHTML
   google.script.run.withSuccessHandler(dealWithIt).addRowToSheet(text);
   }
function dealWithIt(returnvalue){ // you can call the return value whatever you want. Often you'll see people calling it "e"
   document.getElementById('callthiswhateveryouwant').innerHTML="From the server: "+returnvalue;
   }

</script>

Meanwhile on the server (so in code.gs instead of javascript added to main.html):

function addRowToSheet(s) { // you can call what's passed anything you want. You don't have to call it "text"
   var ss = SpreadsheetApp.getActive(); // grabs the associated spreadsheet
   var sheet = ss.getSheetByName("Sheet1");
   sheet.appendRow([s]); // appendRow needs an array like ["first column info", "second column info", ...] 
   return "holy cow it worked!";
   }