Combining Separate Scripts into One

Question

I've been trying to figure out a solution to this for a few weeks & I think I'm at the home stretch, but having some trouble piecing it together. With great help, I have a script (below) that will scrape the tables off a website. One of the tables is a Javascript object, so I can't use the simple IMPORTHTML.

function first200links () {
  const url1 = 'https://www.basketball-reference.com/players/a/abdulka01/gamelog/1970'; // This URL is from your question.
  const sheetName1 = 'Sheet1';  // Please set the destination sheet name.

  const html1 = UrlFetchApp.fetch(url1).getContentText();
  const tables1 = [...html1.matchAll(/<table[\s\S\w]+?<\/table>/g)];
  if (tables1.length > 2) {
    const ss1 = SpreadsheetApp.getActiveSpreadsheet();
    Sheets.Spreadsheets.batchUpdate({
      requests: [
        {
          pasteData: {
            html: true, data: tables1[8][0], coordinate: {
              sheetId: ss1.getSheetByName(sheetName1).getSheetId(),
              rowIndex: 1,
              columnIndex: 1
            }
          }
        },
        {
          pasteData: {
            html: true, data: tables1[7][0], coordinate: {
              sheetId: ss1.getSheetByName(sheetName1).getSheetId(),
              rowIndex: 30,
              columnIndex: 1
            }
          }
        }
      ]
    }, ss1.getId());
return;
}
throw new Error('Expected table cannot be retrieved.');


  const url2 = 'https://www.basketball-reference.com/players/a/abdulka01/gamelog/1971'; // This URL is from your question.
  const sheetName2 = 'Sheet2';  // Please set the destination sheet name.

  const html2 = UrlFetchApp.fetch(url2).getContentText();
  const tables2 = [...html2.matchAll(/<table[\s\S\w]+?<\/table>/g)];
  if (tables2.length > 2) {
    const ss2 = SpreadsheetApp.getActiveSpreadsheet();
    Sheets.Spreadsheets.batchUpdate({
      requests: [
        {
          pasteData: {
            html: true, data: tables2[8][0], coordinate: {
              sheetId: ss2.getSheetByName(sheetName2).getSheetId(),
              rowIndex: 1,
              columnIndex: 1
            }
          }
        },
        {
          pasteData: {
            html: true, data: tables2[7][0], coordinate: {
              sheetId: ss2.getSheetByName(sheetName2).getSheetId(),
              rowIndex: 30,
              columnIndex: 1
            }
          }
        }
      ]
    }, ss2.getId());
return;
}
throw new Error('Expected table cannot be retrieved.');

My problem is that I'm trying to do multiple links at once. The actual script is 20,000 lines of code, but it just repeats the same structure over and over again. I set up a Google Sheet to recreate the script for new links but with new variables using the Sheet row. For example, you'll see it says "html1" and "html2" for the next link, and so on so forth through the 500 URLs I want to scrape.

I've been trying to COMBINE these two functions into one (and likewise for all 500), but I keep getting errors with everything I've tried.

I thought to remove the "return" rule and merge the codes, but again, having issues with any idea thus far.

I'm going to keep attempting, but would love to know if there is something simple I'm just missing.

On a side note, I tried using ParseHub for this, but also having big issues with that software unfortunately. Seems like these tables are just very tough to scrape due to the sporadic nature of them.

Additionally, I know this maps to individual Sheets. My hope is I can do 200 tabs at a time and somehow label them with the link, then use another script to thus combine all tabs.

Answer 1

You will find worthy to spend some time improving your JavaScript and programming knowledge and skills. In this specific case you could create a function to be called by another function (some people call them "helper function") by passing the URL as parameter or make use of Array methods like Array.prototype.forEach() or Array.prototype.reduce(), among others, this way you could avoid having to repeat the same code lines over an over.

By the other hand, your code is using batchUpdate so there is a "big" opportunity to make your code more efficient (this might be relevant to reduce the number of calls to the Sheets API due to the Google Apps Script and Sheets API quotas) by making several batchUpdate requests in a single call. For this you should be able to handle Arrays in JavaScript (actually, I already mentioned that in my answer to your previous question.

Resources

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Grammar_and_types
• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Functions

Related

• ImportHTML Script at Bulk