How to append an html table to a sheet

Question

This script sends the data content of an html table to a Google Sheet.

But I now need it to append the data to the next available row.

I've used appendRow() in many cases, but I'm not sure of the syntax in this particular case.

function pasteRequisicaoHtml(table) {
  var ss = SpreadsheetApp.openById("1J_7GZ1C7pgHuRsdfsdfsdfsdf");
  var sheet = ss.getSheetByName('Sheet5').getSheetId();
  var req = {
    requests: [
      {
        pasteData: {
          html: true,
          data: table,
          coordinate: {
            sheetId: sheet,
            rowIndex: 2,
            columnIndex: 0,
          },
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ss.getId());
}

Does this answer your question? [Transfer a HTML table (user input) to google sheets](https://stackoverflow.com/questions/55650581/transfer-a-html-table-user-input-to-google-sheets) — TheMaster, Apr 05 '20 at 21:46

score 1 · Accepted Answer · edited Feb 05 '21 at 01:26

You want to put a HTML table to "the next available row" using Sheets API with Google Apps Script as appending.

If my understanding is correct, how about this answer?

Modification point:

In your case, you can use both Sheets API and Spreadsheet service. Using this, in order to append the table using Sheets API, you can use getLastRow() of Spreadsheet service. And please use the value retrieved by getLastRow() to rowIndex.

When this is reflected to your script, it becomes as follows.

Modified script:

function pasteRequisicaoHtml(table) {
  var ss = SpreadsheetApp.openById("1J_7GZ1C7pgHuRsdfsdfsdfsdf");
  var sheet = ss.getSheetByName('Sheet5');  // Modified
  var req = {
    requests: [{
      pasteData: {
        html: true,
        data: table,
        coordinate: {
          sheetId: sheet.getSheetId(),  // Modified
          rowIndex: sheet.getLastRow(),  // Modified
          columnIndex: 0,
        },
      },
    }, ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ss.getId());
}

References:

Added:

In this sample script, the header row is deleted after the table is append. In this case, it supposes that the header row is one row.

function pasteRequisicaoHtml(table) {
  var ss = SpreadsheetApp.openById("1J_7GZ1C7pgHuRsdfsdfsdfsdf");
  var sheet = ss.getSheetByName('Sheet5');  // Modified
  var lastRow = sheet.getLastRow();  // Added
  var req = {
    requests: [{
      pasteData: {
        html: true,
        data: table,
        coordinate: {
          sheetId: sheet.getSheetId(),  // Modified
          rowIndex: lastRow,  // Modified
          columnIndex: 0,
        },
      },
    }, ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ss.getId());
  sheet.deleteRow(lastRow + 1);  // Added
}

Thank you for the the information, it works. What can I do so that the header row is not included? — user2916405, Apr 05 '20 at 22:27
@user2916405 Thank you for replying. I'm glad your issue was resolved. About your additional question, in this case, in order to remove the header row, it is required to modify `table` before the table is put or remove the header row after the table was put. But I'm not sure about the value of your `table`. So I would like to propose the latter method. Could you please confirm my updated question? If that was not the direction you want, I apologize. — Tanaike, Apr 05 '20 at 22:35
That's awesome ... That did the trick, it work perfectly! Thank you again! — user2916405, Apr 05 '20 at 22:44

How to append an html table to a sheet

1 Answers1

Modification point:

Modified script:

References:

Added: