Send HTML web app table data back to Google Sheets using Scripts

Question

I have a Google HTML web app I am creating to allow several users to go in and report their expenses for their credit card. First I paste a raw data report into a Google spreadsheet, then an HTML web app is launched which displays the users transactions in an HTML table. I also added drop boxes in the last column to allow the user to select which type of expense each transaction is.

I need help figuring out how to send the data the user selects from the drop downs back to google sheets. I would like this information to be sent back when the user clicks the submit button.

Here is what I have so far:

index.html

 <? 
  var ss = SpreadsheetApp.openById('myID');
  var HomeSheet = ss.getSheetByName('Home');
  var StoreNum = HomeSheet.getRange(5, 2).getValue();
  var StoreSheet = ss.getSheetByName(StoreNum);
  var data = StoreSheet.getRange('A7:I' + lastRow('A',StoreNum)).getValues();
 ?>
<h2>Location: <?= StoreNum ?></h2>

<button onclick='buttonFunction()'>Submit</button>

<body style='background-color:#cccccc'>
  <table class='myTable' border='1'>
    <th align='center'>Settlement Date</th>
    <th align='center'>Purchase Date</th>
    <th align='center'>Purchase Time</th>
      <? for (var i = 0; i < data.length; i++) { ?>
        <tr>
          <? for (var j = 0; j < data[i].length; j++) { ?>
            <td align='center'>
            <?= data[i][j] ?>
            </td>     
          <? } ?>
            <td align='center'>
              <form>
                <select>
                  <option value="Select Type">Select Type</option>
                  <option value="Food">Food</option>
                  <option value="M&R">M&R</option>
                  <option value="Office Sup">Office Sup</option>
                  <option value="Other">Other</option>
                </select>
              </form>
            </td>  
        </tr>
      <? }?>
  </table>
</body>

Code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate()
}

function include(file) {
  return HtmlService.createHtmlOutputFromFile(file).getContent();
}        

function submitRequest() {
  var table = document.getElementsByClassName('myTable')[0];
  var tableRow = table.rows[1][8];
  var tableContent = tableRow.firstChild.textContent;
  StoreSheet.getRange('D15:D18').setValues(tableContent)
}

I tried to do the above function, which I found from somewhere, but I get a "document not defined" error. I'm pretty sure its because the document.getElementsByTagName needs to be called in the <script> of an HTML file but I'm not really sure where to go from here, I've been successful in getting Google sheet data into HTML but not backwards.

Here is an image of the table if it helps to visualize.... I am not sure if I should be grabbing the data by the table, or by the dropdown box elements.

EDIT: CONFIRMED WORKING

(Juan Diego's answer) index.html file above

Code.gs:

function setValue(value) {
  var ss = SpreadsheetApp.openById('myID');
  var HomeSheet = ss.getSheetByName('Home');
  var StoreNum = HomeSheet.getRange(5, 2).getValue();
  var StoreSheet = ss.getSheetByName(StoreNum);

  StoreSheet.getRange('E1').setValue(value)
}

script.html

<script>

function onSuccess() {
  alert('Your request has ben submitted');
}

function buttonFunction() {
  var Row = document.getElementById("tablerow");
  var Cells = Row.getElementsByTagName("td");
  var cellvalue = (Cells[0].innerText);

  google.script.run.withSuccessHandler(onSuccess).setValue(cellvalue);
}

</script>

Community · Accepted Answer · 2017-05-23 11:54:07Z

2

submitRequest() Should be in the front end and callback a function in the backend sending the data of the HTML form

Check this stackoverflow post: How to submit HTML form into a Sheet?

Google Doc Class google.script.run

The idea here is to have the function in the front end:

<script>
function submitRequest(){
 google.script.run.withSuccessHandler(success).[YOUR_BACKEND_FUNCTION]([YOUR_FORM_VALUES]);
}
</script>

Tha will give you an object in the backend that you can treat

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Mar 6, 2017 at 9:42

Juan Diego Antezana

9124 silver badges16 bronze badges

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

2 Comments

Senor Penguin Over a year ago

Thank you so much for the response and information. Could you please see my edit above and tell me if something like that would work? What exactly would I put for "YOUR_FORM_VALUES"? Also do you know if I am grabbing the data from the form correctly? Thanks again

Senor Penguin Over a year ago

After some further research on google.script.run and realizing that it's the way a google script file communicates with the html javascript, it all made more sense. I put the working script under my edit. Thanks again for the info and example.

Collectives™ on Stack Overflow

Send HTML web app table data back to Google Sheets using Scripts

index.html

Code.gs

EDIT: CONFIRMED WORKING

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

index.html

Code.gs

EDIT: CONFIRMED WORKING

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related