I found a solution on StackOverflow that I have been using to populate table data from some websites and haven't had any issues with the script (including some modifications I would make depending on the site, etc...) But, I'm having trouble getting the data from this website to populate although I am seeing the response from the Logger.log(table)

Is this because table is not a valid JSON? Is there any way to modify the script to pull this data?

function senate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = 'html';
  const sheetName = "senate"; 
  var dstSheet = ss.getSheetByName("senate");
  var lr = dstSheet.getLastRow();
  const html = UrlFetchApp.fetch(url).getContentText();
  const tables = [...html.matchAll(/<table[\s\S\w] ?<\/table>/g)];
  Logger.log(tables)
  if (tables !== null) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables, type: "PASTE_VALUES", coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId(),rowIndex: lr, columnIndex: 0 } } }] }, ss.getId());
    return;
  }
  throw new Error("Expected table cannot be retrieved.");
}

CodePudding user response：

If you are using this sample script, I think that in this case, tables is an array. So, how about the following modification?

From:

Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables, type: "PASTE_VALUES", coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId(),rowIndex: lr, columnIndex: 0 } } }] }, ss.getId());

To:

Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables[0][0], type: "PASTE_VALUES", coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId(),rowIndex: lr, columnIndex: 0 } } }] }, ss.getId());

• When I saw tables.length, it's 1. So, in this modification, data: tables was modified to data: tables[0][0].