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);
}

Diego · Accepted Answer · 2019-12-12 10:55:00Z

1

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.

answered Dec 12, 2019 at 10:55

Diego

9,5812 gold badges24 silver badges35 bronze badges

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

2 Comments

Jennifer Le Over a year ago

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

Diego Over a year ago

@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 :)

yuri · Accepted Answer · 2019-12-12 12:13:47Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related