HTML_BODY Function for Google Sheets

The custom function HTML_BODY for Google Sheets returns the body tag of a web page and its content (or the page response code in case of error).

Simply copy and paste the code of the HTML_BODY function into the script editor to be able to use it later (for more details, check out the Add a Custom Function to Google Sheets page).

Usage Example

The HTML_BODY function here retrieves the content of the site index:

=HTML_BODY("https://www.sheets-pratique.com")

Preview:

Activate wrap text if necessary for a better view of the imported HTML code.

Google Apps Script Code of the Function

The code of the function to copy-paste into the script editor:

function HTML_BODY(url) {
  
  // Source: https://www.sheets-pratique.com/en/codes/html-body-function
  
  // Request
  const request = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Response code
  const code = request.getResponseCode().toString();

  // Returns the page content (or an error message)
  return code[0] == 2 ? request.getContentText().match(/<body.*<\/body>/si) || 'Error: no body tag' : 'Error ' + code;
}

Version Limited to 50,000 Characters

To avoid getting an error in case of exceeding the 50,000 character limit of a cell, you can use this second version of the HTML_BODY function:

function HTML_BODY(url) {
  
  // Source: https://www.sheets-pratique.com/en/codes/html-body-function
  
  // Request
  const request = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Response code
  const code = request.getResponseCode().toString();

  // Returns the page content (or an error message)
  return code[0] == 2 ? (request.getContentText().match(/<body.*<\/body>/si) || ['Error: no body tag'])[0].slice(0, 50000) : 'Error ' + code;
}