Javascript – how to get table from site github.com with Cheerio google app script

For starters, you might want to use GitHub’s API, avoiding the pitfalls of web scraping.

If you do want to stick with GAS and avoid the API, the issue seems to be that the page served is different than the one in the browser. I determined this by adding DriveApp.createFile("test.html", res); to bypass log truncation (apparently, there is no better way according to TheMaster). From this output HTML, it’s apparent that the data is available only in a React JSON string inside a script tag, which can be extracted with Cheerio, parsed with JSON.parse() and traversed.

However, an easier option may be to request the raw markdown and either convert it to HTML with marked and proceed with Cheerio, or parse the table by hand. I’ll use the latter option since I’m not too familiar with the GAS package ecosystem:

function myFunction() { // default GAS function name
  const url = "https://raw.githubusercontent.com/labnol/apps-script-starter/master/scopes.md";
  const res = UrlFetchApp.fetch(url).getContentText();
  const data = [];
  
  for (const line of res.split("n")) {
    const chunks = line
      .replace(/[*`]/g, "")
      .split("|")
      .slice(1, 3)
      .filter(e => e !== " -- ")
      .map(e => e.trim());

    if (chunks.length) {
      data.push(chunks);
    }
  }
  
  console.log(data);
}

Output:

Logging output too large. Truncating output. [
  [ 'Google OAuth API Scope', 'Scope Description' ],
  [ 'Cloud SQL Admin API v1beta4', '' ],
  [ 'View and manage your data across Google Cloud Platform services',
    'https://www.googleapis.com/auth/cloud-platform' ],
  [ 'Manage your Google SQL Service instances',
    'https://www.googleapis.com/auth/sqlservice.admin' ],
  [ '', '' ],
  [ 'Android Management API v1', '' ],
  // ...

Parsing the raw markdown is a bit hacky, but should be reliable enough. If it proves not to be, try one of the other options.

If you’re not married to using GAS, your original code works for me in Node 20.11.1:

const cheerio = require("cheerio"); // ^1.0.0-rc.12 or rc.10

const url = "https://github.com/labnol/apps-script-starter/blob/master/scopes.md";

fetch(url)
  .then(res => {
    if (!res.ok) {
      throw Error(res.statusText);
    }

    return res.text();
  })
  .then(html => {
    const $ = cheerio.load(html);
    const data = $("tbody")
      .find("td")
      .toArray()
      .map(x => $(x).text());
    console.log(data);
  })
  .catch(err => console.error(err));

Output:

[
  'Cloud SQL Admin API v1beta4',
  '',
  'View and manage your data across Google Cloud Platform services',
  'https://www.googleapis.com/auth/cloud-platform',
  'Manage your Google SQL Service instances',
  'https://www.googleapis.com/auth/sqlservice.admin',
  '',
  '',
  'Android Management API v1',
  // ... 1360 total items ...
]

Although this works, the array shown above is too flat to be usable–essentially one giant row. I would use a nested row and cell based scrape to preserve the tabular nature of the data and avoid flattening it out.

// ...
const $ = cheerio.load(html);
const data = [...$("tr")].map(e =>
  [...$(e).find("td, th")].map(e => $(e).text().slice(0, 25))
);
console.table(data.slice(0, 10));
// ...

Here’s the output, which is similar to the GAS script output (remove the slice calls to see all of the data, without truncation):

┌─────────┬─────────────────────────────┬─────────────────────────────┐
│ (index) │ 0                           │ 1                           │
├─────────┼─────────────────────────────┼─────────────────────────────┤
│ 0       │ 'Google OAuth API Scope'    │ 'Scope Description'         │
│ 1       │ 'Cloud SQL Admin API v1bet' │ ''                          │
│ 2       │ 'View and manage your data' │ 'https://www.googleapis.co' │
│ 3       │ 'Manage your Google SQL Se' │ 'https://www.googleapis.co' │
│ 4       │ ''                          │ ''                          │
│ 5       │ 'Android Management API v1' │ ''                          │
│ 6       │ 'Manage Android devices an' │ 'https://www.googleapis.co' │
│ 7       │ ''                          │ ''                          │
│ 8       │ 'YouTube Data API v3'       │ ''                          │
│ 9       │ 'Manage your YouTube accou' │ 'https://www.googleapis.co' │
└─────────┴─────────────────────────────┴─────────────────────────────┘

You can process this further to group on sub-categories. Rows with two empty cells are a delimiter between a scope category (I think–I’m not a domain expert), while rows with an empty right cell are category headers. Here’s an example that groups by sub-categories and attaches headers to each cell:

const grouped = [];
const headers = data[0];

for (const row of data.slice(1)) {
  if (row.every(e => e === "")) {
    continue;
  } else if (row[1] === "") {
    grouped.push({title: row[0], items: []});
  } else {
    grouped
      .at(-1)
      .items.push(
        Object.fromEntries(
          row.map((e, i) => [headers[i], e])
        )
      );
  }
}

console.log(JSON.stringify(grouped, null, 2));

I tested this example processing code in both GAS and Node.