Update HTML table (user input) to Google Sheets using Apps Script

Question

I am trying to append the HTML table (user input) to Google Sheets. I read this post but the solution did not work for me. I've enabled Google Sheets API in Apps Script. I can use appendrow to update each single row per click, but I look forward to appending the whole table with a single click.

HTML front end.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <table id="t01">
  <tr>
    <th>Project</th>
    <th>Current Light Type</th>
    <th>Qty</th>
  </tr>
  <tr>
    <td><input type="text" id="p1" size="25"></td>
    <td>      
       <select id="l1">
         <option>2D</option>
         <option>Donwlight</option>
       </select>
    </td>
    <td><input type="text" id="q1" size="25"></td>
  </tr>
  <tr>
    <td><input type="text" id="p2" size="25"></td>
    <td>
      <select id="l2">
        <option>2D</option>
        <option>Donwlight</option>
       </select>
    </td>
    <td><input type="text" id="q2" size="25"></td>
  </tr>
  </table> 
    <div>
    <button id="btn">Submit</button>
    </div>
      <script>
      document.getElementById("btn").addEventListener("click",parse_to_backend);
      function parse_to_backend(){
        let table = document.getElementById("t01").outerHTML;
        google.script.run.pasteHtml(table);
        document.getElementById("p1").value="";
        document.getElementById("l1").value="";
        document.getElementById("q1").value="";
       };
       </script>
  </body>
</html>

JS backend

function doGet(e) {
  return HtmlService.createTemplateFromFile("page").evaluate();
}

function pasteHtml(table) {
  var url = "https://docs.google.com/spreadsheets/d...";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  var req = {
    requests: [
      {
        pasteData: {
          html: true,
          data: table,
          coordinate: {
            sheetId: ws,
            rowIndex: 0,
            columnIndex: 0,
          },
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ws);
}

score 1 · Accepted Answer · answered Dec 12 '19 at 10:55

You're close. There are two critical things that you didn't maintain from the answer you referenced.

The value for sheetId needs to be the sheet's ID, not the sheet ws
The Sheets.Spreadsheets.batchUpdate() call expects the spreadsheet ID, not the spreadsheet itself.

function pasteHtml(table) {
  var url = "https://docs.google.com/spreadsheets/d...";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  var req = {
    requests: [
      {
        pasteData: {
          html: true,
          data: table,
          coordinate: {
            sheetId: ws.getSheetId(), // Pass the sheet ID
            rowIndex: 0,
            columnIndex: 0,
          },
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ss.getId());  // Pass the spreadsheet ID
}

Now, you're going to have some other issues because of the way that you're extracting the table in your front end code (because it's actually inputs within a table), but this works. If you have other questions about how to extract the table/input data, please post another question and add the javascript tag so others can help you with it.

Many thanks, Diego. It works now, and you are right that I am facing another issue. I will need to spend more time with javascript.. — Jennifer Le, Dec 12 '19 at 12:05
@JenniferLe Great. If do post another question, please share it and I'll take a look. In the meantime, if you feel it appropriate, please mark the question as answered :) — Diego, Dec 12 '19 at 12:58

score 0 · Answer 2 · answered Dec 12 '19 at 12:13

Using doGet and doPost functions with Html forms the problem gets very simplified:

JS Backend

function doGet(e) {
  return HtmlService.createTemplateFromFile("page").evaluate();
}

function doPost(e) {
  var url = "YOUR_SPREAD_SHEET_URL";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  var params = e.parameter;  
  ws.appendRow([params.p1, params.l1, params.q1]);
  ws.appendRow([params.p2, params.l2, params.q2]);
  return HtmlService.createTemplateFromFile("page").evaluate();
}

HTML Front

<!DOCTYPE html>
<html>
  <head>
  <base target="_top">
  </head>
  <body>
  <form action="PASTE_YOUR_WEBAPP_LINK_HERE" method="post"> 
  <table id="t01">
    <tr>
      <th>Project</th>
      <th>Current Light Type</th>
      <th>Qty</th>
    </tr>
    <tr>
      <td><input type="text" name="p1" id="p1" size="25"></td>
      <td><select name="l1" id="l1"><option>2D</option><option>Donwlight</option></select></td>
      <td><input type="text" name="q1" id="q1" size="25"></td>
    </tr>
    <tr>
      <td><input type="text" name="p2" id="p2" size="25"></td>
      <td><select name="l2" id="l2"><option>2D</option><option>Donwlight</option></select></td>
      <td><input type="text" name="q2" id="q2" size="25"></td>
      <td><input id="btn" type="submit" value="Submit"></td>
    </tr>
  </table> 
  </form>
  <div>
 </div>
 </body>
</html>

For some documentation on doPost and doGet functions check:

https://developers.google.com/apps-script/guides/web

Update HTML table (user input) to Google Sheets using Apps Script

2 Answers2