CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Jon Bowles
Jon Bowles

Reputation: 129

Scrape all table data from a html file into a Google sheet

I am trying to scrape all table data from a html file into a Google sheet. I have the script below which almost works which I got here, the only problem is as the script runs it replaces the previously imported table instead of adding the data below the already imported data. So effectively IU only end up with the last table data from the html file.

     function myFunction() {
     var fileId = "16vFEMQNlvulGQEN1gqAqP7XoIM5ox5h8";  // Please set the file ID of HTML file.
     var spreadsheetId = "1cCFqxEE3Ry2pzAV8syXkYmyBVsif5hb-EqzIQVoaf8U";  // Please set the Spreadsheet ID for putting the values.
     var sheetName = "Sheet4";  // Please set the sheet name for putting the values.

     // Retrieve tables from HTML data.
     var html = DriveApp.getFileById(fileId).getBlob().getDataAsString();
     var values = html.match(/(<table[^>]*>(?:.|\n)*?<\/table>)/gi);

     // Put the HTML tables to the Spreadsheet.
     var ss = SpreadsheetApp.openById(spreadsheetId);
     var sheet = ss.getSheetByName(sheetName);
     var sheetId = sheet.getSheetId();
     var rowIndex = 0;
  
     values.forEach(function(e) {
     var resource = {requests: [{pasteData: {html: true, data: e, coordinate: {sheetId: sheetId, 
     rowIndex: 
     rowIndex}}}]};
     Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
     rowIndex = sheet.getLastRow();
     })
    }

Upvotes: 2

Views: 204

Answers (1)

Tanaike
Tanaike

Reputation: 201358

In your situation, how about the following modification?

From:

Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
rowIndex = sheet.getLastRow();

To:

Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
SpreadsheetApp.flush(); // Added
rowIndex = sheet.getLastRow();

Reference:

Upvotes: 1

Related Questions