CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
user2916405
user2916405

Reputation: 147

How to append an html table to a sheet

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

spreadsheet sample

Upvotes: 1

Views: 669

Answers (1)

Tanaike
Tanaike

Reputation: 201388

  • 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
}

Upvotes: 1

Related Questions