0

With great help, this script is working to pull in the 2nd table on the below link. The table cannot be scraped with IMPORTHTML unlike the first table (Regular Season works, Playoffs doesn't).

https://www.basketball-reference.com/players/c/curryst01/gamelog/2016

function myFunction() {
  const url = "https://www.basketball-reference.com/players/c/curryst01/gamelog/2016"; // This URL is from your question.
  const sheetName = "Sheet1";  // Please set the destination sheet name.

  const html = UrlFetchApp.fetch(url).getContentText();
  const tables = [...html.matchAll(/<table[\s\S\w]+?<\/table>/g)];
  if (tables.length > 2) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables[8][0], coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId() } } }] }, ss.getId());

    Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables[7][0], coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId() } } }] }, ss.getId());
    
    
    return;
  }
  throw new Error("Expected table cannot be retrieved.");
}`enter preformatted text here`

Now, I have two more issues I'm looking to solve.

  1. Is there a way to tweak this code so both tables 8 and 7 are loading at once? I think my code here fills in with table 8, but then is replaced by table 7. I think because of the batchupdate?

  2. Is there a way to load more than one URL at once. Basically, I want to replicate this code so that 50 URLs can load in the tables at once.

I was previously just using a bunch of IMPORTHTML functions, but because the Playoffs table is a Javascript object, I'm struggling to figure it out.

1

 You are on the right track as the code is using batchUpdate. I suggest you to spend some time learning about how to handle Arrays in JavaScript. I.E. There is no need to include two batchUpdate as it's possible to put the request object of both in a single one, actually in a single batchUpdate it's possible to include 50 pasteData.

Your guess about that both "paste" operations are working and that the second overwrites the first is correct. The overwrites happens because the coordinate property only specifies the sheetId but not the rowIndex and columnIndex properties.

Try replacing:

Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables[8][0], coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId() } } }] }, ss.getId());

Sheets.Spreadsheets.batchUpdate({ requests: [{ pasteData: { html: true, data: tables[7][0], coordinate: { sheetId: ss.getSheetByName(sheetName).getSheetId() } } }] }, ss.getId());

by

Sheets.Spreadsheets.batchUpdate(
{ requests: [
{ pasteData: { html: true, data: tables[8][0], coordinate: { 
sheetId: ss.getSheetByName(sheetName).getSheetId(), 
rowId: 1,
columnId: 1
} } },

{ pasteData: { html: true, data: tables[7][0], coordinate: { 
sheetId: ss.getSheetByName(sheetName).getSheetId(),
rowIndex: 30,
columnIndex: 1
 } } }

] }, ss.getId()
);

Complete code:

function myFunction() {
  const url = "https://www.basketball-reference.com/players/c/curryst01/gamelog/2016"; // This URL is from your question.
  const sheetName = "Sheet1";  // Please set the destination sheet name.

  const html = UrlFetchApp.fetch(url).getContentText();
  const tables = [...html.matchAll(/<table[\s\S\w]+?<\/table>/g)];
  if (tables.length > 2) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    Sheets.Spreadsheets.batchUpdate({
      requests: [
        {
          pasteData: {
            html: true, data: tables[8][0], coordinate: {
              sheetId: ss.getSheetByName(sheetName).getSheetId(),
              rowIndex: 1,
              columnIndex: 1
            }
          }
        },
        {
          pasteData: {
            html: true, data: tables[7][0], coordinate: {
              sheetId: ss.getSheetByName(sheetName).getSheetId(),
              rowIndex: 30,
              columnIndex: 1
            }
          }
        }
      ]
    }, ss.getId());


    return;
  }
  throw new Error("Expected table cannot be retrieved.");
}

Resources

6
  • Thanks so much for this response, I'm currently having an issue implementing it. "rowID" on line 13 is giving me a syntax error. Trying to figure out what's wrong but I really appreciate the help. Will keep playing with it. Dec 27 '21 at 18:43
  • Try declaring variables for the rowId so you can see it's value on the Google Apps Script debugger, and/or use console.log(JSON.stringify(value)) (replace value by the the variable or the expression that is causing problems) to be sure that it's an integer.
    – Rubén
    Dec 27 '21 at 18:48
  • Great - the error says "Unexpected Identifier Line" as is, I will try to do that. Thank you! Dec 27 '21 at 18:51
  • Sorry, I am still struggling to figure this out. Is there a piece of code I'm missing - this is the error I'm seeing now. GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload received. Unknown name "columnId" at 'requests[0].paste_data.coordinate': Cannot find field. Invalid JSON payload received. Unknown name "rowId" at 'requests[0].paste_data.coordinate': Cannot find field. Dec 27 '21 at 20:14
  • @AnthonyMadle I'm sorry, there was several errors like a missing comma, the use of the Id suffix instead of Index, among others. I just updated the answer.
    – Rubén
    Dec 27 '21 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.