Zillow Google Sheets Zestimate Scraper

Google Sheets Appscript.gs

function getZillowUrl(address) {
  const baseUrl = "https://www.zillow.com/homes/";
  const addressUrl = baseUrl + address.replace(/\s+/g, '-') + "/";
  
  try {
    const response = UrlFetchApp.fetch(addressUrl);
    const content = response.getContentText();

    // Look for any script tags that might contain the zpid
    const scriptRegex = /<script\b[^>]*>([\s\S]*?)<\/script>/gm;
    let scriptMatches;
    while ((scriptMatches = scriptRegex.exec(content)) !== null) {
      try {
        const jsonData = JSON.parse(scriptMatches[1]);

        // Extract the zpid from the JSON data
        const zpidRegex = /"zpid":(\d+)/;
        const zpidMatches = JSON.stringify(jsonData).match(zpidRegex);
        
        if (zpidMatches && zpidMatches[1]) {
          const zpid = zpidMatches[1];
          const finalUrl = `${addressUrl}${zpid}_zpid/`;
          return finalUrl;
        }
      } catch (e) {
        // If parsing fails, continue to the next script tag
        continue;
      }
    }
    
    return "URL not resolved";
  } catch (e) {
    return "Error fetching URL: " + e.toString();
  }
}
function getZillowUrl_full(address, city, state) {
  const baseUrl = "https://www.zillow.com/homes/";
  const addressUrl = baseUrl + address.replace(/\s+/g, '-') + '-' + city.replace(/\s+/g, '-') + '-' + state + "/";
  
  try {
    const response = UrlFetchApp.fetch(addressUrl);
    const content = response.getContentText();

    // Look for any script tags that might contain the zpid
    const scriptRegex = /<script\b[^>]*>([\s\S]*?)<\/script>/gm;
    let scriptMatches;
    while ((scriptMatches = scriptRegex.exec(content)) !== null) {
      try {
        const jsonData = JSON.parse(scriptMatches[1]);

        // Extract the zpid from the JSON data
        const zpidRegex = /"zpid":(\d+)/;
        const zpidMatches = JSON.stringify(jsonData).match(zpidRegex);
        
        if (zpidMatches && zpidMatches[1]) {
          const zpid = zpidMatches[1];
          const finalUrl = `${addressUrl}${zpid}_zpid/`;
          return finalUrl;
        }
      } catch (e) {
        // If parsing fails, continue to the next script tag
        continue;
      }
    }
    
    return "URL not resolved";
  } catch (e) {
    return "Error fetching URL: " + e.toString();
  }
}

Above is working right now to download zestimate. To use:

1. In Google Sheets, Go to Extensions->AppSCript and paste in this code
2. Return to your Google Sheet and import the Macros (Extensions->Macros->Import Macros) and enter one of two formulas:

=getZillowUrl([Street Address])

or

=getZillowUrl_full(address, city, state)

3. This will return the best guess URL with zpid. I've noticed it doesn't work for some addresses such as apartments where there are many units.
4. Using that new cell with the address, next to it, enter this formula to grab the zestimate from that url:

=importxml([Cell_with_url],"//*[@id='home-details-home-values']/div/div[1]/div/div/div[1]/div/h3")