CODEWITHSUNDEEP
javascripthtmldomgoogle-apps-scriptgoogle-apps
Senor Penguin
Senor Penguin

Reputation: 405

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

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. 1]

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>

Upvotes: 0

Views: 2584

Answers (1)

Juan Diego Antezana
Juan Diego Antezana

Reputation: 912

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

Upvotes: 2

Related Questions