Search code examples
htmlgoogle-apps-scriptweb-applicationsgoogle-sheetsclient-server

Transfer a HTML table (user input) to google sheets

i want to transfer some user input data from a html page to my google sheet with google´s app scripts. I can do that for single values but could not figure out a nice way to do that for a table. The table will be dynamically created by the user (fix number of columns and dynamic number of rows).

Bellow is the code which shows the example for a single value and is missing a solution for the entire table.

One idea was to iterate over the table and create a array of arrays with the table data and parse that to the backend script to then write it to the sheet but maybe there is a smarter way to do so.

HTML frontend:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

  <!-- Get the user name -->
  <label>Name:</label><input type="text" id="username">

  <!-- Get the table data -->
  <table class="table table-bordered" id="tbl_posts">
        <thead>
          <tr>
            <th>#</th>
            <th>column 1</th>
            <th>column 2</th>
            <th>column 3</th>
            <th>column 4</th>
          </tr>
        </thead>
        <tbody id="tbl_posts_body">
          <tr id="rec-1">
            <td><span class="sn">1</span>.</td>
            <td>11</td>
            <td>12</td>
            <td>13</td>
            <td>14</td>
          </tr>
        </tbody>
        <tbody id="tbl_posts_body">
          <tr id="rec-2">
            <td><span class="sn">1</span>.</td>
            <td>21</td>
            <td>22</td>
            <td>23</td>
            <td>24</td>
          </tr>
        </tbody>
      </table>

    <button id="sendbutton"> Send </button>

    <script>

    document.getElementById("sendbutton").addEventListener("click",parse_to_backend);

    function parse_to_backend(){

      // transfer the username
      var name = document.getElementById("username").value;
      google.script.run.TransferUsername(name);

      // transfer the table data 

      // HERE I NEED SOME CODE

    };

   </script>

  </body>
</html>

JS backend:

function doGet() {

  return HtmlService.createHtmlOutputFromFile("FrontEndScript");

}


function TransferUsername(name){

  var URL = "https://docs.google.com/spreadsheets/...";
  var Spreadsheet = SpreadsheetApp.openByUrl(URL);
  var Worksheet = Spreadsheet.getSheetByName("Data");
  Worksheet.appendRow([name,]);

}

function TransferTabledata(tabeldata){

  var URL = "https://docs.google.com/spreadsheets/...";
  var Spreadsheet = SpreadsheetApp.openByUrl(URL);
  var Worksheet = Spreadsheet.getSheetByName("Data");

  // Here i need some code

}
Solution

  • You can use Sheets API to parse and paste HTML directly.

    FrontEnd.html Snippet:

    let table = document.getElementById('tbl_posts').outerHTML;
google.script.run.pasteHtml(table);

    Code.gs Snippet:

    function pasteHtml(table) {
  var ss = SpreadsheetApp.getActive();
  var req = {
    requests: [
      {
        pasteData: {
          html: true,
          data: table,
          coordinate: {
            sheetId: ss.getSheets()[1].getSheetId(), //second sheet!A1
            rowIndex: 0,
            columnIndex: 0,
          },
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ss.getId());
}

    To Read: